What are the best examples of natural keys in SQL?

user_2375346 0
Viewed 2267

I've been reading the great debates about natural vs surrogate keys in data modeling, and to be clear I'm not trying to get into that thorny question here. All I want to know is what are some of the best examples of good natural keys?

All I seem to find online are keys that someone thought might be good but turn out not to be, like social security numbers. (For that one: privacy concerns, not everyone has one, reused after death, can be changed after identity theft, can double as business tax id.)

My own guess is that internationally standardized codes (ISBN, VIN, country codes, language codes) would make good keys.

database-design sql
3 Answers

Invoice numbers, vehicle registration numbers, scheduled flight codes, login names, email addresses, employee numbers, room numbers, UPC codes. There are also many thousands of industry, public and international standards for everything from currencies, languages, financial instruments, chemical compounds and medical diagnoses. All of these are potentially good candidates for key attributes. Some sensible criteria for choosing and designing keys are: Simplicity, Stability and Familiarity (i.e. familiar within the business or other context in which they are used).

Some people seem to struggle with the choice of "natural" key attributes because they hypothesize situations where a particular key might not be unique in some given population. This misses the point. The point of a key is to impose a business rule that attributes must and will be unique for the population of data within a particular table at any given point in time. The table always represents data in a particular and hopefully well-understood context (the "business domain" AKA "domain of discourse"). It is the intention/requirement to apply a uniqueness constraint within that domain that matters.

For example, if my website requires each user to supply a unique email address when they register then email address may be a valid choice of key in the database supporting that website. The fact that there are other populations of people in other domains where email addresses are not required to be unique does not necessarily invalidate that choice of key for my website.

user_1917080

Assume, there is a table named person. When we use the columns LastName, FirstName and Address together as a key, then this will be a natural key as those columns are completely natural to people, and there is also a logical relationship between the columns in the table.

user_84651050

Your DNA code would be one really good example of a natural key in real life.

user_87018760
Related