| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Surrogate Key vs Production Key
Dusan Bolek wrote:
> krislioe_at_gmail.com (xtanto) wrote in message
news:<e1c9bd55.0410052139.2c139132_at_posting.google.com>...
>> 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 ?
>>
>> What is the cost/benefit ?
> The biggest problem of not using surrogates is that you do not have > the control over used key values. In this particular case it's going > to be funny when you will get another external resource for these > documents with overlapping keys. > I'm recommending to use external keys only for world wide accepted > keys that are enforced by laws, compliance rules etc. (such as social > numbers, ZIP codes, ...), I would never use external key based just on > someone else design. > --
Since a unique externally generated key is provided it would seem logical that the documents will also be accessed via this external key. If this is true then using the surrogate key will require translaction from the external key to use it to begin with. Why not just use the external key? It would save space (extra index) and unnecessary work finding the surrogate key value to be used.
Important questions whose answers are important to the proper choice of natural verse surrogate keys is the fact of whether the key will ever change? What should happen to related data at the time of change (discard, attach to new key value, archive)? How many related tables are there? And how will the data be queried?
Based on 20 years IT industry experience I have found that in general a natural key should be used in preference to a surrogate key, but there are places for both.
HTH -- Mark D Powell --
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 |
 |