>Hi Jeff....
>
>>I'm having a discussion with my boss and a fellow co-worker who is a dba about the usage of surrogate keys for primary keys in tables (he calls them "artificial keys").
>
>LOL. Well, keys that naturally occur in a table are called "natural keys"....I suppose that they are using the reverse of that definition.
>
>>My argument is for using single-field sequence numbers as primary keys for every table and using single-field foreign keys in any child tables (which would also contain their own single-field primary keys). My reasoning is that these sequence key fields will never change because they're not part of natural data. I'm also arguing that this approach is more efficient for SQL joins, indexed seeks, etc.
>
>Agreed. Primary keys should always be single field. And surrogate keys are best when not tied to any real data for the reasons you stated, although I don't see where the type of key is any mroe efficient as long as it's single-field and unique.
>
>>My colleagues on the other hand are saying that it's a mistake to use "artificial" keys because they aren't self documenting in the code. They even argue for composite keys to be used when multiple fields uniquely identify a record.
>
>Excuse me? All of my primary keys are called "pk" and all my foreign keys are called "fkEntity"; i.e "fkCustomer" (always singular when 1-to-1). How much more "self-documenting" do they need??
>
>(Cut for brevity) I agree with your model. Completely. I think you need to take a copy of the Codd and Boyce-Codd rules for normalization and tack them to your co-workers foreheads.
Thanks all for validating what I suspected to be true.
Are there any books/articles/papers I could find preferrably written by known experts in the field that could back up my argument for using surrogate keys?
Thanks again,
-JT
Jeff Trockman, MCP