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

71–80 of 568 posts

Re: You'll regret using natural keys

#71

Post by bruce511 »

Twisell wrote: post #64
selcuka wrote: post #61

Earlier quoted context omitted.

At least the latter provided some examples.

Here's a real world example if you run a script once every 5 minutes that launch sub-task you might be tempted to add a non natural auto-increment number that identify each occurence to create a link between the script and the subtasks. However it will be way more painless to use a timestamp of the script starting point a natural key. This way when shit happen you have a relevant and stable way to identify each occur…

If you want a timestamp, by all means add a timestamp field. And of course add a (non primary) key on the field.

I would add a different surrogate field though for the primary key. Because I've -always- discovered edge cases which break my original model.

In this situation I can think of 2. Firstly if the process triggers twice at the same time you have a problem (which happened to me in a case where a test suite fired up thousands of instances on a machine with many cores). Secondly twice a year with daylight savings (and when the owner of the system decided to change from local time to utc).

Is there an actual natural kh out there that will never fail me? Probably. But I'm tired of looking for it.

Re: You'll regret using natural keys

#72

Post by karmakaze »

A simpler and more compelling reason in my experience is that relating tables is so much easier with synthetic keys. You can decide how large your synthetic keys are based on uniqueness requirements, natural keys have their own size and format that would then be copied to make relations. And if uniqueness depends on several values to be unique then dealing with compound foreign keys isn't fun or efficient. At this point, there would have to be a very compelling reason to want to use natural keys to offset this and I've rarely seen them. A case that may come up is for a miscellaneous table to use the synthetic key from another table with another natural value that uniquely identifies a row. I wouldn't choose this route if you ever think you want to relate anything to rows in this table, they should be leaves related only to the synthetic key being used.

Re: You'll regret using natural keys

#73

Post by huygens6363 »

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 would make sense for instances of something so general as “Row”.

What you were saying amounts to “don’t model the thing holding the model”, but experience shows the thing holding the model is itself an (often unwilling) active part of systems.

Someone here gave the example of wrongly entered PK’s by administrative personnel handing live customers. That’s IMO a good example of why you need an extra layer on top of your Actual Model(c). I can think of more.

Re: You'll regret using natural keys

#74

Post by kijin »

"Natural keys" just means that someone else can change them under your feet.

People change names, their phone numbers, passport numbers. Governments change their numbering schemes all the time. Corporations merge and split. Heck, even governments merge and split, surprisingly often, at the municipal level. All without your knowledge or consent. And you're left wondering why you suddenly have duplicate key errors in your database.

The only key that you can trust is one that you, and only you, control. That's the point of the surrogate key. Whether it's BIGINT or UUID v7 is beside the point.

Re: You'll regret using natural keys

#76

Post by t90fan »

petepete wrote: post #66
mths wrote: post #23

Earlier quoted context omitted.

Last I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.

Mine too, but with a British ISP that hasn't existed for fifteen years.

ntl?

me too!

Re: You'll regret using natural keys

#77

Post by The_Colonel »

hobs wrote: post #49
crabmusket wrote: post #40

Earlier quoted context omitted.

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…

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.

Re: You'll regret using natural keys

#78

Post by _flux »

aaronbrethorst wrote: post #6
ltbarcly3 wrote: post #5

Sometimes you will regret it, other times you won't. Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.

That’s quite the absolute statement to make without even one example.

How about:

- Internet scanner that would use host IP as the primary key

- A table connecting two other tables using their primary keys as the composite primary key (when there can only be zero to one such connections)

Re: You'll regret using natural keys

#79

Post by tedunangst »

Every blog post about database schema should use as its example the most obvious example, a blog. People are pretty familiar with the domain, and it even has many of the same challenges. You probably have a postid field in there somewhere, but lots of blog software also uses url as a natural key. Which is why sometimes the old title appears in the url. Can't change that. Except you can. Some software will do redirects, by creating stub records for old URLs that map to new ones. So that's not an intractable problem. Etc.
Post reply on HN