Hi everyone,
I want to model the following scenario for an online marketing application:
Users can create mailings. The list of recipients can be uploaded as
spreadsheets with arbitrary columns (each row is a recipient). I expect
the following maximum quantities the DB will contain:
* up to 5000 mailings
* 0-10'000 recipients per mailing, average maybe 2000
* approx. 20 columns per spreadsheet
I see basically two approaches to store the recipients:
A) A single table with a fixed number of generic columns. If the
spreadsheet has less columns than the table, the values will be null.
CREATE TABLE recipient (
mailing integer,
row integer,
col_1 text,
…
col_50 text,
PRIMARY KEY (mailing, row),
FOREIGN KEY mailing REFERENCES mailing(id)
);
B) Two tables, one for the recipients and one for the values:
CREATE TABLE recipient (
mailing integer,
row integer,
PRIMARY KEY (mailing, row),
FOREIGN KEY mailing REFERENCES mailing(id)
);
CREATE TABLE recipient_value (
mailing integer,
row integer,
column integer,
value text,
PRIMARY KEY (mailing, row, column),
FOREIGN KEY mailing REFERENCES mailing(id),
FOREIGN KEY row REFERENCES recipient(row)
);
I have the feeling that the second approach is cleaner. But since the
recipient_value table will contain approx. 20 times more rows than the
recipient table in approach A, I'd expect a performance degradation.
Is there a limit to the number of rows that should be stored in a table?
With approach B the maximum number of rows could be about 200'000'000,
which sounds quite a lot …
Thanks a lot in advance for any suggestions!
Best regards,
Andreas
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
