5 ms·

My opinion. Always if in any way possible pick a semantic key. There is usually something defining the thing you are working on. If there isnt work on your norm

My opinion. Always if in any way possible pick a semantic key. There is usually something defining the thing you are working on. If there isnt work on your normalisation. Main benefits to this: Avoids accidental duplication (happens so much). Avoids additional round trips to fetch the id to make a mutation. Of course if you work on something where you don’t know what it is yet (actually humans are a good example for that) uuid or int might make sense but I hear so many times picking non semantic as a default.

silvestrov 4y ago
Semantics tend to change over time, so you will now have to change the meaning of your key. Did you know that sometimes the same social security number is assigned to multiple persons? In this case a person can change social security number. Good luck updating all of your database foreign keys in this case.
- williamdclt 4y ago
  Updating foreign keys in a database is at least doable; updating external systems that have a reference to this entity is impossible
williamdclt 4y ago
I'd heavily push for the exact opposite. Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain. I find that there actually rarely is something defining the thing you're working on. The concept of "immutable identity" is rarely a useful thing in digitalized systems: - being able to create a new digital entity for the same real-life entity is almost always useful and expected ("the setup of this user is all messed up, just disable it and create a new one") - attributes that you thought were immutable actually are not ("surely the 'originally scheduled time' of an event is an immutable property" - except when you have a bug and events are scheduled at the wrong time and you need to fix data) - the concept of "immutable identity" is often pretty subjective in the real world. We generally agree that a person has an immutable identity, sure, but is a 9am appointment that's moved to a week later the same appointment, or a new one? Depends on who you ask, depends on what purposes you need this concept of "identity" for.
- magicalhippo 4y ago
  For one of our customers we integrate heavily with their ERP system. They had nice sub-10 million integers as order numbers, so we used that as primary key for the order table (and as fk for 10+ child tables). Last year they changed ERP system, and now order numbers are much longer and can contain dashes. Not the worst change, as converting integer to varchar is lossless, but we had to go over all the views and our code to make sure it could handle it.
- hot_gril 4y ago
  > Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain. Yep, happens to the best of us. Never mess around with this, just use a bigserial.
  - hot_gril 4y ago
    (Or other things like UUIDs, which I would not use, are still way better than semantic keys.)
the_duke 4y ago
Almost every time I picked a natural key I have regretted it in the long run. Requirements change, or index sizes get bloated and hamper performance, or you need a nice, short ID for URLs , or foreign keys get more complicated with compound primary keys, or ... Natural keys are nice in theory, but not so much in practice. Especially if we are talking about active databases where large migrations are a burden.
thewataccount 4y ago
I'm strongly against these, I've always regretted it. SKUS, emails, etc all seemed liked good keys. They're always unique right? Until one day when they decided to rename some skus, they suddenly want family accounts, you realize you really do want the ability to have duplicates so you can keep historical copies without ripping up your entire database. Semantics change. A UUID/whatever does not. I've learned you should never ever use a natural key. PKs are extremely difficult or near impossible to replace depending on your application, and if that really means using serial id's, or UUIDs, or extra lookups - it's worth doing that instead of using natural keys.
tragomaskhalos 4y ago
For relational purists, e.g. Joe Celko, this approach is pretty much mandatory. However as others have stated in reply it's very difficult to find a genuinely immutable identifying attribute in many domains, including human beings.
hcarvalhoalves 4y ago
To add to the other comments mentioning why this is difficult in practice: It's the "Ship of Theseus" paradox [1]. Choosing a semantic key means mixing identity and attribute, while a synthetic key solves by assuming "constitution is not identity". Since a digital system is a model of the world, a synthetic key allows the system to address objects in this internal model without assuming a particular interpretation of identity in the real world. E.g., it's often the case that you do need to have two "customer" entries in your system that represent the same physical "person" in the world, and this is ok because the concept of "customer" is useful and sufficient for your model, and the physical person isn't. More often than not, people get caught on this trap in relational databases and object oriented modelling. This can be seen in books and lectures that use Customer-Order-Product relations to teach databases, or Car-Engine to teach OO. [1] https://en.wikipedia.org/wiki/Ship_of_Theseus https://en.wikipedia.org/wiki/Ship_of_Theseus
- cccybernetic 4y ago
  The existence of a synthetic key contradicts the idea that we're modeling the "world". Of course, this is a debate that’s been around forever, and I understand the advantages of synthetic keys, but I've found that the intuitive elegance of a natural key will often flow into the business logic, untangling nests of code dedicated to id look-ups, id-matching, filtering, mapping, and the general slicing and dicing and shaping of data. Queries that once referenced obscure ids now point directly to fields which intuitively make sense: yes, if I want to update the user_apps table, I know or can reasonably infer pk(user_id, app_id), and I know those values and I have them right here in my pocket, don't need to look them up, and they mean something very tangible and real and I don’t have to say “…WHERE id = ‘…’” Of course, nothing is ever perfect and natural keys have their issues, especially when migrating data, but there’s something about them that’s always “clicked” with how I reason about software systems.
  - thewataccount 4y ago
    I originally thought the same - I manage the backend/database for a small team that works on our warehouse/website integration with a legacy ERP system. My experiences with semantic keys has been awful. I've been told "Oh this [property] will either never need to change, and never have duplicates" of properties you'd think really should never change, several times. Somehow a different department decided to change the skus. I've seen emails need to either have duplicates or be changed. I've seen a few instances of order numbers from the ERP system having duplicates under certain circumstances. A few cases like GTINs where every item should already have a unique one assigned - until we have an item that is missing one and will never be assigned one.... The big one is needing to archive things or keep histories of objects - if you use a synthetic key it's super easy to just add a "active" flag and you have to change very little code. I totally get wanting to avoid extra queries and extra steps, but if the need to change a pk _ever_ occurs it's terrible to near impossible (in the case of external dependencies). Many frameworks like django make it fairly easy to naturally add that extra step without needing to make spaghetti to do your lookups
jsmith45 4y ago
Semantic key keys have downsides, and it is also important to note that there is not always a single unique semantic key. Relation theory has the concept of superkeys, which are a set of columns that uniquely determine a row. But these are not useful, since for example, the set all all columns should uniquely determine a row. What is useful is "candidate keys", which are minimal superkeys, with any columsn not necessary to be unique removed. There are two ways to determine candidate keys. One is empirically by analyzing the data. If you do it that way, then the "candidate" naming is appropriate, since it is possible that some sets of columns are unique simply by chance, not by fundamental nature, and unique by chance is not what we want. Alternatively you can use domain knowledge and logic to determine candidate keys. Candidate keys determined by logic are true keys, since no duplicates should ever occur unless the requirements or fundamental nature of the data changes. This means that ideally, all such keys should have a unique constraint placed on them (although the implicit unique constraint from marking as a primary key will works for one of these keys). Adding unique constraints for all logically determined candidate keys is the ideal way to avoid accidental duplication. Within the database and within the application, you ideally want to only use small keys that are unlikely to change, and are unlikely to ever become non-unique. Keys that change tend to cause headaches with updates if referenced elsewhere, and you can have undesirable race condition issues with application logic on changing keys. Similarly, for keys likely to become non-unique from changing requirements, using them within the database means a much bigger refactor later if they become no longer unique. But if you never use those value to reference within the database, then simply dropping the unique constraint is easy. Impact on application code may vary, from potentially no change needed at all, to much more significant changes, depending on the data in question and how the application uses it. Large keys that don't change, and are extremely unlikely to ever become non-unique are conceptually fine, but have the practical problem of being large, and thus undesirable to reference from all over the database from a file size perspective. This is especially true of multi-column keys which also tend to be inconvenient from a query writing perspective. Another important issue is that many identifiers that are supposed to be universally unique, like UPCs, ISBNs etc, are not actually always unique. These things do end up getting occasionally reused, usually accidentally. If you are using that everywhere as your primary key, and eventually come across such a scenario, it is a real nightmare to refactor everything to use a different key in order to be able to handle this. While if you are using some surrogate key almost everywhere, it becomes a lot more feasible to handle this with things like having "lookup by UPC" screens show a list of options when you stumble upon one that happens to have a duplicate.
cyclotron3k 4y ago
I have to disagree with you there, for all the reasons mentioned above, but also it makes generating queries and tooling so much harder. If you see a user_id column, you know it's going to link to user.id. if you see email_id, you know it's going to link to email.id, etc, etc. There's a lot of value in having a predictable schema.

silvestrov 4y ago

Semantics tend to change over time, so you will now have to change the meaning of your key. Did you know that sometimes the same social security number is assigned to multiple persons? In this case a person can change social security number. Good luck updating all of your database foreign keys in this case.

williamdclt 4y ago
Updating foreign keys in a database is at least doable; updating external systems that have a reference to this entity is impossible

williamdclt 4y ago

I'd heavily push for the exact opposite. Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain. I find that there actually rarely is something defining the thing you're working on. The concept of "immutable identity" is rarely a useful thing in digitalized systems: - being able to create a new digital entity for the same real-life entity is almost always useful and expected ("the setup of this user is all messed up, just disable it and create a new one") - attributes that you thought were immutable actually are not ("surely the 'originally scheduled time' of an event is an immutable property" - except when you have a bug and events are scheduled at the wrong time and you need to fix data) - the concept of "immutable identity" is often pretty subjective in the real world. We generally agree that a person has an immutable identity, sure, but is a 9am appointment that's moved to a week later the same appointment, or a new one? Depends on who you ask, depends on what purposes you need this concept of "identity" for.

magicalhippo 4y ago
For one of our customers we integrate heavily with their ERP system. They had nice sub-10 million integers as order numbers, so we used that as primary key for the order table (and as fk for 10+ child tables). Last year they changed ERP system, and now order numbers are much longer and can contain dashes. Not the worst change, as converting integer to varchar is lossless, but we had to go over all the views and our code to make sure it could handle it.
hot_gril 4y ago
> Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain. Yep, happens to the best of us. Never mess around with this, just use a bigserial.
- hot_gril 4y ago
  (Or other things like UUIDs, which I would not use, are still way better than semantic keys.)

the_duke 4y ago

Almost every time I picked a natural key I have regretted it in the long run. Requirements change, or index sizes get bloated and hamper performance, or you need a nice, short ID for URLs , or foreign keys get more complicated with compound primary keys, or ... Natural keys are nice in theory, but not so much in practice. Especially if we are talking about active databases where large migrations are a burden.

thewataccount 4y ago

I'm strongly against these, I've always regretted it. SKUS, emails, etc all seemed liked good keys. They're always unique right? Until one day when they decided to rename some skus, they suddenly want family accounts, you realize you really do want the ability to have duplicates so you can keep historical copies without ripping up your entire database. Semantics change. A UUID/whatever does not. I've learned you should never ever use a natural key. PKs are extremely difficult or near impossible to replace depending on your application, and if that really means using serial id's, or UUIDs, or extra lookups - it's worth doing that instead of using natural keys.

tragomaskhalos 4y ago

For relational purists, e.g. Joe Celko, this approach is pretty much mandatory. However as others have stated in reply it's very difficult to find a genuinely immutable identifying attribute in many domains, including human beings.

hcarvalhoalves 4y ago

To add to the other comments mentioning why this is difficult in practice: It's the "Ship of Theseus" paradox [1]. Choosing a semantic key means mixing identity and attribute, while a synthetic key solves by assuming "constitution is not identity". Since a digital system is a model of the world, a synthetic key allows the system to address objects in this internal model without assuming a particular interpretation of identity in the real world. E.g., it's often the case that you do need to have two "customer" entries in your system that represent the same physical "person" in the world, and this is ok because the concept of "customer" is useful and sufficient for your model, and the physical person isn't. More often than not, people get caught on this trap in relational databases and object oriented modelling. This can be seen in books and lectures that use Customer-Order-Product relations to teach databases, or Car-Engine to teach OO. [1] https://en.wikipedia.org/wiki/Ship_of_Theseus https://en.wikipedia.org/wiki/Ship_of_Theseus

cccybernetic 4y ago
The existence of a synthetic key contradicts the idea that we're modeling the "world". Of course, this is a debate that’s been around forever, and I understand the advantages of synthetic keys, but I've found that the intuitive elegance of a natural key will often flow into the business logic, untangling nests of code dedicated to id look-ups, id-matching, filtering, mapping, and the general slicing and dicing and shaping of data. Queries that once referenced obscure ids now point directly to fields which intuitively make sense: yes, if I want to update the user_apps table, I know or can reasonably infer pk(user_id, app_id), and I know those values and I have them right here in my pocket, don't need to look them up, and they mean something very tangible and real and I don’t have to say “…WHERE id = ‘…’” Of course, nothing is ever perfect and natural keys have their issues, especially when migrating data, but there’s something about them that’s always “clicked” with how I reason about software systems.
- thewataccount 4y ago
  I originally thought the same - I manage the backend/database for a small team that works on our warehouse/website integration with a legacy ERP system. My experiences with semantic keys has been awful. I've been told "Oh this [property] will either never need to change, and never have duplicates" of properties you'd think really should never change, several times. Somehow a different department decided to change the skus. I've seen emails need to either have duplicates or be changed. I've seen a few instances of order numbers from the ERP system having duplicates under certain circumstances. A few cases like GTINs where every item should already have a unique one assigned - until we have an item that is missing one and will never be assigned one.... The big one is needing to archive things or keep histories of objects - if you use a synthetic key it's super easy to just add a "active" flag and you have to change very little code. I totally get wanting to avoid extra queries and extra steps, but if the need to change a pk _ever_ occurs it's terrible to near impossible (in the case of external dependencies). Many frameworks like django make it fairly easy to naturally add that extra step without needing to make spaghetti to do your lookups

jsmith45 4y ago

Semantic key keys have downsides, and it is also important to note that there is not always a single unique semantic key. Relation theory has the concept of superkeys, which are a set of columns that uniquely determine a row. But these are not useful, since for example, the set all all columns should uniquely determine a row. What is useful is "candidate keys", which are minimal superkeys, with any columsn not necessary to be unique removed. There are two ways to determine candidate keys. One is empirically by analyzing the data. If you do it that way, then the "candidate" naming is appropriate, since it is possible that some sets of columns are unique simply by chance, not by fundamental nature, and unique by chance is not what we want. Alternatively you can use domain knowledge and logic to determine candidate keys. Candidate keys determined by logic are true keys, since no duplicates should ever occur unless the requirements or fundamental nature of the data changes. This means that ideally, all such keys should have a unique constraint placed on them (although the implicit unique constraint from marking as a primary key will works for one of these keys). Adding unique constraints for all logically determined candidate keys is the ideal way to avoid accidental duplication. Within the database and within the application, you ideally want to only use small keys that are unlikely to change, and are unlikely to ever become non-unique. Keys that change tend to cause headaches with updates if referenced elsewhere, and you can have undesirable race condition issues with application logic on changing keys. Similarly, for keys likely to become non-unique from changing requirements, using them within the database means a much bigger refactor later if they become no longer unique. But if you never use those value to reference within the database, then simply dropping the unique constraint is easy. Impact on application code may vary, from potentially no change needed at all, to much more significant changes, depending on the data in question and how the application uses it. Large keys that don't change, and are extremely unlikely to ever become non-unique are conceptually fine, but have the practical problem of being large, and thus undesirable to reference from all over the database from a file size perspective. This is especially true of multi-column keys which also tend to be inconvenient from a query writing perspective. Another important issue is that many identifiers that are supposed to be universally unique, like UPCs, ISBNs etc, are not actually always unique. These things do end up getting occasionally reused, usually accidentally. If you are using that everywhere as your primary key, and eventually come across such a scenario, it is a real nightmare to refactor everything to use a different key in order to be able to handle this. While if you are using some surrogate key almost everywhere, it becomes a lot more feasible to handle this with things like having "lookup by UPC" screens show a list of options when you stumble upon one that happens to have a duplicate.

cyclotron3k 4y ago

I have to disagree with you there, for all the reasons mentioned above, but also it makes generating queries and tooling so much harder. If you see a user_id column, you know it's going to link to user.id. if you see email_id, you know it's going to link to email.id, etc, etc. There's a lot of value in having a predictable schema.