Re: Database schema for univesal usage

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?

This path led me to the conclusion that data belongs in tables, and the only real problem was the time and expense involved in changing structures and keeping code synchronized. So I went off in pursuit of that goal. Others pursue other paths.

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