Helen wrote:

... stuff snipped

>Indexes on small ("control") tables won't slow anything down, since the

tables are usually >pretty static anyway and you are not going to be
performing huge bulk inserts or deletes to >them. Some would say that it's
not worth indexing them but you are using IBO, which makes >good use of
indexes.

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

... other stuff snipped

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).
REGNUM refers to the region of the country from which the response came.
REGNUM is about 12 and not likely to change.
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.
ADNUM refers to the advertisement (TV commercial, press ad, radio ad) that
generated the response. ADNUM will be in the thousands.

All these columns are implemeted as FK's to ensure data integrity and to
make use of the IB generated indexes. Also, this keeps the design clean and
understandable. Are you saying that none of these should be implemented as
FK's? 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.

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?

>Cheers,
>Helen

Thanks for any help,

Ray
CIA UK



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