Natural vs. Surrogate Keys

I am opening a very old question in this blog – natural vs. surrogate key.

I prefer natural keys. But before starting forcing surrogate keys, let me explain what the expression “natural key” means to me.

Think of it – what exactly a natural key is? Is this a name? A SSID, TaxID? All of this was invented by humans. As I am a human as well, I can invent my key, for example a sequential number; this key is as much natural as all mentioned ones. If the government who invented TaxID is natural, then I am natural as well. After couple of beers, I can actually be very natural. So, either all possible keys mentioned are natural, or all of them are surrogate.

As you can see, this common perception of what is a natural and what is a surrogate key has a lot of problems. Therefore we need a better definition. Much better definition was posted by Fabian Pascal: “Keys are natural if the attribute it represents was used for identification prior to and independently of the database.”

But, I still don’t like this definition. I have to warn you from here onwards this is not something you could find in books; this is my definition. I am changing Fabian’s words slightly; actually, I am just dropping couple of words: “Keys are natural if the attribute it represents is used for identification independently of the database.” What I mean is keys are natural for me if people use them. After all, a database is nothing more than a picture of some subset of real world. So, I do not care if people start to use the keys AFTER I define them in them in the database.

This is actually how things work in real life. People need invoice numbers, reservation IDs, even personal internal IDs (remember that you can have many people with the same names, and often you can’t collect all of the data you need to get a unique identification). Int and Bigint data types fits into this picture, as naturally in average we can easily emember 7-digit +- 1 digit numbers (ask yourself why do we have phone numbers of a specific length, and after that we introduce area and country codes?).