DB design

Baski

Hi,

How to design a generalized database that is used to store datas from
any other database. The thing is that the tables and datas of the
source database is not known while designing this target database. The
design should be a generalized one to store data from any database. Can
dummy tables be used? Should there be a provion to create the actual
tables from the source db?? Or is there any better way??

Thanks in advance...
Baski.

Feb 28 '06 #1

Mark A

"Baski" <ma*********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Hi,

How to design a generalized database that is used to store datas from
any other database. The thing is that the tables and datas of the
source database is not known while designing this target database. The
design should be a generalized one to store data from any database. Can
dummy tables be used? Should there be a provion to create the actual
tables from the source db?? Or is there any better way??

Thanks in advance...
Baski.

Back in about 1990, IBM realized that customers wanted a data dictionary
that was written using DB2 that could be used to capture entity and
attribute information in the analysis, design, development, and production
support of a project. (Back in those days we had to draw the
entity-relationship diagrams by hand). The DB2 system catalog only stored
information about tables once the application was completely developed, and
allowed little capability for data definitions and domains.

So IBM started a project to develop a product called the Repository that was
so generalized that it not only stored metadata about an application, but it
could store information about anything that could be conceived. The
Repository was so generic that even the tables that stored entities and
attributes were not hard-coded in the Repository..

Even though the Repository was released as a beta product, and IBM even
taught official classes on how it worked, it was never released as a product
and soon became known as the Suppository. It was a gigantic failure and a
huge write off for IBM..

Don't do it. Generic data models are the road to disaster.

Feb 28 '06 #2

Baski

Thanks.
Yes it is not advisable to think of a very generic database to store
anything that is possible. But here, I have my database that has some
standard set of tables to cater to most functions of my application. In
addition I need to accomodate some amount of informantion that is to be
fetched from some other database. May be data of 5 - 10 tables of the
source db or data got from quering on few tables. But these tables are
not necessarily know prior hand.
How to handle this?? Is there a way out?

Feb 28 '06 #3

Serge Rielau

Baski wrote:

Thanks.
Yes it is not advisable to think of a very generic database to store
anything that is possible. But here, I have my database that has some
standard set of tables to cater to most functions of my application. In
addition I need to accomodate some amount of informantion that is to be
fetched from some other database. May be data of 5 - 10 tables of the
source db or data got from quering on few tables. But these tables are
not necessarily know prior hand.
How to handle this?? Is there a way out?

You have to pivot the information.
That is you have the following columns:

(tableschema, tablename, columnname, value)

Works best if you use all strings otherwise you need different value
columns for different types and remember the data type as well.

You require good indexing to make this work and of course it's less than
perfect.

When you're dealing with schema-chaos XML may be the better answer.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Feb 28 '06 #4