BrettK (a.k.a. x002548), a member of the SQL Team, posted an entry entitled Surrogate Keys...the Devil's spawn (OK Not really). As Brett acknowledges, this topic always sparks a heated debate. He listed 6 cons of working with surrogate keys, but only one pro. He also asked what other situations might merit the use of a surrogate key. Here's my take on one:
Surrogate keys can be plenty useful when it's a pain in the neck to have to carry around 5+ fields to do a join on the natural key. For example imagine an entity, Party where you have a single ID (datatype irrelevant) that serves as the PK. You want to model the relationships between two Partys over time. You create an entity PartyRelationship that contains both Party's id's and the start and end dates for the relationship. However, two parties can have several relationships. You may be a Microsoft employee, but also a customer. So let's assume a RelationshipType entity that has a FromPartyRole and ToPartyRole and a Description. To capture all these requirements, your PartyRelationship entity now has FromPartyId, FromPartyRole, ToPartyId, ToPartyRole, StartDate as it's "natural" PK. EndDate is not needed in the PK. With me so far? Next, assume you have entities that relate to PartyRelationship (think of Agreements perhaps) and you can see that you're going to have to carry around these five fields in your join. I would much prefer to have a clustered, unique key on the five fields and have a surrogate key, PartyRelationship Id to simplify my queries. As for where I pulled this hypothetical from...SQL 2000 Enterprise Edition ships with the Silverston Data Model in its samples directory.
Oh and the #1 Reason not to use surrogate or identity columns...
People tend to use them like it's the ONLY way it's suppose to be.
Ever see a database that surrogates (identity) PK's in EVERY table?
I have.
Scrubs
Posted by: at June 9, 2004 04:12 PMWell that's why I mentioned the Address...kinda inconvenient to hav street address, city, state, zip as a composite PK...ya know a hundered byte key is not very attractive, and I guess size does matter ;-)
But to say 5 column composite is to unwieldy...I'm not sure I by it...
You want to say 10, 15, then ok...but the a lot of nested relational levels...my best guess is like, what 4 or 5 at the most?
That's a good question.
What's the deepest levels of relationship have people seen?
And Thanks for the reference.
I'll add the relational depth argument to the Pros
Posted by: at June 9, 2004 03:59 PM