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

251–260 of 568 posts

Re: You'll regret using natural keys

#251

Post by konschubert »

bjourne wrote: post #249

Hard disagree. You create tables for them to be queried by applications. For the Restaurants example the application clearly wasn't designed to handle Restaurant entries whose only field differs is rank. Consequently, you shouldn't allow data the application can't handle. Whenever the time comes and you think "ah, wouldn't it be great if multiple people could review the same restaurant in the same year?" you change t…

You can use a UUID as a primary key and still enforce uniqueness on a combined index of city and name.

Re: You'll regret using natural keys

#252

Post by kodisha »

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

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

Or, just use cuid2 [1] + prefix.

[1] https://github.com/paralleldrive/cuid2

Re: You'll regret using natural keys

#253

Post by 8372049 »

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

i, L and o are left out of the alphabet in the snippet, so there's not really any ambiguity.

Re: You'll regret using natural keys

#254

Post by kassner »

meekaaku wrote: post #236

I am firmly in the control your primary key, preferably uuid. Quick question for database gurus here. Is it ok to have a currency table using the currency code as primary key?

Likely no, there are currencies without an ISO 4217 code, i.e.: https://en.wikipedia.org/wiki/Faroese_kr%C3%B3na

Re: You'll regret using natural keys

#255

Post by fiatjaf »

Such a long article, no convincing examples. Why did he waste so long on the stupid restaurant case that clearly makes no sense? Natural keys are good as long as you think one minute before deciding to use them.

Re: You'll regret using natural keys

#256

Post by nottorp »

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

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…

Funny you should say that...

I've been very slooowly degoogling myself, and that includes changing all logins that have a gmail address to a different non google email.

I'd say only like 1/3 of the sites I made logins for have the option of changing the email.

Re: You'll regret using natural keys

#257

Post by kstenerud »

wvh wrote: post #195
kstenerud wrote: post #172

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once a…

I agree with your general point, but it might still be cheaper to redesign your system if a rare breaking change happens to usually reliably stable external registration systems, rather than to pay the cost of a thousand paper cuts by indirection, computational cost or human confusion. One edge case and good indicator is if your system is testable by itself without third party cooperation and the ability to, for inst…

Every system that uses SSN as the primary key is fully testable by itself without third party cooperation. And yet every one of these systems has required workarounds because what the designers thought to be invariants, weren't.

And this is ALWAYS the case with identifiers that you don't control. You don't make policy decisions about them, but SOMEBODY does. And that somebody isn't aware of - and wouldn't even care - about the invariants that you ASSUMED they followed (and maybe they really did follow them, but they sure don't anymore! Oops...)

Fixing broken invariants after the fact is always a nightmare, because it only comes up once you get stuck - either you can't enter something into the database that you absolutely MUST by the weekend, or you can't change something in the database that you absolutely MUST by the weekend. So you do some last minute hacks to get things kind of working, and then it works for awhile until the next problem (usually involving your hack).

It's hardly any extra work or complexity to just use an ID generator for the primary key. You'll still have the same indices, the same foreign key linkages etc. You have no reason not to do this.

And yet somehow people always seem to fall for the "Oh cool! This existing ID does everything we want! Let's just use that instead of adding one more field to the table! I'm so clever!"

Re: You'll regret using natural keys

#258

Post by zarzavat »

bjourne wrote: post #249

Hard disagree. You create tables for them to be queried by applications. For the Restaurants example the application clearly wasn't designed to handle Restaurant entries whose only field differs is rank. Consequently, you shouldn't allow data the application can't handle. Whenever the time comes and you think "ah, wouldn't it be great if multiple people could review the same restaurant in the same year?" you change t…

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.

Re: You'll regret using natural keys

#259

Post by 8372049 »

gvx wrote: post #250
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."

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 customer/consumer to be able to recite something in phonetics is not realistic in most cases.

Fortunately the code already takes this into consideration and removes ambiguous characters.

Re: You'll regret using natural keys

#260

Post by mrweasel »

devjab wrote: post #86
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 terms of the Danish CPR that is mentioned here, the way we actually solved the challenge in the national architecture strategy was to define your social security number(s) as what we call an address on your person. I’m not sure why it was called an address, but it’s basically a UUID and the information. Maybe it’s because it was first used to store addresses? Anyway. Unless your system has not yet implemented the…

It's been years since I worked on systems with CPR numbers, but I seem to recall that there is also a policy in place, stating that you are not allowed to use the CPR number as a "primary key". Many companies did anyway, because they never actually bothered to read the guidelines and regulations.

All the systems I've seen always had the CPR as a lookup for a UUID, which as you say is the the "address" of the actual person object.

Post reply on HN