Re: Database schema for univesal usage

lauri.pietarinen_at_atbusiness.com wrote:

> 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

>> of DB1 and DB2 each precisely and exactly implement two different

>> The first rule is that a customer has up to two phones, one of each

>> while the second allows for 0-1-infity of both contacts and types.

>> schema is correct for the rules it implements, and neither is correct

>> the other's rules.

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.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)