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

111–120 of 568 posts

Re: You'll regret using natural keys

#111

Post by crabmusket »

huygens6363 wrote: post #73
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…

There is a model of a thing and there is a row that stores a representation of that model of a thing. They both are things. Ignoring the last one might be tempting, but it’s not practical. Interestingly your own way of thought is applied, but now a level deeper again. How do you model a row? What makes it unique? A surrogate ID is the only sensible unique identifier for such a thing as there is no “natural key” that…

> They both are things.

Corollary: your app is part of the real world.

Re: You'll regret using natural keys

#112

Post by throwaway8486k »

The_Colonel wrote: post #77
hobs wrote: post #49

Earlier quoted context omitted.

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain…

I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.

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 tank problem)

Your competitors can estimate number of customers, your growth rate and other stats that you often don't want them to know.

https://en.m.wikipedia.org/wiki/German_tank_problem

Re: You'll regret using natural keys

#113

Post by samtheprogram »

I technically use a natural key, if you could call it that, when using Supabase. Having a `public.users` table with a primary key that’s the same as the key for the internal user auth table (from Supabase).

Arguably not a natural key, or at least a contrived example, but: any downsides?

Re: You'll regret using natural keys

#114

Post by crabmusket »

mypalmike wrote: post #106
paulddraper wrote: post #91

Earlier quoted context omitted.

Terrible advice. Surrogate keys are keys are a layer of indirection. They don't fix all problems, but they fix some problems. Not least of which is performance. Often natural keys are character strings, whereas surrogate keys can be fixed size integers, saving index sizes on your FKs.

Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.

Aren't you literally describing an index?

Re: You'll regret using natural keys

#115

Post by hackit2 »

crabmusket wrote: post #40
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…

The surrogate key uniquely identifies a row in your database , which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra diffic…

A entity can exist over more than one row in your database, but it is useful to uniquely identify each row as the lowest common denominator.

Re: You'll regret using natural keys

#116

Post by mobiuscog »

It's also important to put as much thought into any synthetic key.

It's easy to just use an auto-generated sequence... but then you start having to export/import or otherwise merge data, and other manipulations that often use the primary key and find there are collisions everywhere. There can also be problems when needing to support multiple databases, or update versions.

UUIDs (or equivalent synthetic keys that are independent of the database itself) are often the best answer for this reason.

Having been bitten by sequences so many times in the past, I find them to often be more trouble than natural keys in the first place - just a lazy approach.

Re: You'll regret using natural keys

#117

Post by The_Colonel »

throwaway8486k wrote: post #112
The_Colonel wrote: post #77

Earlier quoted context omitted.

I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.

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'm not saying it's a bad practice, the opposite actually.

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

True, if you start your application from scratch. Like if I started designing a new app today, I'd just choose the UUIDv7 for the primary key.

It's not an easy thing to add into an existing application, though. I see applications leaking their internal IDs all the time, but usually it's not worth the effort to fix that, because it's a comparatively minor problem.

Re: You'll regret using natural keys

#118

Post by jonathan-re »

mobiuscog wrote: post #116

It's also important to put as much thought into any synthetic key. It's easy to just use an auto-generated sequence... but then you start having to export/import or otherwise merge data, and other manipulations that often use the primary key and find there are collisions everywhere. There can also be problems when needing to support multiple databases, or update versions. UUIDs (or equivalent synthetic keys that are…

Well, UUIDs bring their own challenges. Dropping that one here:

Be Careful with UUID or GUID as Primary Keys https://news.ycombinator.com/item?id=14523523

Re: You'll regret using natural keys

#119

Post by [deleted] »

The_Colonel wrote: post #77
hobs wrote: post #49

Earlier quoted context omitted.

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain…

I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.

[deleted]

Re: You'll regret using natural keys

#120

Post by saghm »

ray_v wrote: post #9

Feels like this could have used a few more solid examples up-front. I think another good example would be PlayStation Network using the natural key of "gamer tags" as the primary key to identify players would be a good example. Since this effectively locks players into having to keep a gamertag in order to uniquely identify them in the service -- instead of having a synthetic key that carries no meaning or data other…

On the other hand, Discord used to allow arbitrary usernames and would add a suffix for when you needed to disambiguate (e.g. if you used the username JohnDoe, your "id" would be something like JohnDoe#12345) but over the past year or so forced everyone to pick a fully unique username. In this case, the decision seemed to potentially be financially motivated, given that people with subscriptions were given priority for claiming usernames, and historically there really hasn't been much reason to pay for it in the first place.
Post reply on HN