Re: Surrogate keys (Was: enums)

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Josh Berkus
> Sent: Wednesday, January 18, 2006 3:59 PM
> To: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Surrogate keys (Was: enums)
>
> Martjin,
>
> > Interesting. However, in my experience very few things have "natural
> > keys". There are no combination of attributes for people, phone
calls
> > or even real events that make useful natural keys.
>
> I certainly hope that I never have to pick up one of your projects.
A
> table without a natural key is a data management disaster.   Without a
> key, it's not data, it's garbage.

I have a different opinion.

The data should absolutely never use a natural key as a primary key.

The data should use something like a sequence for the primary key.

Examples:
SSN --> believe it or not, SSN's sometimes change.

First, Middle, Last names --> Not really unique

Street Address --> More than one person can live there.  They can move.

Basically, every physical attribute or logical attribute is a terrible
choice for a primary key.  They won't cause problems very often, it's
true.  But when they do cause problems, it is a terrible doozie of a
problem.

Now, on the other hand, if we are talking about INDEXES here, that's a
horse of a different color.   Lots of natural attributes and
combinations of natural attributes make excellent candidates for keys.
Such things as SSN, names, addresses, phone numbers, etc.

Therefore, I am guessing the two posters upstream in this thread that I
am responding to were therefore talking about different subjects
altogether.

One was talking about using natural attributes for indexes, which is a
superior idea that I agree with.

The other was talking about never using natural attributes for keys,
which I also agree with.

Therefore, I am guessing that everyone is in complete agreement, but it
is a nomenclature thing.

Just a guess.