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

351–360 of 568 posts

Re: You'll regret using natural keys

#351

Post by sgarland »

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…

Your post brings up a critical difference I’ve noticed when working with devs (I’m a DBRE): those who actually do rigorous data modeling, and those who view it as an annoyance impeding their project.

Spend time modeling your schema. Ask yourself, “does every attribute in this table directly relate to the primary key? And is every attribute reliant upon the primary key?” Those two alone will get you most of the way through normalization.

Re: You'll regret using natural keys

#352

Post by vanderZwan »

kstenerud wrote: post #316
biztos wrote: post #231

Earlier quoted context omitted.

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

I developed safe32 for this reason. https://github.com/kstenerud/safe-encoding/blob/master/safe3... Notably, confusable characters are interchangeable when being ingested (although a machine encoder MUST always produce canonical output). https://github.com/kstenerud/safe-encoding/blob/master/safe3... So a user can confuse 1 for l, 0 for o, I for l, u for v, uppercase, lowercase etc, or the agent can say any of those…

Oh that looks well-thought out and is probably the sanest way to solve this particular problem!

It's obvious why the safe64/safe80/safe85 cannot do this, but is there a reason why the safe16 version doesn't have the same features?

Re: You'll regret using natural keys

#353

Post by jonstewart »

stiiv wrote: post #348

There is still something to be said for "real world uniqueness" (GIS coordinates) or deferring to a third party to establish identity (license plate numbers). Identifiers like these aren't always available, but within many domains will be sufficient. The idea here is not that these keys can't be somehow "invalid," but rather that it isn't our system's problem -- it belongs to some other authority.

TFA author cites many examples from human-oriented systems, which perhaps are more common and traditional domains for RDBMS design, where there often several layers of exceptions to the rules - his argument that synthetic keys work well for these domains lands.

I personally work far more with computer-oriented systems and their data, and natural keys work well for me. When well-chosen they allow me to do an initial load of the source data for analysis, and then aggregate such databases together later on for historical analysis without fear of conflict. The data are often immutable in these domains, too.

Re: You'll regret using natural keys

#354

Post by kalleboo »

tapland wrote: post #344
kassner wrote: post #245

Earlier quoted context omitted.

Also: the personnummer carries a date that often means birth date, but there are cases where it’s not, but I’ve seen a few system that just assumes it’s the same. > SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN Having gone through that, my personummer didn’t change. Maybe that doesn’t happen anymore?

It does, but if you already have a Personnummer or get a residence permit right away so that you are eligible for one you don't get the temporary Samordningsnummer.

And the samordningsnummer isn't only for immigrants - it's also for Swedes born abroad who have never been folkbokförd.

Re: You'll regret using natural keys

#355

Post by hateful »

After reading many threads here, I think the final ruling may be: - If your record represents a physical being or object: Use a surrogate key.

People change.

Unrelated - if you have a list of emails or SSNs or license plates or VINs - we can think of these as foreign keys to a database we don't control.

Re: You'll regret using natural keys

#356

Post by mewpmewp2 »

twobitshifter wrote: post #334
mewpmewp2 wrote: post #297

Earlier quoted context omitted.

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

I'm using UUIds by default for everything. Main point being that I don't have to worry about future restrictions.

And incrementing IDs are also problematic yes, since they hide business information data within them.

And I do think that I need it for much more than 1% of projects and DBs.

Re: You'll regret using natural keys

#357

Post by polygotdomain »

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?

To give a slightly more technical answer, at a large insurance company I used to work for, the legal department had provided definitions on what conditions we would consider various components of PII a match. So between SSN, DoB, First Name, Last Name, and a couple others, there were potential combinations that our system would say, "yes this is the same person". Note that we didn't necessarily need exact matches on things like names, but "close enough" matches were sometimes sufficient.

Re: You'll regret using natural keys

#358

Post by sgarland »

twobitshifter wrote: post #334
mewpmewp2 wrote: post #297

Earlier quoted context omitted.

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

> incrementing IDs falls over too

This is a myth. Planetscale [0] uses integers. They are assuredly at scale.

As for auto-incrementing, there are plenty of solutions for distributed systems.

[0]: https://github.com/planetscale/discussion/discussions/366

Re: You'll regret using natural keys

#359

Post by tapland »

nostrademons wrote: post #340

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

It's not really fine. I work on a Healthcare system in the nordics.

Who you billed, who visited what doctor, who your primary care provider is, all the people a doctors office has as patients, refers to the SSN.

You don't want to lose that connection or to have to update everything for any change.

You store a unique identifier for the person in the system, and you can then pull the actual personal identification number when needed.

You do not keep individual private lists of people changing genders.

Re: You'll regret using natural keys

#360

Post by kstenerud »

vanderZwan wrote: post #352
kstenerud wrote: post #316

Earlier quoted context omitted.

I developed safe32 for this reason. https://github.com/kstenerud/safe-encoding/blob/master/safe3... Notably, confusable characters are interchangeable when being ingested (although a machine encoder MUST always produce canonical output). https://github.com/kstenerud/safe-encoding/blob/master/safe3... So a user can confuse 1 for l, 0 for o, I for l, u for v, uppercase, lowercase etc, or the agent can say any of those…

Oh that looks well-thought out and is probably the sanest way to solve this particular problem! It's obvious why the safe64/safe80/safe85 cannot do this, but is there a reason why the safe16 version doesn't have the same features?

Oh whoops that's an oversight! I'll fix that up tonight.
Post reply on HN