The problem is: As soon as a synthetic key is used by an EXTERNAL system, it stops being synthetic; for all intents and purposes it is now an integral part of the record, because if you change it, the external system may not be able to find the record any more.
You'll regret using natural keys
171–180 of 568 posts
Re: You'll regret using natural keys
#172In databases, never rely on data you don't control. "Natural" keys are an example of this.
Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name.
Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly.
Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once again you don't control them. So if the license plate numbering scheme changes in some way that breaks your system, too bad. Or people without an SSN. Or people in transition because an SSN needs to be changed in a government system somewhere. Or any other number of things that happen in a government office that affect you, yet you have no control over.
Phone numbers? Well, we've already seen that mess with many messenger platforms.
Fingerprints? Guess what? They evolve over time, and your system will eventually break.
Retrofitting a system that relies on "natural" keys that have broken SUCKS.
Use a generated unique key system that YOU ALONE control.
The first rule of software design is: Don't try to be clever. You're not clever enough to see all of the edge cases that will eventually bite you.
Re: You'll regret using natural keys
#173You can get performance benefits from using natural keys, as many databases store rows in the order of a table's primary key (sometimes called the clustered index, though it may or may not have a unique constraint requirement depending on the DBMS and a few other bits). In the author's example, if the first column in the natural index was the city name (or city ID!), and locations are often pulled from the database b…
> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...])
Note for readers: Postgres doesn't do that
Re: You'll regret using natural keys
#174Just to add on to the CPR number. As mentioned it contains a birthdate and a gender identification as well as a checksum. Since many refugees don’t know (or can’t prove) their birthdate, they are given first of January. And enough first of Januaries are handed out that for some years there aren’t enough valid numbers, so numbers that fail the checksum are handed out too.
Shows how the assumption that everyone has, or reveals, a known or at least approximate birth date cascades through the system.
Re: You'll regret using natural keys
#175> 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…
In Spain we have the DNI number, that a lot of people asume is unique, even database designers that use is as a natural key. Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it ( https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/ ), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number…
I've seen banks or insurers use DNI as user login.
Re: You'll regret using natural keys
#176Earlier 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.…
> then we add the ability for users to update their email. At this point, you should verify the new email. At least until it is verified, you must track the old email. At this point, you realize you can now introduce a synthetic key and you're fine. Let's say you have a duplicate customer entry and the customer demands their accounts be merged. Now you can't identify the user by their key alone, since by definition,…
> At this point, you realize you can now introduce a synthetic key and you're fine.
Except for having to update all foreign references. Some of those may be external further complicating issues.
Emails are often among the worst keys because they are not terribly stable and they are reusable often enough to burn you.
Re: You'll regret using natural keys
#177Yes, except sometime whole industries come together already has developed a system of unique identifier, and I can just use this as a natural key. For example, the Nasdaq stock exchange is identified perfectly fine by the string 'XNAS', I'm not going to generate an ID on top of it.
Re: You'll regret using natural keys
#178I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…
I don't like the prefix idea: besides the duplication of information, it also becomes a liability if you ever rename things.
Imagine you prefix all customer IDs with `cus_`, but at some point decide to rename Customer to Organization in your codebase (e.g. because it turns out some of the entities you are storing are not actually customers). Now you have some legacy prefix that cannot be changed, is permanently out of sync with the code and will confuse every new developer.
Re: You'll regret using natural keys
#179You 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…
Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad adv…
>As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe?
Yes and no. It is the Axe of Theseus ;)
Re: You'll regret using natural keys
#180Earlier quoted context omitted.
yeah the way he described it it's like... well who would ever do that. but foreign keying to emails or usernames is much easier to "accidentally" do and is a classic source of long-term headaches.
As a great example: My steam account name is the email I was using in 2003. I have largely not used it since then. The email on the account has been updated, but the account name? Stuck.
I was about to ask how you got past steamguard without email access. Makes sense if you were able to navigate off it.