At 11:34 PM 02-02-01 +0000, you wrote:

>This is a very interesting point! I have often this kind of indexes
>with a lot of duplicate caused by indexes of a foreign key. For
>example value stores for code fields: Then I have perhaps 10 code
>values used for some thousands of recods. Something like
>
>Table Data(
> DataID Numeric (18,0),
> CodeID Numeric (18,0),
> Field1 ...
> Primary Key (DataID),
> Foreign Key CodeID REFERENCES Code(CodeID));
>
>Is it somehow possible to define such a compound index for foreign key
>fields? Or do you know another design solution for such a problem?

Well, it's a design solution I use.

I never use low-selectivity columns as foreign keys - I code the cascades
by hand in triggers.
I DO create composite indexes (code + uniqueID usually, in that order).

I replace the composite PK with a surrogate PK if I want to use it as a
foreign key.

Also, understand clearly that keys and indexes are NOT the same thing. IB
always creates an ascending index on a column which you constrain as a
key. If you also create indexes of your own involving such columns, you
will baffle the optimizer with ambiguity.

These days I'm really ruthless about making key columns absolutely atomic -
I absolutely don't allow them to do ANYTHING but form relationships. So
95% or more of my keys are surrogate. It means that, if I have a table
which I want to engage in relationships with multiple other tables, I'm
never constrained to include redundant key element columns in those other
tables if they are not otherwise needed there.

The exception (for my own convenience, cheating a little on atomicity) is
control tables like AccountType, DocFormat, etc. which never engage in
foreign key relationships because of low selectivity. I use a "shortcode"
domain of three uppercase characters to key such tables which then allows
me to display the (logically foreign key column) code "meaningfully" in
dependent rows, e.g. a row in Document (DocID, DocName, DocFormat) might be
(99, 'Book of Job', 'ZIP'). It's personal preference, something I allow
myself to do, like creme caramel. :))

Helen



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