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

341–350 of 568 posts

Re: You'll regret using natural keys

#341

Post by zarzavat »

bjourne wrote: post #275
zarzavat wrote: post #258

Earlier quoted context omitted.

You can’t change primary keys, that’s the point, because you don’t know where they are. For example if an old key is in a URL, and that URL is in a browser bookmark, now you need redirects, so you need to keep all the old keys around forever. Keys should be random or sequential, never contain information. If you want to enforce uniqueness then use a unique index/constraint.

I think you are confused about the terminology because a primary key is a uniqueness constraint. They can be changed in any RDBMS worth its salt. Keys should not be random and your URL example is a case in point. The url /germany/berlin/2023/mcdonalds contains no surrogate key and is immensely more useful than the url /review?uuid=1kjksdhh3244ygdvvgdd2345.

A primary key implies a uniqueness constraint. But you can have a uniqueness constraint without a primary key.

You can change primary keys within a database but you can’t change them outside the database. A database is a map but keys are part of the territory.

The second URL with the UUID is more secure and is preferred in many situations where information leakage is a concern.

The first URL is more descriptive but more prone to breakage. It depends on the situation.

Re: You'll regret using natural keys

#342

Post by giancarlostoro »

strnisa wrote: post #151

I'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…

If I'm going to do that I think I'd use Bitcoins BASE58 which avoids letters that could be confused for each other. The number of times I see an O and 0 and wonder which is which, because the font does not make it clear really annoys me.

Edit:

Other honorable mentions: ObjectID's as used by MongoDB which contain the creation timestamp. Also Discord's snowflakes (inspired by Twitter's iirc), which also contain the creation timestamp.

Re: You'll regret using natural keys

#343

Post by sgarland »

williamdclt wrote: post #173
mootothemax wrote: post #167

You 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

Nope, but it does have a Visibility Map due to its MVCC implementation. Go ahead and do an index-only lookup on a table using a UUIDv4. Then repeat with something k-sortable. See how many buffer hits each one has.

I assure you, those add up. Nothing like explaining to a dev that they have hit the theoretical memory transfer limit of the hardware, and it cannot possibly go any faster unless they refactor their schema (or a faster DDR version comes out).

Re: You'll regret using natural keys

#344

Post by tapland »

kassner wrote: post #245
winternewt wrote: post #155

Earlier quoted context omitted.

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…

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.

Re: You'll regret using natural keys

#345

Post by sgarland »

mewpmewp2 wrote: post #315
sgarland wrote: post #307

Earlier quoted context omitted.

You don’t have to use the PK as the URL slug. Even if you want to route that way, you can have an internal ID and external ID. This is one way to use something random like a UUIDv4 for display without incurring the costs (at least, some of them) of having it as a PK.

And then if you want to list other entities to that user you will have to start mapping the external id and foreign relationships every time to external users? And also if you are doing exception logging, for ids/primary keys there's higher odds of them being logged out, including your own logs and also external platforms. It feels like having primary key set up like this just will complicate everything unnecessarily…

The solution I outlined is the one GitLab and PlanetScale both use internally, so it has been tested at scale and works well, for both Postgres (the former) and MySQL (the latter).

> It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.

That’s a great way to tank performance. You want your PK to be k-sortable.

Re: You'll regret using natural keys

#346

Post by bloopernova »

Kon-Peki wrote: post #57
selcuka wrote: post #54

Earlier quoted context omitted.

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

EDIT - SSA claims that numbers are not recycled. But there are known cases where the same number has been assigned to multiple people. Note that in less than 100 years, more than half of all possible SSNs have already been used…

So we need SSNv6? ;)

Re: You'll regret using natural keys

#347

Post by mannykannot »

8372049 wrote: post #259
gvx wrote: post #250

Earlier quoted context omitted.

Anything that needs to be read over the phone should probably be written out using something like the NATO phonetic alphabet, split into smaller chunks if needed: "The code? It's kilo eight niner; one three mike; delta echo lima."

Having come from a military background where using that is second nature, I'm constantly surprised how rarely I meet civilians who understand it effortlessly. When picking up a package I say "the code is Oscar Foxtrot three-fife" and you see the person processing for a long time to extract the first letter of the word. I've started saying "OF, that's Oscar Foxtrot, 3-5" to help them out. In other words, asking a cust…

My experience in the USA is that if I don't include the phrase "as in" (as in "X as in Xray") most people still will not realize what I am doing (the alternative "for" can be confused with the digit.)

I also ask them to check my readback of key information they have given me and vice-versa; usually that works well.

Re: You'll regret using natural keys

#348

Post by stiiv »

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.

Re: You'll regret using natural keys

#349

Post by mikedelfino »

Longhanks wrote: post #306
mikedelfino wrote: post #268

Earlier quoted context omitted.

How to uniquely identify an American citizen?

How about not doing that?

Then I guess it could lead to duplicate records. Now, whether this is a problem or not depends on the business and how much one cares about data integrity. I work with higher education, so uniquely identifying students and keeping all their records organized is somewhat important. Granted, I don't work in the US, and here we have a unique, national number. So this is covered, except for foreign students. I was curious how this problem is solved in other countries.

Re: You'll regret using natural keys

#350

Post by datadrivenangel »

wruza wrote: post #228
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 short, you advise us to foresee the future, explore unknown unknowns and expect high-precision true answers from the outside. Good advice, not for this universe. You can only get false negatives in this one. A synthetic key means “we think exists”. There exists a contract, a medical record, a person, in a real world, in our opinion. We record these existence identities into our model by using an always-unique key.…

This is why it's hard to be a DBA... Everyone thinks you're a Cassandra user the way other developers ignore your prophecies.
Post reply on HN