On 26 Jul 2002 at 11:52, Rob Schuff 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.
>

I know about cascading referential integrity, but it's something that the DBA has to
set up each time, one of these keys gets used, and there is always the possibility
that one will get missed, and it could be months before it gets officially noticed, and
it can be a real PITA to fix it at that point. As for space, I have seen too many times
where something was done with the idea that the app was going to be quick and
dirty, and 3 years later it's a critical component in the system, and someone is now
having to do 10 times the work, to try and make it efficient, when it could have been
easily done right in the first place.

>
> ----- 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/
> >
> >
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~--> Will You Find True Love? Will You Meet the
> One? Free Love Reading by phone!
> http://us.click.yahoo.com/7dY7FD/R_ZEAA/Ey.GAA/67folB/TM
> ---------------------------------------------------------------------~
> ->
>
> 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/
>
>
>

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