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