Subject Re: [ib-support] Db Design opinions sought - Lookup tables and surrogate PKs
Author Helen Borrie
At 11:52 AM 26-07-02 -0700, you wrote:
>Yes in general i feel the int is the way to go but re: changed spelling and
>all of the changes to other tables, a foriegn key relationship with the
>cascading referential integrity will do the updates for you..albeit in some
>cases it could be a slow update! Space in that scenario is definitely an
>issue but not in smaller apps.

Technically, yes, it is possible to apply ref integrity to such a
relationship and let cascades do their thing. There is a significant
problem with this, however. If you apply RI to a lookup key, an
single-column index will be created for the lookup key. Single-column
indexes on lookup columns typically have very low selectivity, which kills
performance - every time, not just when a key value changes. So RI on
lookup columns is best avoided...


All for Open and Open for All
Firebird Open SQL Database · ·