... and knowing that every record in your db is accessible via a 4-
or 8-byte integer key allows you to build generic primary key
processes that rely upon one fixed format. If you've had to code
processes that need to handle single- and multiple-column primary
keys of varying length then you'll appreciate it.

Regards,
Andrew Ferguson
-- The danger is no longer theoretical.

--- In ib-support@y..., "Rob Schuff" <rob@b...> 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.
>
>
> ----- Original Message -----
> From: "Paul Schmidt" <paul@t...>
> To: <ib-support@y...>
> 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@t...
> > www.tricattechnologies.com
> >
> >
> >
> > To unsubscribe from this group, send an email to:
> > ib-support-unsubscribe@e...
> >
> >
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
> >
> >