4 ms·

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 identit

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
hot_gril 4y ago
Ship of Theseus is the thing I was looking for. Our dept has repeatedly suffered from this same mistake. Our team was stuck with this damn three-part PK that also gets exposed to partner teams using our service as an identifier, and is now baked into their code and their clients' code. Now we took it out as the PK, but it's still used as an identifier. We're making a bunch of hacks to ensure that ID stays unique. The analogy went further when the partner team asked, can we have a "stable ID" that doesn't change even if that identifier changes. Our team was close to exposing our new row-level keys again. I asked, if the name changes, is it the same thing still? What if the name and the other attributes change? This isn't like a social media post that obviously has an identifier; we're modeling physical objects. Why do you need this feature again? Turns out they didn't need the feature, or even quite understand what they were asking. The way our application is, really we didn't have to expose any identifier with guarantees about uniqueness.

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

hot_gril 4y ago

Ship of Theseus is the thing I was looking for. Our dept has repeatedly suffered from this same mistake. Our team was stuck with this damn three-part PK that also gets exposed to partner teams using our service as an identifier, and is now baked into their code and their clients' code. Now we took it out as the PK, but it's still used as an identifier. We're making a bunch of hacks to ensure that ID stays unique. The analogy went further when the partner team asked, can we have a "stable ID" that doesn't change even if that identifier changes. Our team was close to exposing our new row-level keys again. I asked, if the name changes, is it the same thing still? What if the name and the other attributes change? This isn't like a social media post that obviously has an identifier; we're modeling physical objects. Why do you need this feature again? Turns out they didn't need the feature, or even quite understand what they were asking. The way our application is, really we didn't have to expose any identifier with guarantees about uniqueness.