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

derek.gtalk (AT) gmail (DOT) com wrote:
It all depends on what you ae going to do with the data. I am sure
you'll get more useful advice if you outline what the purpose is.

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

at the first stage, what I need is to design a table or tables to store
different data sources efficiently. I may need to query or do some
statistic on those data later on.

derek wrote:
This answer doesn't add any information that I didn't already have,
except that it seems to be for read only access. Also, you write "store
data sources". In my terminology there is a difference between a data
source and data. The data source is basically just a reference to the
database (my-database-server.mydomain.com with login credentials).

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

sorry, i mean to store data from different external systems.

derek.gtalk (AT) gmail (DOT) com wrote:
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

derek.gtalk (AT) gmail (DOT) com wrote:
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

This is not a table BY DEFINITION; you have to know all the attributes
that identify the entity you are modeling. You are not allowed a
"Briteney Spears, automobiles or squid" construct in RDBMS.

For something this vague and poorly defined, I would just use notes in
English until I was able to make a proper data model.

What are you actually trying to do?

add rows to account for new columns. It doesn't address any
of the concerns voiced in previous responses though.
Essentially you end up with a filing system instead of a
database. But that maybe all you need.

You really ought to look at using different tables for different
datasources, or are the different datasources pulling in the same entities?

If you want to hold it in one structure you should be looking at XML; SQL
Server 2005 has a fairly good implementation of XML inside the data engine
now and you can index it and have check constraints on the XML schema....

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


<derek.gtalk (AT) gmail (DOT) com> wrote