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

you are trying to recreate a DBMS. Create tables specific for each
source. If you really cannot do that, use the filesystem. Creating
tables on-the-fly is a really bad idea. Unmaintainable, unnormalized,
just a disaster waiting to happen.

Ed

.

Relevant Pages
Loading