Walter Meester
HoogkarspelPays-Bas
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Conception bases de données
Versions des environnements
SQL Server:
SQL Server 2012
>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.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement