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

311–320 of 568 posts

Re: You'll regret using natural keys

#311

Post by isaacremuant »

biztos wrote: post #233
Knufferlbert wrote: post #215

Earlier quoted context omitted.

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

I'd say those articles show Wikipedia's political bias and a tendency to overly politically correct instead of portraying reality.

Many in real life do refer to themselves as gitano or Gypsy and would ask for others to refer to them as such.

Of course it's very easy to find an article saying otherwise and then using that as the end of discussion for Wikipedia editors.

Re: You'll regret using natural keys

#312

Post by fhars »

smaudet wrote: post #241
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 wonder if Unicode could be used to alter the characters such that these mistakes would be less possible, e.g. using ⓪.

That is "AT", isn't it?

(No, it isn't, if you look closely enough.)

Re: You'll regret using natural keys

#313

Post by noisy_boy »

shireboy wrote: post #292

In the example from the article I think the table should be named RestaurantRank(year,rank,restaurantid), with a natural key year,rank. It would have a fk to Restaurant(Id,name,etc). Choose a natural key if there is some natural unique field or combination. In this case unless restaurants can tie in their ranking year,rank can be unique

You'll have to modify the natural key if later it is decided to add rankCategory ("best overall", "best ambience", "best value" etc) via rankCategoryId foreign key to the RestaurantRank table because a given restaurant can have the same rank in the same year under multiple categories. A synthetic key avoids you having to mess with the key to handle such cases.

Re: You'll regret using natural keys

#314

Post by sgarland »

zelphirkalt wrote: post #132
maweki wrote: post #125

Earlier quoted context omitted.

> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your…

Why does anything need to be a primary key anywhere in order to enforce some constraint? At least from ORMs I know I can set for example any group of attributes unique. Other constraints can be implemented in some general method that is called when persisting in the actual database. Even if no ORM, you can write a wrapper around your persisting procedure.

It doesn’t, you’re right. However, indexes aren’t free, so if your data is such that a natural PK (composite or otherwise) makes sense, you’ll save RAM. Also, for clustering RDBMS like MySQL, data is stored around the PK, so you can get some locality boosts depending on query patterns.

Re: You'll regret using natural keys

#315

Post by mewpmewp2 »

sgarland wrote: post #307
mewpmewp2 wrote: post #294

Earlier quoted context omitted.

But now then if you want to expose a detail page for that user the id for identifying that page has to include all this potentially personal information about them? e.g. instead of mysocialmedia.com/users/2374927 you would be showing mysocialmedia.com/users/email@example.com-2024-06-05-mysocialmedia.com Then exposing a lot of information that you may have not wanted to expose.

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 for the future including many edge cases that you don't foresee.

Just have the main ID not have any meaning.

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

Re: You'll regret using natural keys

#316

Post by kstenerud »

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 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 over the phone, and it won't matter.

Re: You'll regret using natural keys

#317

Post by ceejayoz »

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 like to nix vowels and things that look like them, i.e. 0, to avoid random b00bs sort of tokens.

Re: You'll regret using natural keys

#318

Post by ceejayoz »

fnordsensei wrote: post #281
kodisha wrote: post #252

Earlier quoted context omitted.

Or, just use cuid2 [1] + prefix. [1] https://github.com/paralleldrive/cuid2

One thing I’ve been looking for in an ID generator is a way to supply a blocklist. There are a number of character combinations I’d like to avoid in IDs, because they might be offensive or get stuck in filters when copy-pasted (e.g. in a URI). This can be solved in user space by regenerating if the character sequences are detected, but this a) skews the distribution, and b) potentially takes time, especially when the…

Just take out the vowels and numbers that can look like vowels. Nixing 0 means no b00bs IDs, and avoids 0/O; I usually take out 1/I as well.

Re: You'll regret using natural keys

#319

Post by twodave »

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.

Re: You'll regret using natural keys

#320

Post by d--b »

> what if we had a restaurant named China Wok in Springfield, USA? Hardly unique. Add the state, you say? Probably still not unique.

Ok sure, but then you have 2 restaurants which are indistinguishable from one another in your database. It doesn't matter that the thing has a unique id next to it. You can't know which is which. That's not useful.

Post reply on HN