Sean Lange (12/28/2012)
Joe I certainly understand your position but I do have a question. You continually refer to Cindy Lou Who as employee #42. In a Joe Celko database what would be used for the primary key in this table?You can't use SSN. That would be a reasonable choice at first glance but there are two MAJOR flaws with that. First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool. Secondly, and probably far more important, is that SSN is sensitive information. Anytime you have SSN in your database it should be in one and only one location and it should be encrypted at rest. This pretty much rules out SSN as a valid primary key. What else is there? Name certainly doesn't do it.
Not to mention that some employees may not even have SSNs: foreign representatives, etc..
An identity is clearly applicable to many things, including (but not limited to) employee numbers and order numbers. Obviously you create a unique constraint of some type to guarantee uniqueness.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.