Re: Surrogate Key vs Production Key

krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0410052139.2c139132_at_posting.google.com>...

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

You have to store the PK value as a FK in the detail table no matter what you choose as your PK, don't you? Unless you want to forego the declarative referential integrity.

> OR
> Should I create an ID based on Sequence as 'Surrogate' PK ?

If you do that, you still have to store the surrogate value as a FK in the detail table.

>
> What is the cost/benefit ?

6 of one, half a dozen of the other.

The surrogate value can be unconditionaly guaranteed to be unique. An external defined value is unique now and may not be later on.
On the other hand you can always adopt a surrogate value later on. But it will mean major updates and downtime.

If you have to search on PK, then an external value may be easier/more mnemonic to your users than an abstract surrogate key. It may therefore make more sense to use it instead of surrogate values.

But, given that we are not talking 30 year old hardware (I hope!), then why not have the best of both worlds?

Use a surrogate PK/FK combination for the referential integrity and use the external unique value as well for searches on master table. The small increase in row size (at most 30 bytes/row) is not gonna kill the hardware unless you have absolutely enormous numbers of rows.

Use the extra capacity of modern systems to work around this sort of problems, quite frankly. Received on Wed Oct 06 2004 - 07:55:03 CDT