Re: surrogate vs natural primary keys

On Mon, Sep 15, 2008 at 4:02 PM, Seb <spluque(at)gmail(dot)com> wrote:
> Hi,
>
> I've been reading several articles on this hotly debated issue and still
> can't find proper criteria to select one or the other approach for the
> database I'm currently designing. I'd appreciate any pointers. Thanks.

You'll find lots of arguments from both sides, some more strident than
others. In most big transactional systems you'll find surrogate keys
used for performance reasons, as well as design choices. 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).

The problem with natural keys is that you can never be sure they won't
change on you. I like using them, but have been caught out on many
occasions where things changed halfway through development and
required a lot of redesign.

I think this question is a lot like "how large should I set
shared_buffers?" There's lots of different answers based on how you
are using your data.