For those who do not know, natural keys are when you have a primary key in a database table that is derived from the data itself.
An example is using a car's chassis number as the key for the record describing that vehicle.
You'll regret using natural keys
21–30 of 568 posts
Re: You'll regret using natural keys
#22A natural key is what I would normally call unique index. Say first, surname and date of birth in an employee table as a bad (poor design) example. As opposed to a surrogate key like personId being an auto incrementing ID which is the norm since it can be easier to use when joining tables. I wish articles like these would explain terminology up front. I found it irritating to wade into the anecdotal trivia and not kn…
One reason to not do that is because you could then figure out other users IDs by their hiring date. This ended up being a problem for me once with the auxiliary systems that relied on that ID, namely when it was used in links in other applications.
[deleted]
Re: You'll regret using natural keys
#23Feels like this could have used a few more solid examples up-front. I think another good example would be PlayStation Network using the natural key of "gamer tags" as the primary key to identify players would be a good example. Since this effectively locks players into having to keep a gamertag in order to uniquely identify them in the service -- instead of having a synthetic key that carries no meaning or data other…
Last I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.
Re: You'll regret using natural keys
#24There another reason not mentioned — if your key is something like a UUID, it’s very easy to define the logic for joining and filtering based on that key.
If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE
Re: You'll regret using natural keys
#25The way I think of it is a key, in a database, is a record key. It is intended to identify a particular logical record, not the logical thing which is referred to by the record.
An an footnote, I'll add this doesn't mean that you need to have complexities like record versioning, record history, or anything. But couched in those conceptual terms where those things are possible, is a happy and safe space to be. In this space, a database records entries, as if they were each a single paper form with boxes where you fill out, in pencil, to erase if you like or not, the particulars of the thing you're recording. This form comes pre-stamped with a number: the record key.
In this cozy little world, you can be imperfect, and mistakenly (or deliberately, as your use case requires), file multiple slips that refer to the same logical thing, and yet all have different file ("record") numbers - or keys.
Re: You'll regret using natural keys
#26The devil's advocate cases: 1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense. For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever…
A synthetic key is adding an additional dataum - so any problem that could be solved with the original data can be solved by the new data too (worst case, ignore the primary key). That is one of the best reasons to add a proper index - there is almost literally no downside. We're talking maybe a few bytes/record and arguably a trivial amount of administrative overhead.
Bearing that in mind:
1. Add a unique constraint on the natural key.
2. If the accuracy of joining by natural key is acceptable, join based on it. If that doesn't work, then a table design without a synthetic key wouldn't be fit for purpose either.
Re: You'll regret using natural keys
#27You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actually a choke point in your data throughput.
The natural key forces you to think about what makes the row unique. What identifies it. Sometimes, it makes you go back to the SME and ask them what they mean. Sometimes it makes you reconsider time: it’s unique now, but does it change over time, and does the database need to capture that? In short, what are the boundaries of the Closed World Assumption? You need to know that too, to answer any "not exists" question.
To use our professor’s car’s example, we actually do not know the database design. It could well be that the original identifier remained the primary key, and the "new id" is entered as an alias. The ID is unique in the Car table, identifying the vehicle, and is not in the CarAlias table, where the aliases are unique.
Oh, you say, but what if the bad old ID gets reused? Good question. Better question: how will the surrogate key protect you? It will not. The reused ID will be used to query the system. Without some distinguishing feature, perhaps date, it will serve up duplicates. The problem has to be handled, and the surrogate key is no defense.
Model your data on the real world. Do not depend on spherical horses.
Re: You'll regret using natural keys
#28Another example that happens surprisingly often in healthcare. A registration clerk will incorrectly enter a personal health number (PHN) into the system. Then the actuall holder of that PHN shows up. If this were the PK then the system just wouldn’t handle this case and the reg clerk would have a huge mess to sort out on the spot. A surrogate key on the Person table allows this registration to be made where 2 people have the same PHN in the system. Then cleanup can be handled after the fact to track down the first person, determine their correct PHN and update the record.
Re: You'll regret using natural keys
#29Earlier quoted context omitted.
I think this is an excellent example of one of the pitfalls that the author is getting at. (Disclaimer: not a Discord bot programmer.) Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you? (For the unfamiliar: somethi…
It probably leaves you in the same place you’d be with a synthetic key of your own: unable to track users across this key change without additional Discord data.
Yes, but if you then reference that as a foreign key in some other table or system, then whatever migration you need to do will encompass those systems as well, whereas if you use a synthetic key from the start you wouldn't need to change them.
Re: You'll regret using natural keys
#30> how about a personal identification number? In Denmark we have the CPR number, and I understand that the US Social Security Number is vaguely analogous.
The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t need one unless you do something that requires having one. There are also things called ITINs and ATINs that look like an SSN but are not, yet can be used in place of an SSN in a huge range of SSN-required situations!
(Please don’t use the SSN as a database key!)