7 ms·

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.

by yashap 2y ago

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. Or building on that example, maybe at first we always require an email from our users. But later we also allow phone auth, and you just need an email OR a phone number. And later we add user name auth, SSO, etc. Again, all good with surrogate primary keys, a nightmare with natural primary keys. There are countless examples like this. You brought up cars, same thing with licence plates, for example. Or even Social Security Numbers/Social Insurance Numbers - in Canada SINs are generally permanent, but temporary residents can have their SIN change if they later become permanent residents, but they’re still the same person. You want your entities to have stable identity, even if things you at one time thought gave them identity change. Surrogate primary keys do that, natural primary keys do not. Don’t use natural primary keys, use surrogate primary keys with unique constraints/indexes. I challenge you to come up with a single plausible example where you’re screwing yourself by choosing surrogate PK + unique constraints/indexes. Meanwhile there are endless examples where you’re screwing yourself by choosing natural PK.

maweki 2y ago
> 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 customers and there is this special offer just for Virgos and Pisces, you could enforce that the birth date matches this special offer. Some modern systems also technically allow FK on alternate keys, so you could still do it that way, but database theory often ignores that. But second, while I agree that surrogate keys are often a good idea, I find your argument, that you must design for every conceivable change, not convincing.
- cjfd 2y ago
  "for every conceivable change". That is not what he is arguing at all. He is showing that there are very many highly plausible changes that are problematic with natural keys. And he totally correct about that. Frankly, the fact that a post arguing for natural keys makes it the top of an HN comment thread is extremely weird. The original article is correct that natural keys are bad.
- zelphirkalt 2y ago
  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.
  - sgarland 2y ago
    It doesn’t, you’re right. However, indexes aren’t free, so if your data is such that a natural PK (composite or otherwise) makes sense, you’ll save RAM. Also, for clustering RDBMS like MySQL, data is stored around the PK, so you can get some locality boosts depending on query patterns.
  - a57721 2y ago
    > At least from ORMs I know I can set for example any group of attributes unique Just in case, ORMs send DDL statements with uniqueness constraints to the DBMS, they don't do any magic here.
- lstamour 2y ago
  > 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 <referenced table and columns> specifies a <reference column list>, then the set of <column name>s contained in that <reference column list> shall be equal to the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table. So it mentions unique constraints first. Then afterward it says: > If the <referenced table and columns> does not specify a <reference column list>, 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 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.
knallfrosch 2y ago
> 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.)
- epcoa 2y ago
  > At this point, you realize you can now introduce a synthetic key and you're fine. Except for having to update all foreign references. Some of those may be external further complicating issues. Emails are often among the worst keys because they are not terribly stable and they are reusable often enough to burn you.
  - jrs235 2y ago
    Also are emails case sensitive or not? In some systems (that you don't control mind you) they are and others they are not...
    - sgarland 2y ago
      Per RFC5321, the local part (before @) _may_ be case-sensitive, but in practice, it almost never is, and relying on case sensitivity is a recipe for disaster. The domain must always be case-insensitive.
      - jrs235 2y ago
        My point being is if you're using email as a key you "have to" treat it as case sensitive even though for most it's not. And yes, I agree it will be a recipe for disaster.
brigandish 2y ago
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/ https://taguri.org/ [1] http://www.faqs.org/rfcs/rfc4151.html http://www.faqs.org/rfcs/rfc4151.html
- TeMPOraL 2y ago
  Except you're encoding PII in the ID, which makes them plainly visible to people who should not have access to user data, and hard or impossible to change. Sure, I could e.g. change my e-mail and the contact data would be updated, but you still have the old e-mail associated with my account via ID. I'm not sure this would fly under GDPR.
  - brigandish 2y ago
    Erm, don't show the ID to people who don't need it. Aside from that, it's not a violation of GDPR to keep personal information (that they consented to you having) in order to process business for that person. Using an email address as a unique identifier is not a violation, using it to spam them would be. If they're willing to give you their current email why not an old one?
    - lolinder 2y ago
      > Erm, don't show the ID to people who don't need it. How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII? Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID as the key?
arnorhs 2y ago
Another benefit of having stable identies / surrogate primary keys is that any relations (FKs) will be much simpler. Sure, like the post poster you replied to is pointing out, you _can_ use natural keys, and then also relying on dates or other parts of the data - but creating a relation for that can end up being extremely cumbersome. - Indexes generally become larger - relationships become harder to define and maintain - Harder for other developers to get up to speed on a project
ourmandave 2y ago
If I’m going natural PK, I make email the primary key. Welcome to the Mr. Cooper mortgage provider website. Your logon is your email and you can't change it. If you used your cable provider email you're stuck with them for the life of your 30 year mortgage.
- bradleyankrom 2y ago
  Oh, also, we've been breached and your information is available for purchase on the dark web. Fun!
- sevenseacat 2y ago
  Then your cable provider shuts off its email service and you lose that email address. Ooooops
nottorp 2y ago
Funny you should say that... I've been very slooowly degoogling myself, and that includes changing all logins that have a gmail address to a different non google email. I'd say only like 1/3 of the sites I made logins for have the option of changing the email.
twobitshifter 2y ago
ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)
- mewpmewp2 2y ago
  > My impression from the article is that this is a single SQL database being discussed. Even if it's initially single, it's bad to assume that it will be so forever and that you are not going to use third party providers in the future. How well does ON UPDATE CASCADE work if there's millions of existing relations to that entity?
  - twobitshifter 2y ago
    YANGNI for 99% of projects and databases. When you get to global sharded nosql etc. you need to use UUIDs for anything and incrementing IDs falls over too.
    - mewpmewp2 2y ago
      I'm using UUIds by default for everything. Main point being that I don't have to worry about future restrictions. And incrementing IDs are also problematic yes, since they hide business information data within them. And I do think that I need it for much more than 1% of projects and DBs.
      - sgarland 2y ago
        You’ll have to worry about performance tanking instead. If you’re using UUIDv7 then less so, but it’s still (at best) 16 bytes, which is double that of even a BIGINT. Anyone who says UUIDs aren’t a problem hasn’t dealt with them at scale (or doesn’t know what they’re looking at, and just upsizes the hardware).
        chuckadams 2y ago
        Most databases with a UUID type store them as 128-bit integers, typically the same as a BIGINT. It's not like 378562875682765 is the bit representation of a bigint either. And if you're not using uuidv7 or some other kind of cluster-friendly id, you'd best be using a hash index, and if you're doing neither, you probably don't care about their size or performance anyway. You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.
bioneuralnet 2y ago
My first job in the late 2000's was at a small university with a home-grown ERP system originally written in the 80s (Informix-4GL). Student records, employee records, financials, asset tracking - everything. It used natural compound keys. Even worse than the verbose, repetitive, and error-prone conditions/joins was the few times when something big in the schema changed, requiring a new column be added to the compound key. We'd have to trawl through the codebase and add the new column to every query condition/join that used the compound key. It sucked.

by yashap 2y ago

maweki 2y ago

> 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 customers and there is this special offer just for Virgos and Pisces, you could enforce that the birth date matches this special offer. Some modern systems also technically allow FK on alternate keys, so you could still do it that way, but database theory often ignores that. But second, while I agree that surrogate keys are often a good idea, I find your argument, that you must design for every conceivable change, not convincing.

cjfd 2y ago
"for every conceivable change". That is not what he is arguing at all. He is showing that there are very many highly plausible changes that are problematic with natural keys. And he totally correct about that. Frankly, the fact that a post arguing for natural keys makes it the top of an HN comment thread is extremely weird. The original article is correct that natural keys are bad.
zelphirkalt 2y ago
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.
- sgarland 2y ago
  It doesn’t, you’re right. However, indexes aren’t free, so if your data is such that a natural PK (composite or otherwise) makes sense, you’ll save RAM. Also, for clustering RDBMS like MySQL, data is stored around the PK, so you can get some locality boosts depending on query patterns.
- a57721 2y ago
  > At least from ORMs I know I can set for example any group of attributes unique Just in case, ORMs send DDL statements with uniqueness constraints to the DBMS, they don't do any magic here.
lstamour 2y ago
> 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 <referenced table and columns> specifies a <reference column list>, then the set of <column name>s contained in that <reference column list> shall be equal to the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table. So it mentions unique constraints first. Then afterward it says: > If the <referenced table and columns> does not specify a <reference column list>, 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 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.

knallfrosch 2y ago

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

epcoa 2y ago
> At this point, you realize you can now introduce a synthetic key and you're fine. Except for having to update all foreign references. Some of those may be external further complicating issues. Emails are often among the worst keys because they are not terribly stable and they are reusable often enough to burn you.
- jrs235 2y ago
  Also are emails case sensitive or not? In some systems (that you don't control mind you) they are and others they are not...
  - sgarland 2y ago
    Per RFC5321, the local part (before @) _may_ be case-sensitive, but in practice, it almost never is, and relying on case sensitivity is a recipe for disaster. The domain must always be case-insensitive.
    - jrs235 2y ago
      My point being is if you're using email as a key you "have to" treat it as case sensitive even though for most it's not. And yes, I agree it will be a recipe for disaster.

brigandish 2y ago

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/ https://taguri.org/ [1] http://www.faqs.org/rfcs/rfc4151.html http://www.faqs.org/rfcs/rfc4151.html

TeMPOraL 2y ago
Except you're encoding PII in the ID, which makes them plainly visible to people who should not have access to user data, and hard or impossible to change. Sure, I could e.g. change my e-mail and the contact data would be updated, but you still have the old e-mail associated with my account via ID. I'm not sure this would fly under GDPR.
- brigandish 2y ago
  Erm, don't show the ID to people who don't need it. Aside from that, it's not a violation of GDPR to keep personal information (that they consented to you having) in order to process business for that person. Using an email address as a unique identifier is not a violation, using it to spam them would be. If they're willing to give you their current email why not an old one?
  - lolinder 2y ago
    > Erm, don't show the ID to people who don't need it. How do you communicate with other people in your company about a customer without sending around PII if the customer's ID is PII? Maybe we could create a field that uniquely identifies the customer that isn't PII. Then that could be used to uniquely identify a customer in places where we don't want to expose their PII. But then... why not just use this unique ID as the key?

arnorhs 2y ago

Another benefit of having stable identies / surrogate primary keys is that any relations (FKs) will be much simpler. Sure, like the post poster you replied to is pointing out, you _can_ use natural keys, and then also relying on dates or other parts of the data - but creating a relation for that can end up being extremely cumbersome. - Indexes generally become larger - relationships become harder to define and maintain - Harder for other developers to get up to speed on a project

ourmandave 2y ago

If I’m going natural PK, I make email the primary key. Welcome to the Mr. Cooper mortgage provider website. Your logon is your email and you can't change it. If you used your cable provider email you're stuck with them for the life of your 30 year mortgage.

bradleyankrom 2y ago
Oh, also, we've been breached and your information is available for purchase on the dark web. Fun!
sevenseacat 2y ago
Then your cable provider shuts off its email service and you lose that email address. Ooooops

nottorp 2y ago

Funny you should say that... I've been very slooowly degoogling myself, and that includes changing all logins that have a gmail address to a different non google email. I'd say only like 1/3 of the sites I made logins for have the option of changing the email.

twobitshifter 2y ago

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

mewpmewp2 2y ago
> My impression from the article is that this is a single SQL database being discussed. Even if it's initially single, it's bad to assume that it will be so forever and that you are not going to use third party providers in the future. How well does ON UPDATE CASCADE work if there's millions of existing relations to that entity?
- twobitshifter 2y ago
  YANGNI for 99% of projects and databases. When you get to global sharded nosql etc. you need to use UUIDs for anything and incrementing IDs falls over too.
  - mewpmewp2 2y ago
    I'm using UUIds by default for everything. Main point being that I don't have to worry about future restrictions. And incrementing IDs are also problematic yes, since they hide business information data within them. And I do think that I need it for much more than 1% of projects and DBs.
    - sgarland 2y ago
      You’ll have to worry about performance tanking instead. If you’re using UUIDv7 then less so, but it’s still (at best) 16 bytes, which is double that of even a BIGINT. Anyone who says UUIDs aren’t a problem hasn’t dealt with them at scale (or doesn’t know what they’re looking at, and just upsizes the hardware).
      - chuckadams 2y ago
        Most databases with a UUID type store them as 128-bit integers, typically the same as a BIGINT. It's not like 378562875682765 is the bit representation of a bigint either. And if you're not using uuidv7 or some other kind of cluster-friendly id, you'd best be using a hash index, and if you're doing neither, you probably don't care about their size or performance anyway. You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.

bioneuralnet 2y ago

My first job in the late 2000's was at a small university with a home-grown ERP system originally written in the 80s (Informix-4GL). Student records, employee records, financials, asset tracking - everything. It used natural compound keys. Even worse than the verbose, repetitive, and error-prone conditions/joins was the few times when something big in the schema changed, requiring a new column be added to the compound key. We'd have to trawl through the codebase and add the new column to every query condition/join that used the compound key. It sucked.