Re: Surrogate keys (Was: enums)

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Dann Corbit
> Sent: Wednesday, January 18, 2006 4:04 PM
> To: josh(at)agliodbs(dot)com; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Surrogate keys (Was: enums)
>
> > -----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.

Make that: "combinations of natural attributes make excellent candidates
for indexes."
See. I even messed it up, when I was trying to highlight the
distinction.
Of course, we can probably just chalk that up to "dumb as a box of
hammers."

> 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.
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: explain analyze is your friend