Re: Surrogate keys (Was: enums)

["Dann Corbit" <DCorbit(at)connx(dot)com>]

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Josh Berkus
> Sent: Wednesday, January 18, 2006 3:59 PM
> To: pgsql-hackers(at)postgresql(dot)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.