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

101–110 of 568 posts

Re: You'll regret using natural keys

#101

Post by aswerty »

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…

Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe?

For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts.

That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world.

And to pile on. The top comment is bad advice! Surrogate keys provide sanity - god save you if you have to work in a database solely using natural keys.

Re: You'll regret using natural keys

#102

Post by DeathArrow »

Synthetic key is the easiest choice. Because the primary key in one table is going to be foreign key in another table. If I have an employee table I can use their email address as the key but then I will have to use the email in any other table that references employee table, such as salary table, days off table and so on.

Most of the times using an auto incrementing integer or UUID is just fine.

In NOSQL there's usually a synthetic ID used to uniquely identify a document, I've never seen people using natural keys or compound keys.

Re: You'll regret using natural keys

#103

Post by aidos »

CrimsonRain wrote: post #84
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…

Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a s…

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.

Re: You'll regret using natural keys

#104

Post by webprofusion »

As a junior dev decades ago I was once asked to change a perfectly good database (with integer IDs) to use natural keys, so to look up (or SQL join to) a particular office for instance your query would filter on Country, City and Street with the expectation that you get one result.

This was the dumbest technical decision I have ever been asked to be a part of.

Re: You'll regret using natural keys

#106

Post by mypalmike »

paulddraper wrote: post #91
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…

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.

Re: You'll regret using natural keys

#107

Post by groestl »

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…

Adding to the list of comments damning this post to ensure none of my future colleagues follow this advice.

> You think your surrogate key will save you? It will not.

It definitely will, say my 20+ years of experience.

Re: You'll regret using natural keys

#108

Post by vintermann »

aswerty wrote: post #101
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…

Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad adv…

Yes. Ultimately, "are these the same" and "are these different" are philosophical questions. Or to be more precise, teleological questions.

Because those questions have no meaning except with an "for our purposes here" added. And it's up to us to decide what we care about, if anything.

If we care about what other people want with the data though, or suspect that our own wants might not be set in stone, then we should also care to model identity independently in the system (that is, use surrogate keys).

Re: You'll regret using natural keys

#109

Post by moi2388 »

I’ve just always considered natural keys public keys, and added a surrogate key as private key.

From the outside you won’t see it, but internally it saves a lot of headaches.

Space, speed, migrations, I have just never seen an actual downside to using a surrogate.

And if you are absolutely sure, add some unique constraints. Easier to change when you inevitably have to.

Mainly; I’ve learned that my initial assumptions are never correct.

Re: You'll regret using natural keys

#110

Post by crabmusket »

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.

I love it when my competitors use sequential integer IDs.
Post reply on HN