On Mon, Sep 15, 2008 at 3:45 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> for
> instance, when you book a flight with an airline, you'll get a locator
> code like A89JK3 that is unique to any other locator code in the
> system. Sure, you could make a natural key of first name, last name,
> address, phone number, flight number, departure / arrival and day and
> time, but there's no way that's going to perform as well as a single
> char(6).
Scott,
My understanding is that as soon as any code becomes visible outside
of the database or application it can no longer be called a surrogate
key. From my reading of some of the Celko books, he strongly ascribes
to codes as primary keys. His suggestion is to use internationally
recognized codes (if they exist) for identify items. If none exist
then nationally recognized codes, then industry recognized codes, and
then finally if non of these exists then he recommends developing a
company specific unique code (for these he recommends codes that have
a built in check-sum (I think check-sum is the correct word) for data
entry validation).
Any thoughts?
My DB experience has been limited to small < 2 GB OLTP databases, so I
understand that for very large databases there may be cases where
natural keys are a clear looser for performace and storage reasons.
In my case, I've tried to evaluate the trade-offs between using
natural vs surrogate keys for every relation. Most of the time I use
natural keys, however there are some occations when surrogate keys
(for me) are only way to go.
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
