Re: Myltiple surrogate keys in one table!

In message <swN27.14728$Kf3.172552_at_www.newsranger.com>, Mikito Harakiri <nospam_at_newsranger.com> writes
>Now and then somebody pops up a question if using surrogate key is a good
>practice. I'm coming across, however, a table with more than one surrogate key:
>
>PARTY_ID
>PARTY_NUMBER
>
>One possible explanation might be that PARTY_NUMBER is not really a
>surrogate in
>the same sence as SSN is not.

That sounds about right. Of course the SSN *is* a surrogate key, in that it's not an intrinsic property of the individual it identifies.

> For example, it could be reserved for future
>applications that might interpret it as something meaninful, for example, a
>number from Dun and Broadsteet catalog. On the other hand it is more natural to
>dedicate a special column DUNS for that purpose. I would suggest that this is a
>typical situation for ERP applications, when surrogate keys from multiple
>systems propagate into a single table. Those are not surrogates, however.

If someone else assigns a surrogate key to something like a party then I could trust them and adopt their surrogate key as my primary key. This is what happens when someone uses the SSN as a primary key to identify a US citizen.

That only works when the surrogate key is always available and is guaranteed to be unique. If I don't always have access to the SSN or I suspect there may be duplicates then I can't use it as a PK. In that situation I might create my own surrogate, and I could have multiple surrogate keys in a record. But there would only be one that I trusted, the one I created for myself.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti