4 ms·

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

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