You'll regret using natural keys • Hacker News Board

Re: You'll regret using natural keys

#221

Post by ttyprintk » Wed, Jun 05, 2024, 10:13 AM UTC

mythas wrote: post #38

jen20 wrote: post #37

Earlier quoted context omitted.

This sounds like it should probably be a workflow instead. Modelling the intent here is actually important.

Care to expand on this thought? Curious what you have in mind!

(not parent)

Think about these different intentions, each deserving an audit trail with different attached metadata:

- brand new MRN for a newborn. The system should be able to provide info on the mother, at all points of care for the newborn.

- unknown person arrives in the emergency department unconscious. You’ll see imaging named John Doe in this case. The system should be able to retrieve info if and when identity is established.

- the machine-readable bracelet given at admission needs to be replaced. This is really two different cases: you intend to create a duplicate bracelet, or to correct an error in which all current info in the system needs to abruptly update.

Re: You'll regret using natural keys

#222

Post by praptak » Wed, Jun 05, 2024, 10:14 AM UTC

Modeling identity doesn't end with synthetic keys though. "Generate own keys" only solves the problem of identity ownership. You now own the identity but it doesn't mean your identities are as they should be.

Say a customer used a different government-issued ID to re-register with your bank. A year down the line you notice that you have two identities for the same person. It might be a meh for an online game but if you are a bank this can make you run afoul of regulations. Can you handle the merge of all the relevant data? And merging is usually the easier of the two glitches - can you handle a split?

The point is that identity just like security requires thought from the start of the design.

For a domain where identity is really hairy (although admittedly with less consequences for screwing up) see https://news.ycombinator.com/item?id=4493959 "The music classifying nightmare". Also https://en.wikipedia.org/wiki/Identity_(philosophy)#Metaphys... for some philosophical perspective.

Re: You'll regret using natural keys

#223

Post by dividuum » Wed, Jun 05, 2024, 10:15 AM UTC

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…

I took a look at a bunch of stripe customer ids I have stored and at least mine look very random on first glance. I assume their blog post uses demo keys or something similar.

Re: You'll regret using natural keys

#224

Post by nailer » Wed, Jun 05, 2024, 10:15 AM UTC

kstenerud wrote: post #172

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once a…

I was this recently. I moved to US in November and it was February before I had an SSN. A bunch of companies had to put in fake SSNs into their system, which they have a standard for.

Re: You'll regret using natural keys

#225

Post by Macha » Wed, Jun 05, 2024, 10:22 AM UTC

akira2501 wrote: post #204

jimbobthrowawy wrote: post #197

Earlier quoted context omitted.

Licence plate numbers are an interesting one, since what those mean varies from country to country. Here (Ireland), they are assigned to the car itself via VIN, are never meant to change once assigned, and are backdated based on information about the vehicle itself (e.g. year of first registration even if first registered in another country, following an older format if applicable), but in other countries they can be…

You don't have vanity plates in Ireland?

There aren't any, the closest is that some authorities reserve low sequence number plates for e.g. the local mayor

Re: You'll regret using natural keys

#226

Post by [deleted] » Wed, Jun 05, 2024, 10:23 AM UTC

kstenerud wrote: post #172

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once a…

[deleted]

Re: You'll regret using natural keys

#227

Post by cheema33 » Wed, Jun 05, 2024, 10:24 AM UTC

ThePowerOfFuet wrote: post #214

akira2501 wrote: post #204

Earlier quoted context omitted.

You don't have vanity plates in Ireland?

Many, many countries do not.

Vanity plates are essentially free money for the govt. I am surprised every govt. body doesn't go for it. If you really don't like them, make them super expensive.

Re: You'll regret using natural keys

#228

Post by wruza » Wed, Jun 05, 2024, 10:25 AM UTC

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…

In short, you advise us to foresee the future, explore unknown unknowns and expect high-precision true answers from the outside. Good advice, not for this universe. You can only get false negatives in this one.

A synthetic key means “we think exists”. There exists a contract, a medical record, a person, in a real world, in our opinion. We record these existence identities into our model by using an always-unique key. Then there’s a date, an appointment #, a name, etc. You can refer to an entity by its identity, or search by its attributes. If you use searches in place of identity references, you get non-singletons eventually and your singleton-based logic breaks.

Re: You'll regret using natural keys

#229

Post by big_whack » Wed, Jun 05, 2024, 10:27 AM UTC

mkleczek wrote: post #152

The question is: if you have two restaurants having different scores but distinguishable only by their surrogate keys, how do you know which one to go to? In other words - using surrogate key is an attempt (and the wrong one!) to fix the problem of missing important information in the database.

If you use a surrogate key, you still need a unique constraint in the table (probably the same columns you would otherwise call your natural PK). If your unique constraint isn't sufficient to capture the difference you mention, you need to add more columns.

However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.

Re: You'll regret using natural keys

#230

Post by fregonics » Wed, Jun 05, 2024, 10:29 AM UTC

garblegarble wrote: post #188

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…

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.

True! For instance the example itself. "cus" is profanity in Portuguese. If you were to localize the application, this would be a factor.