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

131–140 of 568 posts

Re: You'll regret using natural keys

#131

Post by inopinatus »

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.

UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit.

Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling their own crypto, and it also makes identifiers much longer.

Re: You'll regret using natural keys

#132

Post by zelphirkalt »

maweki wrote: post #125
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.…

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

Why does anything need to be a primary key anywhere in order to enforce some constraint? At least from ORMs I know I can set for example any group of attributes unique. Other constraints can be implemented in some general method that is called when persisting in the actual database. Even if no ORM, you can write a wrapper around your persisting procedure.

Re: You'll regret using natural keys

#133

Post by knallfrosch »

yashap wrote: post #97
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…

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

> then we add the ability for users to update their email.

At this point, you should verify the new email. At least until it is verified, you must track the old email. At this point, you realize you can now introduce a synthetic key and you're fine.

Let's say you have a duplicate customer entry and the customer demands their accounts be merged. Now you can't identify the user by their key alone, since by definition, they can't be the same (yet.)

Re: You'll regret using natural keys

#134

Post by brigandish »

yashap wrote: post #97
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…

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 100%) natural key.

It's stable over time and unique, easy to mint, and has a standard behind it. The user also gets to change their contact details without any problem related to the key.

[0] https://taguri.org/

[1] http://www.faqs.org/rfcs/rfc4151.html

Re: You'll regret using natural keys

#135

Post by lstamour »

maweki wrote: post #125
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.…

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

> Some modern systems also technically allow FK on alternate keys

As far as I can tell, all modern systems allow it, as it is part of the SQL standard that foreign keys can be either primary keys or unique indexes. Here's a brief quotation from a copy of ISO/IEC 9075-2:1999 (not the latest version) that I randomly found online:

> If the specifies a , then the set of s contained in that shall be equal to the set of s contained in the of a unique constraint of the referenced table.

So it mentions unique constraints first. Then afterward it says:

> If the does not specify a , then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY.

If I'm reading this right, it means that in the base case, where you specify the column to reference, it can be any unique constraint, where a primary key is just another possible unique constraint (as all primary keys are by definition unique). And only if you don't specify the fields to reference does it then fall back to the primary key instead of a named unique constraint.

I'm not disagreeing with you entirely - it's true that often there's an assumption in database theory that primary keys are natural and foreign keys are primary keys. But this isn't a hard requirement in practice or in theory, and it partly depends on the foreign key's purpose, why you need it in the first place. This StackOverflow answer also explains it well: https://softwareengineering.stackexchange.com/a/254566

I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.

Re: You'll regret using natural keys

#136

Post by spoiler »

maweki wrote: post #125
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.…

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

Something about the nat key is about to change? Fine, we can write migrations. Even if it affects millions of rows, it's not a big deal.

I understand people have been burnt by bad design decisions involving nat keys, but they're not some devil's key everyone here dogmatically makes them out to be. You can mess up using anything.

Re: You'll regret using natural keys

#137

Post by mixmastamyk »

spoiler wrote: post #123
aidos wrote: post #103

Earlier quoted context omitted.

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.

Can you share more about this? Wouldn't you run into the same problems if you used a surrogate pk? Without the nat/external pk and fk, you run the risk of having validity issues. Conversely, if the ID changes, isn't the friction what you want? I feel like optimising for unlikely edge cases instead of integrity because of a single incident is too reactionary. Writing a query or script to update a string, even for mill…

Instead of migrating one column, now you have that and every table with a foreign key to the original. Requiring transactions and possibly locking etc.

Re: You'll regret using natural keys

#138

Post by akvadrako »

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…

UUIDs are only good when you don't care about ergonomics or performance.

Snowflake IDs are much more reasonable. 41 bits timestamp (ms) + 10 bit machine id + 12 bit serial.

But if you care about ergonomics and privacy the most then short and random IDs are really the best.

Re: You'll regret using natural keys

#139

Post by The_Colonel »

inopinatus wrote: post #131
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.

UUIDv7 may not be generated sequentially, but it is still temporally ordered and discloses a timestamp, which may be an undesirable leakage for some applications/environments. When obfuscation matters that much, use a UUIDv4 and eat the index performance hit. Some might suggest, "encrypt your object identifiers on the way in/out", but there's a ton of pitfalls involved since for most applications they are now rolling…

Yes, you can go to great depths, but they each have trade-offs - in performance, increased complexity etc. and it's necessary to make a judgment for each particular app instead of applying the most overengineered solution everywhere.

Re: You'll regret using natural keys

#140

Post by fcatalan »

the_gipsy wrote: post #87

In Spain the ID numbers are assigned at birth, carry no information, and cannot be changed. Each police comissary that registers birth gets a unique set of IDs to assign (per year or whatever). However. Mistakes still happen. A colleague had the same ID as someone else. He said he tried to change it, but it was impossible because it was such an impossible concept to any public servant involved. In the end he gave up…

Not really assigned at birth, but at the time you first request the card. It's a very very very crappy database ID number:

-There's a significant amount of duplicates, enough that if your database is big enough you will find one sooner or later.

-Format is variable: There are older 7 digit numbers, modern 8 digit ones, some people pad the 7-digit with a 0, some don't, some consider the CRC letter part of the key, some don't, some just append the letter, some hyphen it. If you deal with manual data entry anywhere, you will suffer.

-Foreign people exist: You start using their passport number, the format of your key is now completely arbitrary, can't validate it. If you were using 8 digit NIFs as keys now some countries use 8 digit passports, increasing your chances of duplicates.

-Foreign people stay: They get a resident card and they use that. Format is almost like NIF but not really so you need to account for that. Someone you registered initially with a passport now has the card and registers for something else with it. Have fun cleaning their duplicated identity from the whole system.

-Foreign people become Spanish: Now they get a NIF. If you have dealt with them for a long enough time, have fun again fixing their records for the second time.

Post reply on HN