Re: how to design tables to store random number of columns

derek.gtalk@xxxxxxxxx wrote:
Hi,

I want to design a generic solution to store different data sources.
One data source may have 10 columns, while the other may have 100+. I
know a few solutions, but none of them seems ideal. Could you give me
some advice? My solutions are listed below:

1. one record only holds one column. so the table will look like:

data_Id, columnId, dataValue

This table may have millions of records easily.

2. put all the columns into one record as below:
data_Id, column_1, column_2, ..., column_N

Obviously we don't know the N in advance. When we got another data
source which has more columns, have to append more columns to the
existing table. The other drawback is that it wastes lots of space for
other data sources whose number of columns is less than N.

3. the solution combines the above two.
data_Id, segment_Id, column_1, column_2, ..., column_M

For a data source which has N columns, it will have N/M records for
each message data.

Regards,
Derek


Only solution 1 will provide you with a normalized database and avoid the need for system modifications in the event additional data sources are required and/or existing data sources require additional columns.

Multi-million row tables, if properly indexed, don't present performance problems.

HTH
Jerry
.

Relevant Pages
Loading