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

291–300 of 568 posts

Re: You'll regret using natural keys

#291

Post by abujazar »

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…

Natural keys can change, and synthetic keys never have to. That alone is reason enough to use synthetic keys.

Performance is another major argument for synthetic keys, as they are can be made sequential, which is rarely the case for natural keys.

Re: You'll regret using natural keys

#292

Post by shireboy »

In the example from the article I think the table should be named RestaurantRank(year,rank,restaurantid), with a natural key year,rank. It would have a fk to Restaurant(Id,name,etc). Choose a natural key if there is some natural unique field or combination. In this case unless restaurants can tie in their ranking year,rank can be unique

Re: You'll regret using natural keys

#293

Post by rsynnott »

begueradj wrote: post #156

One of the most interesting part to me is the fact he is a university professor in a good country but rides a used/second hand car... in an era where status and success are highlighted by displaying what one buys and owns on social media.

> in an era where status and success are highlighted by displaying what one buys and owns on social media.

I mean, not for everyone? These things, pretty much regardless of era, matter a lot to some people and not at all to others; for still others it may be a negative signal of sorts.

Anecdotally, I know a lot of very well-off people; well under half would have fancy cars.

Re: You'll regret using natural keys

#294

Post by mewpmewp2 »

brigandish wrote: post #134
yashap wrote: post #97

Earlier quoted context omitted.

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…

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…

But now then if you want to expose a detail page for that user the id for identifying that page has to include all this potentially personal information about them?

e.g. instead of mysocialmedia.com/users/2374927

you would be showing

mysocialmedia.com/users/email@example.com-2024-06-05-mysocialmedia.com

Then exposing a lot of information that you may have not wanted to expose.

Re: You'll regret using natural keys

#295

Post by mgkimsal »

yellowapple wrote: post #191
throwaway8486k wrote: post #112

Earlier quoted context omitted.

Security by obscurity makes it more difficult for bad actors as an additional layer that they need to break. I don't see a reason why that's a bad thing and doesn't take a lot of effort to implement in this case. I have been in a startup where competitors used our sequential keys to scrape a list of customers. Lesson learned the hard way with actual business consequences! Sequential keys also leak information (German…

> I have been in a startup where competitors used our sequential keys to scrape a list of customers. If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.

The 'customers' could be free tier users - a social media type system where everyone has a public profile - intended for the public - would still be scrapable by /profile/1, profile/2, etc. Doesn't necessarily require 'authentication' for the exposing of sequential integers to have a bad outcome.

Re: You'll regret using natural keys

#296

Post by fuzzfactor »

begueradj wrote: post #156

One of the most interesting part to me is the fact he is a university professor in a good country but rides a used/second hand car... in an era where status and success are highlighted by displaying what one buys and owns on social media.

This is an educated person who shows higher-class tendencies by riding a bicycle, compared to those who seek mere automotive eminence.

Even if the bicycle is older than the car, perhaps even more so.

Re: You'll regret using natural keys

#297

Post by mewpmewp2 »

twobitshifter wrote: post #279
yashap wrote: post #97

Earlier quoted context omitted.

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

> My impression from the article is that this is a single SQL database being discussed.

Even if it's initially single, it's bad to assume that it will be so forever and that you are not going to use third party providers in the future.

How well does ON UPDATE CASCADE work if there's millions of existing relations to that entity?

Re: You'll regret using natural keys

#298

Post by cogman10 »

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 insertions tend to be on the same page. If you have a low frequency insertions then minute resolution is probably good enough. (Minutes since 2000 is an easy calculation).

To implement that here, I'd suggest looking at how base64 or 85 encoders work and use that instead of repeated mods. You can then dedicate the upper bits to a time component and the lower bits can remain random. [2]

[1] https://vladmihalcea.com/uuid-database-primary-key/

[2] https://github.com/mklemm/base-n-codec-java/blob/master/src/...

Re: You'll regret using natural keys

#299

Post by sgarland »

twobitshifter wrote: post #279
yashap wrote: post #97

Earlier quoted context omitted.

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

I was going to say; this is a perfect use case for a cascading FK.

Re: You'll regret using natural keys

#300

Post by pradn »

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…

Pernicious assumptions!

Google Cloud projects have three attributes: user-friendly names, system numbers, and system names. System names are alphanumeric. They can be chosen by the user, derived from the friendly name if there's no collision.

But! There's some system names from the olden days that are actually all numbers - so not actually alpha-and-numeric. Thankfully we don't run into those often.

Post reply on HN