Subject Re: [firebird-support] Database Design Issue
Author Ivan Prenosil
From: "chee hee"
> I have a main table with fields like sex, occupation, model, etc (around 10 of these fields)
referencing parent tables respectively. Foreign keys are in placed for these fields. The table will
contain millions of records.
> Will these foreign keys automatically generate indexes for these fields. If yes, these indexes are
low selectivity - lots of duplicates.
> I always heard from the forum that low selectivity will slow down the performance of the table.


> How to resolve or maybe I should not enforce the foreign keys.

Foreign key constraints can be replaced by Check constraints or triggers,
but those solutions can't see behind the "transaction wall", so you have to decide
on which fields you can use it safely in multiuser environment.
E.g. values in lookup table for your "sex" field are good candidates
since they will not change much (there will probably be just 2 values,
but one is not sure what will those genetic engineers invent.
Or perhaps there will be more values, like 69 or so ?)