Re: Surrogate Key vs Production Key

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

I think that, in the end, the answer is the ever popular "it depends."

>Thank you for your help,
>xtanto
Received on Wed Oct 06 2004 - 07:46:34 CDT