Re: Database schema for univesal usage

From: <lauri.pietarinen_at_atbusiness.com>
Date: 9 May 2005 12:40:17 -0700
Message-ID: <1115667617.048725.318180_at_o13g2000cwo.googlegroups.com>

Kenneth Downs wrote:
> Chris wrote:
>
> > Does anyone know about a intentions to build a general database
schema
> > (based on ER) that can be used for different purposes without
changing
> > the physical layer (tables, views)?
> > Are such ideas described in the literature?
> >
> > Thanx in advance
> >
>
> Chris, I'm guessing you are looking to reduce the time and expense of
schema
> changes, to produce the sort of One True Database that contains
everything.
>
> Many have considered this. When one cosiders the problem in
isolation,
> there are a couple of ideas that tend to come up. The first is the
"One
> Giant Table" idea, where you have this table:
>
> CREATE TABLE OneGiantTable (
> row_type char(20),
> row_id int,
> column_name char(20),
> column_value vchar(100)
> )
>
> In this scenario the "inventor" sees himself as being free of the
need to
> create new tables. The problem is that it turns out he has to
reinvent all
> of the normal handling tools to deal with data in this form.
Ultimately it
> is less work to just deal with specific tables and columns.
>
> The other approach is what you now see in XML, the unstructured or
> semi-structured approach, where a valid file can contain any value so
long
> as you mind your open and close tags. Of course right away you run
into
> huge problems when files contain tags unknown to the application, so
the
> next thing they invented was a verbose and bletcherous scheme to
decide
> what tags ('scuse me, that's "elements") are actually allowed in what
> contexts, and you are right back to structured data and so why not
just use
> tables again?

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.

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.

A third dimension is how to use datatypes. Do we make everything explicit in the schema or do we "hide" structure into datatypes.

Here is an example that comes to mind:

---

DB1:
create table Picture
  (pict_id integer primary key,
   pict_name varchar(30),
   ... etc ...
   picture image)

---

DB2:
create table Picture
  (pict_id integer primary key,
   pict_name varchar(30),
   ... etc ...)

create table Pixel

   (pict_id integer,
    pixel_id integer,
    pixel_color integer,
    primary key(pict_id, pixel_id))

---

In DB1 we have hidden the complexity of the image into a datatype. In DB2 we have made the structure explicit. In most cases I would suppose DB1 is the reasonable choice.

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

---

Perfectly legal, but not a good design.

Best Regards,
Lauri Pietarinen Received on Mon May 09 2005 - 21:40:17 CEST