Wow, first of all, thanks for the quick response.

At 11: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?

Not sure what you mean by this.

>>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.

ok. I can see that, so I should enforce RI with a trigger.

... stuff snipped

>>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.

Agreed.

>>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.

that makes sense too.

>>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!

I agree about the comments but you don't see those when you're looking at a
diagram in Erwin!

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

>Adnum is the only eligible FK I see there.

Me too, now ;-)

>>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.

Just my inexperience showing! I was referring to updating a domain check but
if you implement a lookup table and trigger it doesn't apply.

Helen, thanks for the advice. I'd better get back to the drawing board....


Ray
CIA UK


[Non-text portions of this message have been removed]