Re: OO and relation "impedance mismatch"

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Wed, 06 Oct 2004 14:08:15 -0400
Message-ID: <fac1kc.83b.ln@mercury.downsfam.net>

Tony Andrews wrote:

>

>> > Another question: how can you generate the code for your triggers

> from

>> > the data dictionary, when your data dictionary doesn't know about
>> > primary keys, unique keys, foreign keys?
>>
>> Since I designed my dd, I put the keys into it.  As stated in the

> other

>> sub-thread on this, by dd I mean my own set of information, not

> anything

>> provided by the vendor.

>
> Understood. Duplication of effort, though, since the "system catalog"
> can do that.
>

I'm going to post separate replies for each line of discussion.

For this one: Ouch! no no no, there is no duplication of effort. My other post "Specifying all biz rules in relational data" may give you some insight into where I am coming from with this, but here is a recap:

The dd as designed by me for me is the sole authoritative repository for biz rules out of which I can generate DDL and application code. This is fundamentally different from the various system catalogs provided by all DBMS vendors (and only Oracle seems to call it a dd) that in one way or another let you determine *after* *the* *fact* the details of the database's construction.

Here is an example of how different they are. Question: if you were designing a dd, and had complete freedom to do it anyway you wanted, how would you specify a foreign key? Well, we always start with the absolute simplest way and then embellish, so we get:

1. A foreign key is specified with two atomic values only, the child table and the parent table.
2. A foreign key definition requires a primary key to be defined on the parent table. (Implied convention that *only* the primary key can be used for RI, as opposed to other candidate keys).
3. Enforce the arbitrary convention that a foreign key definition causes columns to be placed into the child table that correspond in name and type/precision/scale to the primary key of the parent. (consequently requires system to check for duplicate column names, but that should be prevented anyway).
4. Embellish this simple system by allowing column renaming in child table, to enable the reverse-engineering of existing databases.

You now have the simplest possible normalized way to store foreign key definitions. But if you look at the system catalog for SQL Server or DB/2, you will see that they define foreign keys in terms of lists of columns. Listing columns for the parent table is denormalized, they should list the key name and from that we get a list of columns. The vendor-supplied system catalog therefore is non-ideal as an authoritative source of information on the schema.

But further, the vendor-supplied catalog is by definition proprietary, so it cannot be used to export a db schema definition from one vendor and build it on another, because they are not compatible. To get the ability to build your systems on more than one platform, you need your own off-line dictionary that is outside of the database being built (though I put a copy into the database when the build is finished).

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Wed Oct 06 2004 - 13:08:15 CDT