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

141–150 of 568 posts

Re: You'll regret using natural keys

#141

Post by maweki »

spoiler wrote: post #136
maweki wrote: post #125

Earlier quoted context omitted.

> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your…

Another example is where you use a service that provides you with a stable id. It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation. Data integrity/quality matters. Adding friction to prevent accidents also matters. I don't want something accidentally and trivially updating a field that's used to reference thing externally.…

> a service that provides you with a stable id

I think there's the important point. Is your key actually natural or is it someone else's surrogate key anyway? Going back to the vehicle identification number: that's already a surrogate key. You just did not assign it yourself.

Re: You'll regret using natural keys

#142

Post by mrkeen »

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.

> surrogate keys can be fixed size integers

This launches into the other debate about PKs: using UUIDs rather than sequential keys.

Re: You'll regret using natural keys

#144

Post by otikik »

One of my first jobs involved an account management software which created an ID for each user based on first name and surname. So John Smith would get SMIJOH. It was the user natural key and it could not be changed once created.

One woman got married and changed her name; se became really upset when she found out that we couldn’t change her login.

Re: You'll regret using natural keys

#145

Post by canary-hill »

What has worked best for us is to have a sequential integer as the primary key as well as UUIDv4 as a surrogate key for every row. The applications would expose the objects only using uuid and never the primary key. The primary key always remain internal at the database level and never gets referenced or used at the application layer. In this way the security and privacy of objects are maintained and having sequential integral primary key for the object ensures the database design remains robust. Essentially, separating out the key for the database and the application. Is there any potential issue with this approach?

Re: You'll regret using natural keys

#146

Post by hyperman1 »

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…

In my experience, this won't end well. Some examples:

Belgium has the RNR/INSZ identifying each person. But it can change for a lot of reasons: It gets reused if someone dies. It encodes birth date, sex, asylum state, so if something changes (which happens about every day), you need to adapt your unique key.

Belgium also has a number identifying medical organizations. Until they ran out of numbers. Then someone decided to change the checksum algorithm, so the same number with a different checksum meant a different organizations. And of course they encode things in the number and reuse them, so the number isn't stable.

An internal COBOL system had a number as unique key, and this being COBOL, they also ran out of numbers. This being COBOL, it was more easy to put characters in the fixed width record than expand it. And this being French COBOL, that character can have accents. So everyone using the 'number' as unique key now had to change their datatype to text and add unicode normalization. Not fun.

In my experience: don't use anything as an ID that you didn't generate yourself. Make it an integer or UUID. Do not put any meaning in the ID. Then add a table (internal ID, registering entity, start date, end date or null, their key as text). You 'll still sometimes have duplicates and external ID updates as the real world is messy, but at least you have a chance to fix them. The overhead of that 1 lookup is negligable on any scale.

Re: You'll regret using natural keys

#147

Post by throwaway8486k »

The_Colonel wrote: post #117
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'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 ID…

> I'm not saying it's a bad practice, the opposite actually

I'm sorry that I didn't make that more clear. I saw that you mentioned it as a best practice and are aware of the advantages. It's just that there are so many others that don't have the balanced view as you seem to have.

I have been involved in many discussions at my work place where "security by obscurity" is used as a way to shut down discussions. They changed their minds about sequential keys after the incident I mentioned, but it still has the power to "win" other discussions. Sure, we need to have rate limiting on ip-addresses, auth and other mechanisms, but they are not perfect and bugs happen all the time. An "unguessable" id is an additional security layer

> It's not an easy thing to add into an existing application, though

I agree, but there are ways to reduce the attack surface. You could add an extra "public id" field that can be used for lookup in addition to the existing id. In this way you can have a gradual migration where you go through each endpoint and migrate them individually without changing the foreign keys and other relations in the database (they would still use the sequential key). Maybe you end up not having time to migrate them all, but at least you can reduce the attack surface on the most sensitive endpoints.

If you have low volume endpoints you could perhaps even simply add a mapping layer where you do a simple db lookup to replace the public key with the internal without changing the existing queries. You could even cache this easily to reduce the db load. (both ids are permanently fixed and can be cached forever).

Re: You'll regret using natural keys

#148

Post by sigtstp »

If anyone works in bioinformatics, please, please, for the love of god, generate your own unique IDs. Database identifiers are not generally unique (the same id might get reused for e.g. protein variants). Even sequences are problematic: I've found the same seq with different ids (can't remember the db now), and they can change (sequencing or human error might've occured and they get updated).

Re: You'll regret using natural keys

#149

Post by mkleczek »

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…

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

But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?

Re: You'll regret using natural keys

#150

Post by teaearlgraycold »

What I’m doing right now is putting synthetic (auto incremented) ids on every table, but also adding lots of compound unique indices. We have a lot of long background processes that might need to be restarted in the middle. The natural key definition allows for easy idempotency in subsequent runs (the jobs only upsert with. collisions handled by a simple timestamp update).

Most entities are tied to a version record (more or less a God object). But some span across versions. I’m able to have the entities that aren’t tied to a version float around because they reference the natural key of the versioned entities, minus the version’s synthetic key.

Post reply on HN