Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

31–40 of 568 posts

Re: You'll regret using natural keys

#31
Natural keys are (quite literally) essential to defining entities. Quine had a great slogan for this: "No entity without identity!" Natural keys are how you determine the identity of an object, which is to say, if you have two different referring expressions, how can you tell whether they are referring to the same object, or different objects?

Suppose you take the advice of this article, and use, say, social security numbers to identify people. (Let's ignore the fact that this only works for the U.S.) Suppose one fine day, somebody tries to enter in a record about a new person--but some typo has happened somewhere, and the new person's SSN clashes with somebody's SSN who is already in the system.

Sure, the database will notify you that something went wrong. But how do you know which social security number is correct, and which is incorrect? You will have to find a set of fields which uniquely identifies each person ANYWAYS. I.e. you'll have to differentiate them by name, birthday, place of birth, etc etc.

Now even worse!!! What if somebody attempts to add the same person TWICE to your database, but mistypes their social security number. Now your database can't even tell you that something has gone wrong. It will happily record duplicate or contradictory information about the same person--and in order to resolve the mess, again, you have to find out what uniquely identifies the people ANYWAYS.

Now, even worser than worse--what if you have taken the advice of this article, and you haven't bothered to identify a set of fields which are genuinely unique to each person. You just have a social security number and a name. How are you going to even going to correct the fact that John Smith is in your system twice, when you have ten John Smiths? How can you possibly tell which two John Smiths are the same John Smith?

Yeah, it take some time and careful thinking to properly come up with natural keys for your entities. But unless you do, you haven't actually specified your entities at all. This is one area where long years of experience really pay off. Expert data modelers spend years and decades honing their craft, observing the work of others, etc etc. Eventually they acquire the wisdom needed to be able to know what kind of information is really needed to nail down what kind of entity the database needs to know about.

Re: You'll regret using natural keys

#32

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.

Perhaps a better example would be a fingerprint or a retinal scan instead of social security numbers as natural keys for a person.

Re: You'll regret using natural keys

#33

There 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

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

Re: You'll regret using natural keys

#34
post #13
post #8

Earlier 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…

Discord's real user ID is a very lengthy number not visible to end-users nor used for other purposes. https://support.discord.com/hc/en-us/articles/206346498-Wher...

Which only reenforces the point that using what is exposed as the ID is not safe as a natural key.

Re: You'll regret using natural keys

#35
post #13

Earlier quoted context omitted.

Discord's real user ID is a very lengthy number not visible to end-users nor used for other purposes. https://support.discord.com/hc/en-us/articles/206346498-Wher...

Which only reenforces the point that using what is exposed as the ID is not safe as a natural key.

You're reading this wrong. The real ID (the Snowflake) is exposed in the API, and the UI if you turn dev tools on. That's the one I use. You don't use @username#1209 but 1247755691426542433.

Re: You'll regret using natural keys

#36
post #33

There 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

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

Yes but any kind of bug could later introduce tainted values. A foreign key contstraint might save you, but not always.

Re: You'll regret using natural keys

#37
post #28

Another 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…

This sounds like it should probably be a workflow instead. Modelling the intent here is actually important.

Re: You'll regret using natural keys

#38
post #37
post #28

Another 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…

This sounds like it should probably be a workflow instead. Modelling the intent here is actually important.

Care to expand on this thought? Curious what you have in mind!

Re: You'll regret using natural keys

#39

You 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…

Of course they won’t save you from external data. The whole point is for your system to have a way to identify rows internally so you can deal with external systems getting wonky without corrupting your own data.

All of your concerns are easily solved by a unique index.

Using external keys as a forcing function to prevent people from representing data wrong is not great.

Re: You'll regret using natural keys

#40

You 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…

The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two!

I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.

Post reply on HN