Re: Database schema for univesal usage

Kenneth Downs wrote:
> lauri.pietarinen_at_atbusiness.com wrote:
>
> >
> > My reasoning that DB2 is more general than DB1 rests on the fact
that
> > DB2 can contain any information that DB1 can contain but not vice
> > versa.
> > Additionally, by adding constraints in the database or in the
> > application program DB2 can be made to contain exactly the same
> > information as DB1.
> > It does not work the other way around.
> >
> > Well, thats my reasoning.
> >
>
> What you say is correct, but if you were putting together an app to
track
> cell phones, then for one EIN record there would be exactly one and
only
> one phone #. Putting in a child table requires a constraint plus a
join to
> retrieve the column, when there will always only be one value.
Occam's
> razor cuts off the child table and puts the column into the first
table.

I agree. Which approach you choose depends on what you want out of it.  I think the reasoning behind the "super schema" that can hold "anything" is that we don't know what kinds of requirements we will have in the future so we should build some flexibility into the schema.  Taken to the extreme you get the OneBigTable approach which gives you everything and nothing, since you can put anything into it, but you have to code the "meaning" of the database into the application.

What I am saying is that there are many degrees of flexibility that can be built into the schema. Maybe we currently need only two different kinds of phone nubmers, but we suspect that the situation might change in the future. Should we go for the flexible schema now or just add new columns into the table when we happen to need them? How will the application programs be affected? How will the queries be affected? Will generalising now unnecessarily complicate queries?

This phone number example is trivial. A typical question might be: should one put ones suppliers and customers in the same table, hence generalising these concepts into the concept of the "party"?

Regards,
Lauri Pietarinen Received on Wed May 11 2005 - 00:10:10 CEST