>>>>Here at my job we have a database modeler in our group. He is insistent that all tables use 'Natural Keys' and not surrogate keys. I am not trying to start a battle or anything, but is this really even still a debate? It does not matter how much logical reason I provide him, he is propagating his plan across the company and it does not seem to matter what the impact will be. This is a global company in 140 countries with data centers all over the world.
>>>>
>>>>My only questions is: Has something changed and I missed it? We are talking about values that users see and will want to change being used as primary keys on the tables.
>>>
>>>There are a few things that need clarification.
>>>
>>>Natural Keys
>>>Surrogate (generated) keys
>>>
>>>vs
>>>
>>>Meaningfull (intelligent) keys
>>>Meaningless keys
>>>
>>>
>>>
>>>The problem in the discussion is that definitions are not clear. Natural keys are keys that already exist in the real world. So for example a SSN or Passport No.
>>>The consensus is that the use of those keys is not reccomended.
>>>
>>>Surrogate keys are keys that are generated from the computer system. It could be a number from a sequence or GUID.
>>>
>>>However, a surrogate key might be an intelligent key. An example is an invoice number. An invoice number is an example of a key that (by law) should be absolutely static. There is little reason not to use an invoiceno as a key, as it is absolutely static by definition. Also note that the meaning of the key originates from the computer system, and not the outside world, even if it has created a meaning there.
>>>
>>>Another misconception is that surrogate keys should always be invisible for the user. A mutation, or payment might have a key that is generated within the computer system, but is visible in the GUI to be able to use that for pusposes outside of the system. (e.g a reference number in correspondence with customers). I've worked with Navision a decade and a half ago (now a Microsoft product) in which this practise is common).
>>>
>>>
>>>Personally, I use integer keys only as it simplifies audit trails and other metadata throughout the database.
>>
>>Excellent points Walter. I would have no issues with meaningful keys such as invoice numbers. I do tend to avoid using integers but primarily because I work in Enterprise systems and have been seen this bite the budget terribly. We had to dump a very large project to separate a large enterprise system into 3 different regional systems due to the use of sequenced integers for everything. There were already billions of rows in many tables and the cost to break this up was prohibitive. The integer keys was the breaking point. I would not have an issue using integers for lookup tables or smaller systems, but who knows sometimes where it will go. I see little harm or downside in using GUIDs for my efforts, so tend to stick to that.
>
>Same here. There is a lot of mis-communication and misunderstanding in IT. Many people go by Joe Celko's stuff, but even so, he's human and things are open to interpretation. This article is a good way to determine what kind of key:
>
>http://www.informationweek.com/software/information-management/celko-on-sql-natural-artificial-and-surrogate-keys-explained/d/d-id/1059246?
>
>First off, let's note that he does not say surrogate keys are bad. Artificial keys are bad.
>
>In "ACM Transactions on Database Systems," Dr. Codd wrote that "…database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them...
>
>This means that a surrogate ought to act like an index, hash table, bit vector or whatever; created by the user, managed by the system and NEVER seen by a user. That means never used in queries, DRI or anything else that a user does."
>
>I disagree with that last part. A surrogate key is a key. The user is not supposed to see it. The system is supposed to manage it. So if the user initiates a query, I see nothing wrong whatsoever with the system executing that query joining tables on the surrogate key, or the system managing referential integrity with that surrogate key.
>
>I would not use the invoice number as the primary key. There is a huge benefit to doing every table and key the same way. That benefit is "practice". You should not have to decide per table what kind of key. That, to me, is like a drywaller choosing between screws, glue, tape, per joint.
>
>If I have a 10 digit customer number in the customer table, and a 10 digit invoice number and a 10 digit line item number, to track payments against line items, what I have a 30 digit key on the line item? Everybody knows keys must be short! So how can anyone argue in favor of natural keys?
>
>Space is cheap, ram is cheap. This continual debating over primary keys is SOOOOO WASTEFUL to the entire planet!!!!

I think you'd better read this:

http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/