how to design tables to store random number of columns

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

.

Relevant Pages
Loading