On Mon, Sep 15, 2008 at 3:45 PM, Scott Marlowe <[EMAIL PROTECTED]> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
