Re: Surrogate Key vs Production Key

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<vbp7m0ts13f3ptp8n13cpi7639nqi4ne8g_at_4ax.com>...
> On 5 Oct 2004 22:39:00 -0700, krislioe_at_gmail.com (xtanto) wrote:
>
> >Hi Gurus,
> >
> >I have a transaction table having unique external document number. It
> >can be assured that it is unique. It is a varchar2(30).
> >This is a header-detail transaction.
> >
> >The options is :
> >Whether I should use this unique external document table as PK ?
> >(means I have also to store this value in Detail table)
> >OR
> >Should I create an ID based on Sequence as 'Surrogate' PK ?
>
> In which case you have to store the surrogate PK in the detail table.
> Either way, the detail table has to include a column with the PK of
> the parent table. So, except for the possible, minimal space savings
> by the artificial key being smaller than the natural one, that part of
> the argument looks like a wash.
>
> >
> >What is the cost/benefit ?
> >
> I'm not quite as jaded as Dusan regarding the mistrust of natural
> keys, but I certainly understand the arguments both ways. In the end,
> I'd try to take it on a case-by-case basis, looking at the liklihood
> and impact of a change of value to the natural key. Also the impact
> of being able to change it *too* easily. Just off the top of my head,
> think about Social Security Number. For most people this is an
> absolute. But there are cases where people's SSN changes. Or it flat
> got entered into the system incorrectly in the first place. I've even
> heard of (never confirmed) rare instances where two people actually
> had the same SSN. With so much tied to it -- both in and out of your

http://www.ssa.gov/oig/ADOBEPDF/audittxt/A-08-00-10047.htm single person, multiple ssn's.
http://www.ssa.gov/oig/ADOBEPDF/audit_htms/97-62003.htm Duplicate SSN used in fraud.
http://www.networkusa.org/fingerprint/page6/fp-ssnfaq.htm One of the faqs.
http://www.databasenation.com/scans/SSNReport2001.pdf privacy.

> system -- do you really want to treat it as just another attribute of
> some sequence number, no different than street address or marital
> status?
>

Any externally generated key is suspect.

jg

--
@home.com is bogus.
http://freedompage.home.mindspring.com/ScottApp.htm this is pretty
funny.