3 ms·

Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as

Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a small isolated place. Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.

aidos 2y ago
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.
- spoiler 2y ago
  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 millions of rows, isn't that big of a deal? Obviously there _are_ cases where nat pks/fks are bad, but not all of them.
  - mixmastamyk 2y ago
    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.
  - aidos 2y ago
    Little bit hazy because it was 15 years ago. I’d used an external int id that was syncing down into our system. There were then a bunch of other tables referencing it as a foreign key. Overnight it turned into a sting. Clearly I was going to have some reconciliation work to do in any case and it may be that I maintained the old id as the primary key at that point (can’t recall). It was just a good lesson in mitigating the blast radius - especially wrt things that are out of your control. As an analogy, imagine that you’re dealing with dates in the same system (we were), you wouldn’t let their date format bleed into your other tables. You draw a line at the boundary between the systems. In a way, natural keys break that rule.
- naasking 2y ago
  > 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. I've kept with this advice for the most part, but I'm tempted in some cases to use the external id when there's some guarantee of stability and universality. Like 2 and 3 digit ISO country codes.
  - Piskvorrr 2y ago
    Not that I'd get about 5 different ISO country code changes (with some flipflopping) just by sitting in this very same spot for a couple of decades. "Stability" in country codes, bah humbug.
    - naasking 2y ago
      Your geographical location's sorting into a country might not be stable, but I'm referring to the ISO codes that name the country, which should be relatively stable.
      - Piskvorrr 2y ago
        ISO 3166-2:XK - self-assigned, in use, but I wouldn't go as far as calling it "stable." I mean, it's been in an explicitly declared unstable state ("about to be assigned an official code") for over a decade.

aidos 2y ago

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.

spoiler 2y ago
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 millions of rows, isn't that big of a deal? Obviously there _are_ cases where nat pks/fks are bad, but not all of them.
- mixmastamyk 2y ago
  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.
- aidos 2y ago
  Little bit hazy because it was 15 years ago. I’d used an external int id that was syncing down into our system. There were then a bunch of other tables referencing it as a foreign key. Overnight it turned into a sting. Clearly I was going to have some reconciliation work to do in any case and it may be that I maintained the old id as the primary key at that point (can’t recall). It was just a good lesson in mitigating the blast radius - especially wrt things that are out of your control. As an analogy, imagine that you’re dealing with dates in the same system (we were), you wouldn’t let their date format bleed into your other tables. You draw a line at the boundary between the systems. In a way, natural keys break that rule.
naasking 2y ago
> 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. I've kept with this advice for the most part, but I'm tempted in some cases to use the external id when there's some guarantee of stability and universality. Like 2 and 3 digit ISO country codes.
- Piskvorrr 2y ago
  Not that I'd get about 5 different ISO country code changes (with some flipflopping) just by sitting in this very same spot for a couple of decades. "Stability" in country codes, bah humbug.
  - naasking 2y ago
    Your geographical location's sorting into a country might not be stable, but I'm referring to the ISO codes that name the country, which should be relatively stable.
    - Piskvorrr 2y ago
      ISO 3166-2:XK - self-assigned, in use, but I wouldn't go as far as calling it "stable." I mean, it's been in an explicitly declared unstable state ("about to be assigned an official code") for over a decade.