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

231–240 of 568 posts

Re: You'll regret using natural keys

#231

Post by biztos »

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

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

Re: You'll regret using natural keys

#232

Post by ourmandave »

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

If I’m going natural PK, I make email the primary key.

Welcome to the Mr. Cooper mortgage provider website.

Your logon is your email and you can't change it.

If you used your cable provider email you're stuck with them for the life of your 30 year mortgage.

Re: You'll regret using natural keys

#233

Post by biztos »

Knufferlbert wrote: post #215
garblegarble wrote: post #188

Earlier quoted context omitted.

This approach will randomly generate profanity. If the ID is visible to users it can cause some to get upset (and best-case simply looks unprofessional). On a purely technical level if visible in URLs it can cause links to be blocked/altered by e-mail filters / filtering proxies. It's generally a good idea to drop vowels for this reason.

We once had a rather angry Irish customer calling our support complaining that we called him a pikey (slur for gypsy). After some back and forth it turns out we just gave him an apikey. We never had a similar issue with our random numbers/letters/reset passwords or anything like that which don't have any kind of "dont return profanity" protections. Though I agree, someone getting a randomly generated customer portal…

Totally off topic, but "gypsy" is itself a slur for Romani people in much of the world.

https://en.wikipedia.org/wiki/Romani_people

Re: You'll regret using natural keys

#234

Post by corobo »

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

Why is such a thing called a slug?

Ooh I had this wonder a while back and jotted it down just in case anyone else ever wondered about it:

Comes from the ye olde paper-based blogs they call newspapers. When an article is being put together it’s given a short name, sort of like a project name. This name would remain the same throughout the article’s life - from reporter through to editor - it left its trail through the process. Like a slug.

Re: You'll regret using natural keys

#235

Post by smaudet »

zer00eyz wrote: post #153
globular-toast wrote: post #124

Earlier quoted context omitted.

How do you deal with the Ship of Theseus/Trigger's broom? There's literally nothing that defines said object apart from its history .

Your database starts to look like git... Think about a jet engine. Let's say you figure out that a part is defective and will cause a failure. You want to identify every plane that has one of those parts in it. What if you find that some of them had a bad oil pump that shortened the life of some bearings. You want to know every engine that had one of those pumps, so you can replace other parts. I dont know if they do…

> Your database starts to look like git...

No, your table archives start to look like git. Which is not a bad thing, version control on data in a database is very difficult, if not impractical to realize, I would take any history that I can get...

"Synthetic" keys are just that, keys for tables that only have meaning within the context of that data. You can of course relate your records to as many other tables as you wish, but you identify records via those keys, nothing more.

Re: You'll regret using natural keys

#237

Post by brabel »

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?

I think long term, that is a bad idea.

In Brazil in the 80's and early 90's, the currency changed name several times. It was called "cruzado", then "cruzeiro", then "cruzado novo" (yep, "new" , very creative) and then I think it went back to just "cruzado" :D before finally becoming Real (which I believe was the name of the currency also during Monarchy, 100 years earlier).

Re: You'll regret using natural keys

#238

Post by jagged-chisel »

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

One can convert those to the correct characters. Since “oh” and “el” are excluded from the alphabetic range, they become “zero” and “one” - deciding whether that is done in software for the help desk, or in the brain of the help desk staff is left as an exercise to management.

Re: You'll regret using natural keys

#239

Post by DrScientist »

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 natural key forces you to think about what makes the row unique. What identifies it.

When designing a table - you should always be clear about what the natural key is and make sure it has uniqueness constraint. Mindlessly having a surrogate key without thinking about what the natural key is, is an anti-pattern. So totally agree here.

That doesn't stop you also having a surrogate key though.

Another aspect of natural versus surrogate keys is joins as the key often ends up in both tables.

Using natural keys can mean in some circumstances you can avoid a join to get the information you want - as it's replicated between tables.

There is also the question of whether you surface the surrogate key in the application layer or not - some of the problems of surrogate keys can be avoided by keeping them contained within the app server logic and not part of the UI.

So via the UI - you'll search by car registration number, not surrogate key, but in terms of your database schema you don't join the tables on registration number - you use a surrogate key to make registration numbers easier to update.

Re: You'll regret using natural keys

#240

Post by wslwsl »

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

I'm not convinced that all Stripe IDs are wholly random strings. I just decoded the base62 part of four genuine "acct_" objects, which at 16 characters are just shy of representing a 12 byte value (log2 62^16 =~ 95.3), and they all have a leading byte of "00000011" and two of them even have a leading 32 bits that is very suspiciously close to an epoch timestamp of a couple of years ago. There is a similarly suspiciou…

In the comments it's mentioned, that the IDs contain a shard key for faster lookups.

https://dev.to/stripe/designing-apis-for-humans-object-ids-3...

Post reply on HN