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

331–340 of 568 posts

Re: You'll regret using natural keys

#331

Post by inopinatus »

biztos wrote: post #231
strnisa wrote: post #220

Earlier quoted context omitted.

Thanks for all the improvement suggestions! Taking them into account, the `makeSlug` function becomes: function makeSlug(length: number): string { const alphabet = "0123456789abcdefghjkmnpqrstvwxyz"; let result = ""; for (let i = 0; i

I think that 0 and 1 are likely to cause problems when customers end up reading their "user ID" back to your employees in Customer Support Country over the phone. "It's one-three-oh-dee-ee-el. Yes, I'm sure, EL as in elephant."

My favourite ambiguous readback is to say "M for Movember".

I have a note from a few years ago that 367CDFGHJKMNPRTWX may be a sufficiently unambiguous alphabet. Drop the one you like the least (probably N) to obtain a faux hex encoding.

Re: You'll regret using natural keys

#332

Post by sgarland »

mrkeen wrote: post #142
paulddraper wrote: post #91

Earlier quoted context omitted.

Terrible advice. Surrogate keys are keys are a layer of indirection. They don't fix all problems, but they fix some problems. Not least of which is performance. Often natural keys are character strings, whereas surrogate keys can be fixed size integers, saving index sizes on your FKs.

> surrogate keys can be fixed size integers This launches into the other debate about PKs: using UUIDs rather than sequential keys.

This is less of a debate, and more of an indicator of who has had to work with a DB at scale using UUIDv4 everywhere.

Don’t blow up your B+trees.

Re: You'll regret using natural keys

#333

Post by mst »

jval43 wrote: post #304
twobitshifter wrote: post #279

Earlier quoted context omitted.

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

It's never a single database in the real world. As soon as you integrate something or have an API to something the keys are out there. Unless you add a translation layer, but then you could just as well use surrogate keys directly.

Using a surrogate UUID for communicating with the outside world is often very useful.

This is true for an internal PK that's an auto-inc id as well as for natural keys, though.

Using a natural PK -inside- your own database can still be a lot more pleasant to work with, even if you don't let it escape.

Re: You'll regret using natural keys

#334

Post by twobitshifter »

mewpmewp2 wrote: post #297
twobitshifter wrote: post #279

Earlier quoted context omitted.

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

> My impression from the article is that this is a single SQL database being discussed. Even if it's initially single, it's bad to assume that it will be so forever and that you are not going to use third party providers in the future. How well does ON UPDATE CASCADE work if there's millions of existing relations to that entity?

YANGNI for 99% of projects and databases. When you get to global sharded nosql etc. you need to use UUIDs for anything and incrementing IDs falls over too.

Re: You'll regret using natural keys

#335

Post by cmiles74 »

selcuka wrote: post #54
Kon-Peki wrote: post #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 ne…

> The US SSN is not guaranteed to be unique The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.

People also type them in wrong, so there's another thing: where is the data coming from.

Re: You'll regret using natural keys

#336

Post by delusional »

winternewt wrote: post #155
delusional wrote: post #122

Earlier quoted context omitted.

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.

If Denmark is anything like Sweden there's also: - SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN. - SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old. - When an unconscious patient comes in to the E.R. you don't know their SSN, s…

> - SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.

We don't do that. instead, we shove that extra bit of information into the digit following the last two digits through a table: https://da.wikipedia.org/wiki/CPR-nummer#Under_eller_over_10...

Re: You'll regret using natural keys

#337

Post by vharuck »

mikedelfino wrote: post #268
Kon-Peki wrote: post #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 ne…

How to uniquely identify an American citizen?

The population health database I work with uses generated IDs for people and relies on health systems to link records before submission (they put a lot of effort into data used for billing). But we do check for problems by looking at duplicates of SSN with date of birth. We don't consider names, because they could be transcribed differently. Even though the SSN+DOB pair should be unique, there have been cases where a widow provides her deceased husband's SSN. Likely because she used it for Social Security benefits and forgot her own long ago.

Re: You'll regret using natural keys

#338

Post by ltbarcly3 »

aaronbrethorst wrote: post #6
ltbarcly3 wrote: post #5

Sometimes you will regret it, other times you won't. Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.

That’s quite the absolute statement to make without even one example.

I don't think you read it carefully.

Re: You'll regret using natural keys

#339

Post by sgarland »

twodave wrote: post #319
wavemode wrote: post #43

Another massive annoyance with natural keys - privacy. If your table's primary key contains personal information, that PII now infects every other table that holds a foreign key to that table.

Agreed, and also they tend to be more guessable. Make a page available with an email address as the id and just watch the hackers use it to discover users of your service and attempt to log in as them.

That is an AuthZ problem, orthogonal to DB key debates.

Re: You'll regret using natural keys

#340

Post by nostrademons »

There's a better solution for many of the exceptional cases that the author describes: aliases & audit logs.

Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. If a person's CPR number changes because they've changed their gender, you will want a separate table recording a.) the date of the change. The new CPR number is not valid before that time b.) the new gender c.) probably the reason for the CPR number change, since if the policy now is that they can change because of a gender change, there's a decent chance they'll be some other policy in the future that results in a new CPR issuance.

Or the chassis number. Also fine as a natural key. If it's changed because of a data-entry error, you also want to record a.) the date of change b.) who changed it. This opens up a whole host of auditing, monitoring, and reporting functionality that eg. lets you catch fraud, determines if a single person is being sloppy, identify mass changes in policy, notify and update external records of owners, etc.

URLs are another good natural key: they are defined to be unique (otherwise your webserver won't work), they make for very easy lookups when you're fetching from a web request, and if they change, they break the web. Except that they do change. But when a URL changes, you don't want to just update them in the database everywhere, because again, that will break the web. You want to leave a redirect from the old to the new one. So you create a redirects table of all the other aliases that point to a given page, use it to generate server redirects, and you can throw in other data like the time of change or hit counts on each individual alias.

Post reply on HN