Myltiple surrogate keys in one table!

From: Mikito Harakiri <nospam_at_newsranger.com>
Date: Wed, 11 Jul 2001 00:58:00 GMT
Message-ID: <swN27.14728$Kf3.172552_at_www.newsranger.com>


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

Therefore, I'm unable to come up with any reasonable justification for 2 surrogate keys in one table. Maybe somebody else have some? In general, if database A has a CUSTOMERS table with surrogate ANUM column, and then database B designers created similar PARTIES table that could be filled from database A CUSTOMERS table, so that they added PARTIES.ANUM reference column, would this column be a surrogate key? Received on Wed Jul 11 2001 - 02:58:00 CEST

Original text of this message