In the example given, the most logical natural key to me would be (year, rank). Why otherwise would they have the year in there?
That was my first thought too. But like TFA says, that still wouldn't prtect you from clerical errors.
You'll regret using natural keys
121–130 of 568 posts
Re: You'll regret using natural keys
#122> 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 ne…
We have the same problem in Denmark, most people just don't realize it. At my dayjob we get at least one person every year who changes gender and consequently gets a new SSN (the final digit is supposed to signify gender). Most people don't store SSNs so they never realize, but it does happen fairly frequently.
Re: You'll regret using natural keys
#123Earlier quoted context omitted.
Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a s…
Yeesh. I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me.
Can you share more about this? Wouldn't you run into the same problems if you used a surrogate pk? Without the nat/external pk and fk, you run the risk of having validity issues.
Conversely, if the ID changes, isn't the friction what you want?
I feel like optimising for unlikely edge cases instead of integrity because of a single incident is too reactionary.
Writing a query or script to update a string, even for millions of rows, isn't that big of a deal?
Obviously there _are_ cases where nat pks/fks are bad, but not all of them.
Re: You'll regret using natural keys
#124You 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 actuall…
How do you deal with the Ship of Theseus/Trigger's broom? There's literally nothing that defines said object apart from its history.
Re: You'll regret using natural keys
#125You 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 actuall…
This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…
> I challenge you to come up with a single plausible example
So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table.
So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your customers and there is this special offer just for Virgos and Pisces, you could enforce that the birth date matches this special offer. Some modern systems also technically allow FK on alternate keys, so you could still do it that way, but database theory often ignores that.
But second, while I agree that surrogate keys are often a good idea, I find your argument, that you must design for every conceivable change, not convincing.
Re: You'll regret using natural keys
#126It's also important to put as much thought into any synthetic key. It's easy to just use an auto-generated sequence... but then you start having to export/import or otherwise merge data, and other manipulations that often use the primary key and find there are collisions everywhere. There can also be problems when needing to support multiple databases, or update versions. UUIDs (or equivalent synthetic keys that are…
It's pretty easy to migrate to UUIDs later if required, as long as you don't leak your keys.
My work made their own uuid-like scheme, similar to UUIDv1 which incorporates several elements like machine ID and timestamp. The mistake was twofold: first they exposed them (so people started using them) but, worse, they made them easily reversible, so people started decoding the information in them. People would naturally see one and think "oh this is a record from place X". Of course that might not be true following subsequent data corrections, but the key can't change.
Re: You'll regret using natural keys
#127You 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 actuall…
Every time I used a natural key I have to come to regret it.
Re: You'll regret using natural keys
#128You 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 actuall…
When I integrate systems, I use that system's natural key (love it when it's a unique ID, but in the systems I work in - it almost never is).
That said, I use that natural key as the "link" to my internally managed, normalized database.
There's nothing that says I cannot add unique identifiers that would replicate the natural key. In fact, that's good design.
Re: You'll regret using natural keys
#129Earlier quoted context omitted.
This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…
> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your…
"for every conceivable change".
That is not what he is arguing at all. He is showing that there are very many highly plausible changes that are problematic with natural keys. And he totally correct about that. Frankly, the fact that a post arguing for natural keys makes it the top of an HN comment thread is extremely weird. The original article is correct that natural keys are bad.
Re: You'll regret using natural keys
#130Earlier quoted context omitted.
In the wise words of Patsy, "it's only a model". The real world is resistent to clean abstractions and abstractions are distressingly subject to change. What made your row unique today is quite likely to become non-unique in the days/months/years to come. Always use surrogate keys. Your future self will thank you.
And always kids, write code for one person, and one person only: your future self.
Yeah, fsck your co-workers and your replacement when you quit.