Re: Foreign key problem

From: Kenneth Downs <knode.wants.this_at_see.sigblock>
Date: Tue, 13 Jun 2006 15:49:50 -0400
Message-Id: <agh3m3-k3r.ln1_at_pluto.downsfam.net>


mAsterdam wrote:

> Kenneth Downs wrote:

>> frebe73_at_gmail.com wrote:
>>
>>
>>>I am working on an application which need a data model that reflects
>>>the schema structure. My tables are ("*" indicates primary key column):
>>>
>>>tableinfo(*table_name)
>>>columninfo(*table_name, *col_name)
>>>fk(*fk_name, pk_table, fk_table)
>>>fk_column(*fk_name, *fk_column, pk_column)

>>
>> I can tell you how we did it, and perhaps this will help.
>>
>> Our tables look more like this:
>>
>> TABLEINFO (*table_name)
>> COLUMNINFO (*table_name, *col_name)
>> FK (*table_name,*table_name_par)
> 
> This rules out more than one FK in one table referencing one other.
> Use /roles/ to name your FK's.
> 

In the limited example given in the post, yes.

In fact we have half-dozen or so other flags on the FK table, two of which combine to provide multiple FK's between any two tables. We employ a "suffix" and a "prefix" property, each of which is used to alter the names of the columns in the child tables by adding the obvious prefix or suffix to each column in the child table. Hence a declaration like so:

table employees {
  column employee { primary_key: Y; }
  foreign_key employees { suffix: _sup; } }

or:

table fk {
  foreign_key tables { suffix: _child; }   foreign_key tables { suffix: _parent; } }

We have no desire to completely rename columns, as this leads to obfuscation. This approach to naming columns is not for everyone, but we like it.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)
Received on Tue Jun 13 2006 - 21:49:50 CEST

Original text of this message