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

381–390 of 568 posts

Re: You'll regret using natural keys

#381

Post by smaudet »

fhars wrote: post #312
smaudet wrote: post #241

Earlier quoted context omitted.

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

Yeah...what I'd really like to do would be to give a character a "natural" background color, e.g.

Then its simple for support to say "red is one, green is ell". But you can't just add a color to a character, because copy paste/rich formatting don't work everywhere, or even transfer well...

Alternatively, if you use ⓪ and ⒈ it matters less if the user says "at" or "zero", and more that they didn't say "oh" or "one".

Re: You'll regret using natural keys

#382

Post by wild_egg »

phkahler wrote: post #376
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…

>> 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 that time b.) the new gender c.) probably the reason for the CPR number change, since if the policy now is that they can change because of a gender change, there's a decent chance they'll be some other policy in the future that results in…

Why could you not join with the audit table and find historical billing information from the old CPRs?

Re: You'll regret using natural keys

#383

Post by brigandish »

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…

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

If we're talking about relational database engines, that's what they do, relate things. One join statement is much the same as another.

Re: You'll regret using natural keys

#384

Post by giancarlostoro »

kibwen wrote: post #373
giancarlostoro wrote: post #342

Earlier quoted context omitted.

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

If you want random IDs to be human-readable (and human-communicatable), I'd just recommend base 32 or even base16. You don't actually save that many bytes from base58 or base64 when it comes to short IDs. Case in point, the parent poster's base64 ID is 14 characters long. When encoded as base32 that's still only 17 characters (or 19 in base16), and now you have completely gotten rid of all notion of casing, which is…

I think the more I think of it, the more I favor the Discord snowflake ID's because they're just integers, and they can be generated on the fly. I think new messages generated them on the client if I'm not mistaken.

Re: You'll regret using natural keys

#385

Post by yashap »

twobitshifter wrote: post #279
yashap wrote: post #97

Earlier quoted context omitted.

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

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

Sure, if all data is in a single DB. But in the real world you’ve generally got some/all of:

- 1 or more data warehouses

- Other services storing said data (e.g. the user id will live in many databases in a service oriented architecture)

- External API integrators who have their own data stores totally out of your control that also have copies of parts of your data

- Job queues. It’s common to have jobs scheduled for the future on some other system (Redis, etc.) that say “do X for user with id Y in Z days”. If the “id” changes these fail

- Caches. Say I cache data by user email instead of a surrogate key, user changes their email, and another user signs up with the old email. Cache hits for the wrong user!

- etc.

Changing the primary key becomes an absolute nightmare project once data lives in multiple places like this, and in my experience it mostly does.

Having truly stable identity for your entities just solves so many future problems, and it’s SO easy to do. In almost all cases, natural PKs are really all downside, virtually zero upside, except slightly less storage.

Re: You'll regret using natural keys

#386

Post by lolinder »

brigandish wrote: post #379
TeMPOraL wrote: post #277

Earlier quoted context omitted.

Except you're encoding PII in the ID, which makes them plainly visible to people who should not have access to user data, and hard or impossible to change. Sure, I could e.g. change my e-mail and the contact data would be updated, but you still have the old e-mail associated with my account via ID. I'm not sure this would fly under GDPR.

Erm, don't show the ID to people who don't need it. Aside from that, it's not a violation of GDPR to keep personal information (that they consented to you having) in order to process business for that person. Using an email address as a unique identifier is not a violation, using it to spam them would be. If they're willing to give you their current email why not an old one?

> Erm, don't show the ID to people who don't need it.

How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII?

Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID as the key?

Re: You'll regret using natural keys

#387

Post by lolinder »

wild_egg wrote: post #382
phkahler wrote: post #376

Earlier quoted context omitted.

>> 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 that time b.) the new gender c.) probably the reason for the CPR number change, since if the policy now is that they can change because of a gender change, there's a decent chance they'll be some other policy in the future that results in…

Why could you not join with the audit table and find historical billing information from the old CPRs?

You could, but that introduces a lot of complexity that would be saved by just giving the customer a synthetic ID that uniquely and stably identifies them in your system.

The grandparent's proposal basically turns the first-entered CPR into a meaningless ID field that should not generally be used in a piece of business logic (unless for some reason you need to display "first CPR they entered"). Once you've declared that you should look elsewhere if you actually need the CPR, why did we even bother using it as the primary key?

Re: You'll regret using natural keys

#388

Post by datavirtue »

codeulike wrote: post #184
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…

Except the Stripe ones are case sensitive which can be annoying with some databases

You know what's annoying? People storing GUIDs in a case-insensitive database.

Re: You'll regret using natural keys

#389

Post by brigandish »

lolinder wrote: post #386
brigandish wrote: post #379

Earlier quoted context omitted.

Erm, don't show the ID to people who don't need it. Aside from that, it's not a violation of GDPR to keep personal information (that they consented to you having) in order to process business for that person. Using an email address as a unique identifier is not a violation, using it to spam them would be. If they're willing to give you their current email why not an old one?

> Erm, don't show the ID to people who don't need it. How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII? Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID a…

Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others? Then why would you send this?

It's so strange an argument. Right now you have my username but not my email address, yet you can still query the website database and get certain data that you're allowed to see. There are so many ways to query a particular user's data, and they would all depend on what you're trying to do, needing the specific key would mean you should have access to it anyway and it could be given on per case basis anyway.

Re: You'll regret using natural keys

#390

Post by lolinder »

brigandish wrote: post #389
lolinder wrote: post #386

Earlier quoted context omitted.

> Erm, don't show the ID to people who don't need it. How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII? Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID a…

Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others? Then why would you send this? It's so strange an argument. Right now you have my username but not my email address, yet you can still query the website database and get certain data that you're allowed to see. There are so many ways to query a particular user's data, and they would all depend on w…

> Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others?

It's not an int, but yes, we have a unique synthetic identifier that serves as the database PK and as a means of communicating about a customer in insecure channels without exposing PII. "Customer ID ### is having an issue with such-and-such."

To turn your second part back around: why a natural key? What is the function of minting a natural key if humans are meant to use something else?

Post reply on HN