Re: Database schema for univesal usage

lauri.pietarinen_at_atbusiness.com wrote:

>
> There is, I believe, a continuum within the relational framework, from
> a very general schema (your OneGiantTable) to a very explicit one. In
> many instances you can make decisions that bring more or less
> flexibility to a database design.
>
> Just as a small example, consider the
> following database schemas:
>
> -----------------------------
> 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.

Neither is more or less flexible than the other.

They are not analogous to the Entityt-Attribute-Value scheme because each has forgone that model for complete tables.

>
> In the extreme case of the OneGiantTable (or maybe a couple
> of them) all the rules and logic has been moved
> into the application layer and the schema tells us
> nothing.
>

Th One Giant Table example is not an extreme case, it is a totally different case. It is an unstructured case. The other examples in this post are OTOH examples of various levels of detail in a structured case.

>
> On the other hand, it would be foolish to
> hide company details into a datatype such as:
>
> ----------------------------------------------
> create table Customer
> (cust_id integer primary key,
> cust_details cust_detail_datatype)
>
> select cust_details.getName()
> from Customer
> where cust_id=1
>
> ----------------------------------------------
>

I think we may hear from Dawn on this one.

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