Re: Choosing a surrogate key vs. a composite key - "holy war" issue or clear best practice?

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Sat, 28 Jun 2008 17:00:13 -0700 (PDT)
Message-ID: <739ab416-04c5-4672-8bb9-13bce0c6bed8@y38g2000hsy.googlegroups.com>


On Jun 28, 9:59 am, dana..._at_yahoo.com wrote:
> On Jun 28, 9:44 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
> > Yes this is a religious topic like flavors of unix and linux.
>
> > Mostly it is known as surrogate versus natural keys your terminology
> > seems to imply something else.
>
> Thanks for correcting me. The same issue could be stated as synthetic
> vs. natural keys. But your terminology is better paired than mine.
> There are simple keys and composite keys (also known as complex keys
> and concatenated keys). I tend to think of simple keys as surrogate/
> synthetic, but a Social Security Number is both a simple key and a
> natural key.

Well the SSN example again brings up problems. You don't want a primary key that can ever change ( fk relationships for example ) or be duplicated.

But what about identity fraud? How about when people ( as they can do rarely ) actually change their SSN.

In most cases and applications yes a lot of people do use SSN ... does it sometimes cause problems though?

> > It's been thrown around and debated and flamed on all/most of the
> > major database forums numerous times.  You can find several long ones
> > here on cdos if you search the archive ( accessible thru google groups
> > interface and possibly other ones ).
>
> Thanks HP, will do. I'm trying to learn. Not push a particular agenda
> and certainly not in a rude manner.

Not sure what Frank got into his bonnet for his reply. No worries. I don't hang out much on any of the other database forums but there's a lot of them. No shortage of discussions here in the past on cdos though. Of course some good discussions on Tom Kyte's asktom.oracle.com also. Received on Sat Jun 28 2008 - 19:00:13 CDT

Original text of this message