You'll regret using natural keys • Hacker News Board

Re: You'll regret using natural keys

#431

Post by notfed » Wed, Jun 05, 2024, 4:35 PM UTC

naasking wrote: post #285

vanderZwan wrote: post #157

Earlier quoted context omitted.

Nice! For the record: the valid chars string is 62 characters, so naively using a modulo on a random byte will technically introduce a bias (since dividing 256 values by 62 leaves a remainder). I don't expect it to really matter here, but since you're putting in the effort of using crypto.randomBytes I figured you might appreciate the nitpick ;). Melissa E. O'Neill has a nice article explaining what the problem is, a…

> For the record: the valid chars string is 62 characters, so naively using a modulo on a random byte will technically introduce a bias Indeed, there's no reason you couldn't just add "_" and "-" or "." as well to complete the set. Your identifier will still be URL-safe. I've been using this type of encoding for years [1] for these kinds of ids to use in URLs, and encoding/decoding is super-fast with some bit shifts.…

- and _ tend to break text selection.

Re: You'll regret using natural keys

#432

Post by cogman10 » Wed, Jun 05, 2024, 4:37 PM UTC

groestl wrote: post #412

cogman10 wrote: post #298

Earlier quoted context omitted.

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…

> A problem with this approach is it's not monotonical Whether or not that's bad fully depends on your platform and the number of writes you do. If you're using a massively distributed database like Datastore, Spanner etc, you want random keys as to avoid hot spots for writes. They produce contention.

Well, you'd still likely want psuedo-random keys. You'd rather not have the underlying database doing extra work to shuffle around records as the pages get jumbled.

One solution to that is having more complex keys. For example, in one of our more contentious tables the index includes an account id (32bit int) and then the id of the entity being inserted. This causes inserts for a given account to still be contiguous (resulting in less fragmentation) while not creating a writing hotspot since those writes are distributed across various clients.

Re: You'll regret using natural keys

#433

Post by caeril » Wed, Jun 05, 2024, 4:38 PM UTC

cogman10 wrote: post #407

reissbaker wrote: post #368

Earlier quoted context omitted.

IMO it's nice to have two keys: 1. An auto-incremented 64-bit (unless you have a good reason, in which case 32-bit is fine) primary key, used internally for foreign key relations. This will generally result in less index bloat on associated tables, and fast initial inserts. 2. A public-facing random string ID. Don't use this internally (other than in an index on the table it's defined for), since it's large. But this…

For the number 2, I think one issue is that you are going to be semi-frequently whacking the db to do a mapping of that random string id back to the real id. OK for smaller entities but might be a pain if there's a lot of those ids to wrangle. You can throw a secondary index on it, but that will still have some minor fragmentation issues. One benefit of a random id is if you are working with more complex data models…

That's why the Good Lord invented caching. In most applications, 90% of your workload will be over ids less than a week old, so your hit rate is likely to be pretty high for this sort of mapping.

Re: You'll regret using natural keys

#434

Post by shkkmo » Wed, Jun 05, 2024, 4:44 PM UTC

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?

Frequently yes. It is extremely common to communicate about specific records using the ID for that record. The fact that this sort of behavior is extremely common is pretty clearly indicated by the question itself.

> 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

A responsible organization at scale with limit and log access to customer data. I should be able to determine if two people are talking about the same customer record without needing access to that record's PII.

It is much better to have an artificial key that is linked to this data. There is no upside to the natural key and many, many downsides.

Re: You'll regret using natural keys

#435

Post by lolinder » Wed, Jun 05, 2024, 4:48 PM UTC

brigandish wrote: post #400

lolinder wrote: post #390

Earlier quoted context omitted.

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

> 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? Because non-natural keys are unnecessary in the presence of a natural key, and unnecessary things bring in complexity. > "Customer ID ### is having an issue with such-and-such." Then you need access to the customer's ID, but the devil here is in the detail you didn't add,…

> Use secure channels…

When it comes to PII at my company, secure channels means "encrypted email only". No Slack, no Jira, no chat in video calls.

That's just not feasible for 100% of communications.

Re: You'll regret using natural keys

#436

Post by shkkmo » Wed, Jun 05, 2024, 4:49 PM UTC

brigandish wrote: post #400

lolinder wrote: post #390

Earlier quoted context omitted.

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

> 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? Because non-natural keys are unnecessary in the presence of a natural key, and unnecessary things bring in complexity. > "Customer ID ### is having an issue with such-and-such." Then you need access to the customer's ID, but the devil here is in the detail you didn't add,…

> Because non-natural keys are unnecessary in the presence of a natural key, and unnecessary things bring in complexity.

None of the things you've presented are actually "natural" keys, they are pieces of information that you've made assumptions about to shoehorn them into being usable as a "natural key".

> Use secure channels…

No channel is perfectly secure. As channels become more secure, they become harder to use and add complexity. The more places you store customer data, the more risk you create. The attempt to force semantic data to serve as "natural key" has now added risk and complexity to your entire communication infrastructure.

Re: You'll regret using natural keys

#437

Post by phatskat » Wed, Jun 05, 2024, 4:50 PM UTC

parpfish wrote: post #52

Cyph0n wrote: post #33

Earlier quoted context omitted.

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

That helps part of it, but there are still places for problems to pop up. - you need to do an adhoc query to look something up so you have to type in the key in a where clause - Or you used different sanitation methods in two different databases and you need to join things now - you try to join the table that had the sanitized email key to a different table that just so happens to have email but it wasn’t sanitized b…

And then you get the other stuff like one-off migrations that are done by someone unaware of the business constraints, or a contractor who completely misses the ORM method, etc.

These of course should be caught by checks and balances, and you can’t count on anyone knowing what they need to know ten years after an implementation is done

Re: You'll regret using natural keys

#438

Post by randomdata » Wed, Jun 05, 2024, 4:51 PM UTC

gregw2 wrote: post #264

wavemode wrote: post #246

Earlier quoted context omitted.

Your perspective is very thorough and interesting, but I can simplify the matter a lot - yes, from my personal experience it absolutely does matter for privacy regulation whether you use natural or surrogate keys. I've worked on systems in the biotech space where certain tables and certain columns in our dataset were considered PHI (personal health information), and others weren't. An auto-incrementing user ID does n…

Thanks for the reply. I will concede or defer to you in regards to PHI and HIPAA... it seems the philosophy behind HIPAA/PHI is very different than PII or GDPR. HIPPA is prescriptive. PII/GDPR are principle-based. HIPAA, it seems, has some text that it's not PHI if the risk is "very small" based on the opinion of someone with statistical expertise documents that it could be de-identified OR if the person avoids an ex…

> SSN ... which are definitely not surrogate keys.

Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).

Re: You'll regret using natural keys

#439

Post by cogman10 » Wed, Jun 05, 2024, 4:52 PM UTC

caeril wrote: post #433

cogman10 wrote: post #407

Earlier quoted context omitted.

For the number 2, I think one issue is that you are going to be semi-frequently whacking the db to do a mapping of that random string id back to the real id. OK for smaller entities but might be a pain if there's a lot of those ids to wrangle. You can throw a secondary index on it, but that will still have some minor fragmentation issues. One benefit of a random id is if you are working with more complex data models…

That's why the Good Lord invented caching. In most applications, 90% of your workload will be over ids less than a week old, so your hit rate is likely to be pretty high for this sort of mapping.

First hit can be a beast. It's workload/entity determinant if caching is enough for this.

Not great if you are spending 1 minute on the first lookup just to do the mapping.

Re: You'll regret using natural keys

#440

Post by crote » Wed, Jun 05, 2024, 4:52 PM UTC

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…

> Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned.

The problem is that the new CPR number is now the one you want to use for all display purposes and future interactions with external systems. In other words, you can't use the "original CPR" field for anything except as key. It's no longer a CPR field, because it no longer has any relation to the person's CPR!

And at that point it'd be better to just use a GUID or something as key and avoid any potential confusion between the "real" CPR and "fake" CPR, because when the two are the same 99% of the time it is guaranteed to cause a shitton of bugs.

The only solution is to essentially rewrite all your records with the new CPR as key, and leave a redirect entry at the old CPR. That's pretty much what happens in Sweden when you change your gender: your old identity ceases to be and you're issued a completely new one.