Subject Re: [IBO] Foreign Key
Author Geoff Worboys
> I disagree that it is a "technically correct" application of
> a foreign key relationship.

I thought it might come to this :-) I actually thought thats why the
topic was avoided on the ib_support list; that no one wanted to get
into a discussion where design styles would differ.

However by "technically" I meant theoretically. As in "the world
according to Codd" (which I always thought was a pretty dismal place
myself). If the relationship exists it should be correct to tell the
database about it, the fact that the dbms cannot cope does not come
into the theoretical argument.

> This selectivity problem on control keys is not peculiar to
> InterBase, i.e. it is not a implementation problem that can
> be changed by "improving the structure". It is a logical
> problem in the relational model.

The problem is the index selectivity not the foreign key definition.
The FK represents a relationship that really exists. There is nothing
(necessarily) wrong with the model that defines the relationship that
happens to be used for lookup. (Note that lookup relationship is just
a term, it can be seen as simply an upside down master-detail.)

There are solutions to the selectivity problem. Since building such
solutions into IB would probably be difficult I am all for idea of
having Interbase NOT create FK indexes. I can define indexes that the
FK can use without the selectivity problem.

Solving the selectivity problem will not totally remove the
performance issue of maintaining additional indexes on a high volume
table - but that is just another physical limitation that we have to
live with. It also represents another reason why it would be good if
IB would not automatically create FK indexes. It would allow FKs to
be defined (even without manually creating additional indexes) so that
the database structure is documented and integrity enforced albeit
with significant performance hits in some instances.

All IMHO of course :-)

Geoff Worboys
Telesis Computing