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

371–380 of 568 posts

Re: You'll regret using natural keys

#371

Post by lolinder »

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…

I'm a fan of audit logs, but what you're describing is more event sourcing than audit logs. From what I understand what you're suggesting is that you don't change the key in the original table, you just record the change in a second table. Presumably, then, when you need to read a row you must also look up the list of diffs for that row to make sure that the natural key hasn't changed.

Two things strike me about this proposed model:

First, under your proposal the version of the model that you work with at the application layer must have two copies of the key field—one is the database key for when you need to make changes or look up more data and one is the meaningful business-model field that's actually up to date. That's exactly the same extra mental overhead that natural keys are supposed to have solved, only worse because the fields will have similar names and will contain the same content most of the time, making it easy to accidentally use one where the other was expected.

Second, if you're going to introduce effectively an event-sourced data model then you've introduced a ton of new database records already, so why not just give everything a proper unique key while you're at it? Once you've done that you can modify the original row after all (while retaining the audit logs!) or, if you're serious about event sourcing, cache the latest derived value and look it up by its database key instead of carrying around an out of date natural key that's just waiting to be used incorrectly.

Re: You'll regret using natural keys

#372

Post by tapland »

kalleboo wrote: post #354
tapland wrote: post #344

Earlier quoted context omitted.

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.

And the samordningsnummer isn't only for immigrants - it's also for Swedes born abroad who have never been folkbokförd.

They're still still an immigrant. The numbers are for residents (at some point in time) and citizenship isn't reflected by it.

Re: You'll regret using natural keys

#373

Post by kibwen »

giancarlostoro wrote: post #342
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 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 annoying to communicate verbally.

Re: You'll regret using natural keys

#374

Post by Kon-Peki »

mikedelfino wrote: post #349
Longhanks wrote: post #306

Earlier quoted context omitted.

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

The US government doesn't assume that it can uniquely identify a citizen with 100% certainty. When things get tricky, we rely on the judicial system to weigh the evidence and make a decision. Which could later get changed.

If you want to design your system to assume that you can do it, that's your problem. Literally, it is now your problem, and nobody is going to step in to help.

Re: You'll regret using natural keys

#375

Post by tapland »

tapland wrote: post #359
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…

It's not really fine. I work on a Healthcare system in the nordics. Who you billed, who visited what doctor, who your primary care provider is, all the people a doctors office has as patients, refers to the SSN. You don't want to lose that connection or to have to update everything for any change. You store a unique identifier for the person in the system, and you can then pull the actual personal identification numb…

> You do not keep individual private lists of people changing genders.

On this note: Some organization in Sweden actually does store changes like suggested in the top comment. Both the tax agency and police were interested in knowing who did, and who had access, since it's the cause of recent mass doxxing events.

Re: You'll regret using natural keys

#376

Post by phkahler »

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 a new CPR issuance.

But what if you have a bunch of records in some other table - billing information for example, and it's indexed by the CPR number (a foreign key). When they change the CPR number you can no longer query for their entire billing history based on CPR. None of your proposed extra complexity does anything about this problem. The only good way to solve it is to use a synthetic key with NO meaning. It would still be good to do as you say and track all the CPR number changes for a given person, but they will still need a unique key. So a sort of "identity table" used to figure out what unique key you're dealing with.

Re: You'll regret using natural keys

#377

Post by echelon »

cogman10 wrote: post #298
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…

A problem with this approach is it's not monotonical. Especially if you want to use this thing as an index in a database, you'll run into problems where you try doing middle insertions frequently, which causes fragmentation. The solution to this problem is making the higher order characters time sorted [1]. You don't need to go all out like uuid, you can have a pretty low resolution. It's more important that new inse…

This is a great technical modification that can be made to work with "Stripe"-alike IDs or tokens.

Another hack for advanced active-active situations where you may need to route events before replication completes: encoding the author shard / region in the lower order bytes.

There are lots of interesting primary key hacks for dealing with physical or algorithmic complications.

Re: You'll regret using natural keys

#378

Post by phkahler »

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…

Something like that should have a built in check "digit" if people are going to see it and possibly type it in.

For numeric values, making them all a multiple of 11 is a simple way to catch all single digit errors or single transpositions.

Re: You'll regret using natural keys

#379

Post by brigandish »

TeMPOraL wrote: post #277
brigandish wrote: post #134

Earlier quoted context omitted.

You require three fields (or four): email at registration, a date for that entry (together these create a natural key), and current email (this one not part of the key and editable). We're almost all the way to a Tag URI[0], so you could combine it with the user's name or username or any other identifier that fits the spec[1] (you could even use the website's own name) and you have a (definitely two thirds, probably…

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?

Re: You'll regret using natural keys

#380

Post by fl0ki »

I inherited a project where a dozen different entity kinds were all given UUIDv4s. Users mostly see namespaced strings, but actually searching by those strings is janky and unreliable for other reasons (including the fact that they can change), so whenever I'm asked to debug something I insist on reducing to UUIDs first so at least we're definitely looking at the same entities.

I didn't like the UUIDs at first, but it ended up being an unexpected boon for generic code to use the same key type for different entity kinds. What was less of a boon was that the string identifiers also have to be unique, but can change at any time, and depend on three-level namespacing (yes really) so there's far more that has to be tracked and enforced. The names are very important for UI use cases, but can never be the way that records reference other records, because that would just make them much harder to change with confidence.

The essay seems to assume you'll have either unique natural keys or unique synthetic keys, but having now worked on a project that does both at the same time for many entities, I think it's a third option worthy of its own analysis. My experience was negative but I can't deny that the end result ticks a lot of functional boxes.

Post reply on HN