Re: OO and relation "impedance mismatch"

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Thu, 07 Oct 2004 12:19:13 -0400
Message-ID: <1aq3kc.n9h.ln@mercury.downsfam.net>

Laconic2 wrote:

>
> "Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message
> news:fac1kc.83b.ln_at_mercury.downsfam.net...

>> 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).

>
>
> There's another way. Allow named user defined domains. Define each named
> domain once in terms of type/precision/scale. Cause corresponding columns
> to be based on the same domain. Keep track of the relationship between
> columns and domains in the metadata (and also in the DD if you have one.)
> "corresponding columns" includes foreign keys and primary keys, but it
> isn't limited to them.
>
> consider this:
>
> create domain employee_nbr number (6);
> create domain department_nbr number (4);
> create domain currency_amount (number 8, 2);
>
> create table departments
> ( department_id department_nbr
> , department_name char (40)
> , manager_id employee_nbr
> , budgeted expense currency_amount
> , actual_expense currency amount
> );
>
> Here "manager_id is a foreign key, and is based on a named domain.
> "department_id" is primary key (even though I didn't say so), and is
> based on a named domain.
> "budgeted_expense" and "actual_expense" are non key values, but they are
> still based on a named domain
> so that they are forced into the same type/precision/scale as each other.
> "department_name" could have been based on a named domain, but what's
> the point?
>

Actually I already use domain definitions, and actually require all columns to be defined domains. Since everything is data (not declarative code), a table's columns are defined as the cross-reference between the table of tables and the table of domains. After that comes the columns that are added as a result of fk defs.

I have pondered vaguely the idea that somehow the system could "know" a primary key and a foreign key when it sees it, but not too seriously. The idea would be something like this:

1. Name a domain, employee_nbr (to use your example) as number(6)
2. Specify table X as being the primary source for this column, the table where the column is the primary key. This is distinct from flagging the column within the x-ref as being part of the pk.
3. All other placements of the column are automatically foreign keys.

But I realized that my definition of the FK/RI as just two table names is more normalized than using #3 above, and I just plain didn't do #2, I went another way. But hmmmm, it does still nag at me that #2 is more precise, more normalized. It just seems to me that when you define a domain like "employee number", the next question is "Well, what table tracks those?" and therefore you want to specify that immediately. OTOH, when you define some commonly used column like "Description varchar(40)" you want to explicitly state that there is no table containing definitive values for this column.

Alas, can't look at that today, have to download the latest Mono and see if it is worth using.

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

Received on Thu Oct 07 2004 - 11:19:13 CDT