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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 30 Jun 2008 12:56:58 -0700
Message-ID: <1214855808.369775@bubbleator.drizzle.com>


Mark D Powell wrote:

> 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.

On one hand you are correct ... SSN's, in the US, are not unique. On the other hand for purposes of computer systems they are a great natural key in that those circumstances where you need to have them they'd better be unique.

What I'm thinking here is banking, credit cards, taxes, etc.

We all know there are dups but something must be done if a dup is found. A surrogate key that allows this to happen sidesteps the quick way to identify the dups and leaves open the possibility of far worse things happening.

The sad fact, too, is that 99% of dup SSNs are the result of identity theft ... not governmental incompetence.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jun 30 2008 - 14:56:58 CDT

Original text of this message