At 03:03 PM 03-04-01 +0200, you wrote:

> > No, don't define them formally as Foreign keys. You will get 6 relationships with low selectivity indexes and it will kill performance.
> >
> > Only use the FOREIGN constraint for master-detail or parent-child (tree) relationships. Hand-code the cascade triggers for lookup relationships such as those you mention.
> >
>
>Hi Helen
>
>I thought the relationships between customers and the six tables are master-detail relationships.

No: Look at the model. One lookup row can be referenced by many rows. Indeed, a single row in a lookup table can be referenced by many rows in many different tables. The lookup relationship is "upside-down" to the genuine master-detail or parent-child dependencies.

In a master-detail relationship, one master record can be referenced by many detail rows. In a tree structure, one parent row can have many children. Hence, we use foreign key relationships to form these dependencies.

In our master-detail or parent-child structures, the master can exist without any detail rows; or the parent can exist without any children. We apply foreign keys to tell the database that it is not valid to have detail rows with no master or child rows with no parent. That is all the database can "know" about the relationship - that you want to constrain it so that it is not possible to create orphan rows.

Now, looking at your Customer row that looks up control tables to get descriptions of things like Country, Payment Terms, Currency, etc. If we place a foreign key constraint on those lookup columns, we are forcing our "true master" to be dependent on the existence of the lookup row. This is conceptually incorrect.

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________