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

521–530 of 568 posts

Re: You'll regret using natural keys

#521

Post by threecheese »

Kon-Peki wrote: post #30

> how about a personal identification number? In Denmark we have the CPR number, and I understand that the US Social Security Number is vaguely analogous. The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t ne…

If I used this as db key, I think our security/risk team might actually have me assassinated.

Re: You'll regret using natural keys

#522

Post by coooolbear »

vsuperpower2020 wrote: post #420
coooolbear wrote: post #416

Earlier quoted context omitted.

By ‘find an article’ you mean find ~10 real citations including the resolution of an authority a long time ago and to tell the reader it is not clear or definitive? Better to be careful and let any individuals or communities tell you what they want. I have Roma connections in my family and at one point the word we’d use is ‘gypsy’. But, because I’m not Roma myself, if I came across some other group I wouldn’t assume…

I don't care what they want. A lot of people are tired of playing these language games.

Then “a lot of people” (lol) shouldn’t complain when they piss someone off when someone already warned them

Re: You'll regret using natural keys

#523

Post by randomdata »

hathawsh wrote: post #499
randomdata wrote: post #438

Earlier quoted context omitted.

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

Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. Furthermore, any time you reveal the primary key, that key can become the thing that people come to depend upon to find that database row, which leads to the possibility that someday, someone will have an important need for some primary keys to change, even if the prima…

> Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key.

SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.

Re: You'll regret using natural keys

#524

Post by coooolbear »

bigstrat2003 wrote: post #471
vsuperpower2020 wrote: post #420

Earlier quoted context omitted.

I don't care what they want. A lot of people are tired of playing these language games.

Also, "do what people want" is fine for your interactions with an individual. But it's not a viable general rule for language, where we need one single approach. I think saying gypsy unless someone personally tells you they would rather you don't call them a gypsy is perfectly reasonable.

Everybody, in fact, takes innumerable social parameters into consideration when you say anything, especially with strangers.

For the sake of mass communication where you can’t really know your receiver, you have to do your best to just communicate whatever you need to (i.e. ‘a single approach’). Choosing to use a word that is ambiguous as to whether it is a slur is a bit unwise. I think it is probably unwise to do the same in personal interactions.

Re: You'll regret using natural keys

#525

Post by khalidx »

Yeah, this works fine unless you’re using something like DynamoDB or any system which requires you to have knowledge of the key.

For relational setups this is the way to go though. I prefer the combo approach though - autoincrementing numbers plus a UUID in another column.

Re: You'll regret using natural keys

#526

Post by Merad »

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'd recommend using Crockford base 32 [0] to encode the bytes. It makes the text more human friendly by eliminating case sensitivity, removing similar/ambiguous letters, and preventing accidental profanity.

And in most cases I think you're also better off just using a uuid and encoding its bytes as base 32, in which case you're basically doing type id's [1]. If the "slug" portion of the id actually encodes a uuid, then it gives you the option to store it in your database using an appropriate uuid type. This will make your database much happier than using long string PK's.

0: https://www.crockford.com/base32.html

1: https://github.com/jetify-com/typeid

Re: You'll regret using natural keys

#527

Post by Too »

naasking wrote: post #477
notfed wrote: post #431

Earlier quoted context omitted.

- and _ tend to break text selection.

I'm not sure what you mean by "break". If you mean that touching or double-clicking on a block of text only extends up to the nearest symbols, that's true. But if your text selection UX is not terrible then it should be simple to extend that further. That said, iOS and Android text selection have gotten worse recently, IMO.

Usually one is not in control over every place where text can be selected.

As a developer I will be exposed to ids being displayed in code, terminals, browsers of various sorts, database editors, json dumps, text editors, api responses, chat messages, you name it.

Re: You'll regret using natural keys

#528

Post by Too »

cogman10 wrote: post #484
senderista wrote: post #479

Earlier quoted context omitted.

I don't understand why you need to maintain two separate keys: instead of generating a random key, why not just encrypt the auto-increment key using a secret key? This is the approach used by e.g. cloud providers that use auto-increment keys internally but don't want them to be guessable.

I think the biggest problem with this approach is it effectively pins you to a encryption key and algorithm (unless you embed some information in the key that lets you version the key, gotta think of that upfront). Imagine, for example, that you picked DES and "kangaroo" as the secret several years back. You are now pinned to an algorithm and key with known security problems and a weak key.

Especially since you can try the encryption algorithm.

Create 1000 users very quickly in a row and you now have 1000 samples of enc(i) all the way up to enc(i+1000) that can help you break the algorithm.

Not saying it will be easy but it surely lowers the guarantees given by the encryption. Someone better at crypto can probably quantify this risk better than me.

Re: You'll regret using natural keys

#529

Post by aidos »

spoiler wrote: post #123
aidos wrote: post #103

Earlier quoted context omitted.

Yeesh. I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me.

Can you share more about this? Wouldn't you run into the same problems if you used a surrogate pk? Without the nat/external pk and fk, you run the risk of having validity issues. Conversely, if the ID changes, isn't the friction what you want? I feel like optimising for unlikely edge cases instead of integrity because of a single incident is too reactionary. Writing a query or script to update a string, even for mill…

Little bit hazy because it was 15 years ago.

I’d used an external int id that was syncing down into our system. There were then a bunch of other tables referencing it as a foreign key.

Overnight it turned into a sting. Clearly I was going to have some reconciliation work to do in any case and it may be that I maintained the old id as the primary key at that point (can’t recall).

It was just a good lesson in mitigating the blast radius - especially wrt things that are out of your control.

As an analogy, imagine that you’re dealing with dates in the same system (we were), you wouldn’t let their date format bleed into your other tables.

You draw a line at the boundary between the systems. In a way, natural keys break that rule.

Re: You'll regret using natural keys

#530

Post by cimnine »

michaelt wrote: post #495
cimnine wrote: post #367

Earlier quoted context omitted.

Yes, and I like to combine two established concepts instead of rolling my own: URI and UUIDv7. So my IDs become `uri:customer_shortname:product_or_project_name:entity_type:uuid`. An example ID could be `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196`. It's even possible to cascade such IDs, for example: `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196:line_item:018fe882-43…

> It's even possible to cascade such IDs, for example: `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196:line_item:018fe882-43b2-77bb-8050-a1139303bb65`. Let me guess - you're a Java developer, right?

Nope.
Post reply on HN