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.

----- Original Message -----
From: "Paul Schmidt" <paul@...>
To: <ib-support@yahoogroups.com>
Sent: Friday, July 26, 2002 8:29 AM
Subject: Re: [ib-support] Db Design opinions sought - Lookup tables and
surrogate PKs


> 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
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>