At 10:25 AM 18-06-01 +0100, you wrote:

> >Making foreign keys on other tables that reference these small tables will
>DEFINITELY slow >things down - IB will form long duplicate chains in the
>index tree of the mandatory index >and wreck performance. Instead, make
>composite indexes on such columns, combining the >lookup column + the
>primary key.
>
>I'd just about finished designing the database for an analysis system I'm
>developing when I read the above. Following is a (stripped down) table which
>records responses to direct response advertising campaigns:
>
>CREATE TABLE "RESPONSES"
>(
> "NUM" "POSITIVEBIGINT" NOT NULL,
> "CAMPNUM" "POSITIVEBIGINT" NOT NULL,
> "REGNUM" "POSITIVESMALLINT",
> "CONTMODENUM" "POSITIVEBIGINT" NOT NULL,
> "ADNUM" "POSITIVEBIGINT",
>CONSTRAINT "RESP_PK" PRIMARY KEY ("NUM")
>);
>
>CAMPNUM refers to the advertising campaign that generated the response.
>CAMPNUM is likely to be in the hundreds (or very low thousands).

Primary table in the structure?

>REGNUM refers to the region of the country from which the response came.
>REGNUM is about 12 and not likely to change.

Selectivity is too low to justify an index for sorting or joining purposes.

>CONTACTMODENUM refers to the method of contact used (e.g. web, phone).
>CONTACTMODENUM has only 2 values currently and is not likely to change much,
>say 10 or below.

Same as above if you have these defined in a control table.

>ADNUM refers to the advertisement (TV commercial, press ad, radio ad) that
>generated the response. ADNUM will be in the thousands.

Foreign key relationship and its index are both justified.

>All these columns are implemeted as FK's to ensure data integrity and to
>make use of the IB generated indexes.

I don't see why the first two tables need to referenced to ensure data integrity. The cost of those indexes is large; if you need data integrity triggers, write your own. The IB-generated indexes will be terrible. If you want these columns indexed in the referring table for ordering purposes, define your own composite indexes, viz. low-integrity key + pri key to raise their selectivity.

>Also, this keeps the design clean and
>understandable.

/* Comments */ have the same effect without forcing you to accept engine-style foreign key constraints that kill performance because of their low selectivity. You can have foreign keys in your database without using the db engine to enforce it!

>Are you saying that none of these should be implemented as
>FK's?

Adnum is the only eligible FK I see there.

>CONTACTMODENUM could be a domain with a check on the content, but
>since they are likely to change I would rather not have to change the
>database structure just to add a new contact method.

Why would you have to change the database structure to add a new contact method ????? If it's a control table, you only need to add a new row.

>What would be the best approach in the above 4 cases, from a performace
>viewpoint? What's the scale of the performane 'hit' with the above approach?
>If defining FK's as FK's produces a noticable performance penalty, is that
>unique to IB?

No, it's not unique to IB, just particularly noticeable in IB. As for why, and for the scale of the performance hit, have a look at Ann's posting...

Cheers,
Helen

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