Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

41–50 of 568 posts

Re: You'll regret using natural keys

#41

Post by ethbr1 »

mythas wrote: 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…

One of the most surprising things I learned about the US healthcare system is how often everything is mutating or being retroactively updated (assume it applies to other countries too).

Things you'd think would be constant after Step 1 often aren't, and processes are tolerant of their being corrected / re-entered after Step 52 has already been completed.

Re: You'll regret using natural keys

#44

Post by polemic »

jklowden wrote: post #27

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…

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.

Re: You'll regret using natural keys

#45

Post by romdev »

mythas wrote: 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…

Finding and eliminating duplicates is a very common software problem that is rarely solved in a reusable, user-friendly way that preserves history while eliminating redundant data. In fact, in 40 years of working with computers I can't think of a single UI that I'd want to emulate.

Re: You'll regret using natural keys

#46

Post by tadfisher »

bongodongobob wrote: post #15
davidhyde wrote: post #10

A 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.

Another reason is sharding, in which case any non-random (to be more precise, non-uniformly distributed) bits are going to skew the partitions.

Re: You'll regret using natural keys

#47

Post by eddd-ddde »

bongodongobob wrote: post #15
davidhyde wrote: post #10

A 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.

Why is knowing someone's ID an issue?

They are meant for identification, if anything it should be a benefit that they are easily guessable.

Re: You'll regret using natural keys

#48

Post by yuriks »

rhelz wrote: post #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…

> Suppose you take the advice of this article, and use, say, social security numbers to identify people.

You seen to have misunderstood the point of the article: the author is recommending NOT using the SSN (a natural key) for primary keys, and instead to use an artificial, automatically generated key, so that the SSN is decoupled from the record and can potentially be updated.

Re: You'll regret using natural keys

#49

Post by hobs »

crabmusket wrote: post #40
jklowden wrote: post #27

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

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers.

You want to change that human key? Sure. You want to to complain that the keys are not sequential? Sure. You want to actually make them weird strings that are harmful to my brain? Why not? You want to update primary keys in the database? No. Absolutely not.

Re: You'll regret using natural keys

#50

Post by arthens »

azlev wrote: post #20

I think somewhat different than the article in some ways. The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works. The drawback of enumeration is real, so if you expose this key you'll need some authentication/ authorization mecanism. Another good thing in natural keys is that you can eliminate…

> The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works.

This is only true in a system using a single database, not replicating data in external services, and not offering APIs.

And while it might work today when the service/website is still fairly small and self contained, the requirements might change at any time. So the title (You will [future] regret it) still applies to this solution

Post reply on HN