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

411–420 of 568 posts

Re: You'll regret using natural keys

#411

Post by jordanthoms »

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 dependent on the database you are using - if it's a key-sharded distributed database, you want to have insertions evenly spread across the key space in order to avoid having all the inserts go into a single shard (which could overload it)

Re: You'll regret using natural keys

#412

Post by groestl »

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…

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

Re: You'll regret using natural keys

#413

Post by jaggederest »

jimbobthrowawy wrote: post #180
jaggederest wrote: post #65

Earlier quoted context omitted.

As a great example: My steam account name is the email I was using in 2003. I have largely not used it since then. The email on the account has been updated, but the account name? Stuck.

I was about to ask how you got past steamguard without email access. Makes sense if you were able to navigate off it.

Email was changed about 5 years before steamguard release :)

Re: You'll regret using natural keys

#414

Post by kalleboo »

tapland wrote: post #372
kalleboo wrote: post #354

Earlier quoted context omitted.

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.

Interesting, I've never heard of anyone referring to a citizen as an immigrant, but looking up the legal definition, they certainly are. TIL.

Re: You'll regret using natural keys

#415

Post by Pengtuzi »

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…

> URLs are another good natural key: they are defined to be unique

No, see more info here: http://localhost:8080/

Re: You'll regret using natural keys

#416

Post by coooolbear »

isaacremuant wrote: post #311
biztos wrote: post #233

Earlier quoted context omitted.

Totally off topic, but "gypsy" is itself a slur for Romani people in much of the world. https://en.wikipedia.org/wiki/Romani_people

I'd say those articles show Wikipedia's political bias and a tendency to overly politically correct instead of portraying reality. Many in real life do refer to themselves as gitano or Gypsy and would ask for others to refer to them as such. Of course it's very easy to find an article saying otherwise and then using that as the end of discussion for Wikipedia editors.

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’m just allowed to say it to them.

Re: You'll regret using natural keys

#417

Post by shkkmo »

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.

This is a horrible idea. You now have two different pieces of information that are identical in form and indistinguishable in the majority of cases: "first CPR number" and "current CPR number. Every place you enter, or use a CPR number, you now must track which piece of information you have. If you make a mistake doing this, it will be hard to catch. Now you've decided that first piece of information is a natural key and will be every single place the record is used, even if that spot doesn't do anything specific to the CPR number. Every single time a CPR number is ingested from an exterior source, you need to do a lookup to make sure it is the original CPR number and then track it. Ever place you don't do this lookup is a place where an error can creep in if something changes in your pipeline our source.

Even in an context where external sources are using something like a CPR number as an key, I would still use a different key internally since I see only downsides to using a "natural key".

> URLs are another good natural key: they are defined to be unique (otherwise your webserver won't work), they make for very easy lookups when you're fetching from a web request, and if they change, they break the web

URLs are also horrible natural keys. They are not defined to be unique and provide no guarantee that the content has not changed or even that the same content is sent to different users.

If the location for content changes, you may or may not get a redirect. If you do get a redirect, you'd have to now go update every single place that uses the key to the new value. It is much better to map URLs to an artificial key and update that mapping in a single place.

Re: You'll regret using natural keys

#418

Post by nikita2206 »

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…

But there’s no natural key for a person, not even the SSN. This article also starts off by showing an example where no natural key exists (Restaurants) and acknowledges it.

Re: You'll regret using natural keys

#419

Post by nostrademons »

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…

You need this complexity anyway - if you had surrogate keys, the primary key would be an opaque identifier, and to do anything involving the CPR, you'd need to join against your CPR table (which, again, needs to be 1:many because CPRs themselves are not a 1:1 relationship).

The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you. And then it has other added benefits in that you have a record of CPR changes, you can understand how common a case this is, the CPR change table itself has semantic meaning and you can query a wide variety of properties without joining your primary user table, etc.

Re: You'll regret using natural keys

#420

Post by vsuperpower2020 »

coooolbear wrote: post #416
isaacremuant wrote: post #311

Earlier quoted context omitted.

I'd say those articles show Wikipedia's political bias and a tendency to overly politically correct instead of portraying reality. Many in real life do refer to themselves as gitano or Gypsy and would ask for others to refer to them as such. Of course it's very easy to find an article saying otherwise and then using that as the end of discussion for Wikipedia editors.

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.
Post reply on HN