On 25 Jul 2002 at 13:38, Rob Schuff wrote:

> greetings folks,
>
> we are having a spirited discussion at a work about lookup table and
> whether or not they should have a surrogate primary (integer) or
> whether its better to just use the lookup value (or an uppercase'd
> copy of it) as the primary key. I personally have been on both sides
> of the fence before (normalize to the max or be somewhat pragmatic).
> Anybody care to share their opinions?
>

If your using a language without code reuse, and the key is never ever going to
change in a gazillion years, and the key is short and used in relatively few records,
then use the lookup value, otherwise use a surrogate.

Always use a surrogate, if your using a language like C++ or Java that allow you to
reuse code all over the place, because then you can have a base class that handles
lookups and deals with the key issue, the same way everywhere. If the language
requires that you write fresh code every time, like COBOL does, then it becomes an
issue of how well fixed is that key, in other words is there a chance the key value
could change over time. For example if the key is username and the usename is
first initial + last name, then what do we do when we have Kelli Smith who becomes
Kelli Offerski, changing one table is easy, but if it is a lookup key on 147 tables,
then it becomes a real pain in the donkey. Third issue is storage if your key is
longer say a CHAR(20) and it's a lookup for several tables with 15 000 000 000
records, do you really want to be wasting 240GB of storage, twice that if it's
unicode.









Paul Schmidt, President
Tricat Technologies
paul@...
www.tricattechnologies.com