Re: Approaches for Lookup values (codes) in OLTP application

On Feb 15, 2008, at 1:43 PM, Scott Marlowe wrote:
> Generally speaking, I tend towards using the real value as the key and
> foreign key in lookup tables, but occasionally using an artificial
> numeric key is a better choice.

Something to consider here... any table that will have either a lot
of rows or a lot of "type" fields will likely be better off with a
phantom key (such as a serial) rather than storing text values in the
base table. As an example, we have a 500G database at work that
currently doesn't use any phantom keys for this kind of thing. I
recently estimated that if I normalized every field where doing so
would save more than 1MB it would reduce the size of the database by
142GB. Granted, about half of that is in a somewhat unusual table
that logs emails (a lot of the emails have the same text, so the gain
there is from normalizing that), but even discounting that 75G is
nothing to sneeze at in an OLTP database.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828