Re: Database schema for univesal usage

Kenneth Downs wrote:
> lauri.pietarinen_at_atbusiness.com wrote:
> > -----------------------------
> > DB1:
> >
> > create table Customer
> > (cust_id integer primary key,
> > cust_name varchar(30),
> > voice_phone varchar(20),
> > fax_phone varchar(20))
> >
> > -----------------------------
> > DB2:
> >
> > create table Customer
> > (cust_id integer primary key,
> > cust_name varchar(30))
> >
> > create table CustPhone
> > (phone_id integer primary key,
> > cust_id integer references Customer,
> > phone_number_type char(5),
> > phone_number varchar(20))
> >
> > ---------------------------------
> >
> > Clearly DB2 is more flexible, since
> > when new types of phones are introduced, no
> > schema changes are needed.
> >
> > On the other hand DB1 is more explicit, imposing
> > the rule that a customer can have only certain
> > types of phonenumbers and only one of each.
> > It is also a tad easier to query.
> >
> > These kinds of decisions must be made many times
> > during database design. Flexibility will lessen
> > the need for schema changes, explicity will (probably)
> > lessen the need for code in the application programs
> > and make the schema itself more communicative.
>
> I would have to disagree with this general line of reasoning. The
examples
> of DB1 and DB2 each precisely and exactly implement two different
rules.
> The first rule is that a customer has up to two phones, one of each
type,
> while the second allows for 0-1-infity of both contacts and types.
Each
> schema is correct for the rules it implements, and neither is correct
for
> the other's rules.

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.

Regards,
Lauri Pietarinen Received on Tue May 10 2005 - 21:09:23 CEST