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

On Jun 28, 8:00 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> 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.

Here is another fact involving Social Security Numbers that many people do not know. SSN's are not truely unique as the Social Security Administration re-issued around 10,000 numbers (technically due to an error). While I believe the original holder for all these numbers were supposed to be dead some insurance companies and credit card issuers with extensive databases have probably encountered the issue.

The Social Security Administration has also reused the same SSN for surviving Foreign spouses of US citizens by appending a single character suffix so your 11 digit numeric character SSN can be a 12 digit alpha-numeric if the surviving spouse ever immigrates to the US.

And as hpuxrac mentioned in a few thousand cases due to identity theft some people have had new SSN's issued to them. I discussed the ability to update a PK in my prior post and will not rehash it here.

I will add that because of its sensative nature the SSN should likely always be an encrypted data item and not used as the key for child tables.

• Mark D Powell --