I'm curious to hear real-world experiences that go beyond database design 101 textbooks.
Have you faced any tradeoffs, surprises, or regrets around this decision?
So just tangentially-related to foreign-key usage?
Here's one: Even if it looks convenient, ON DELETE CASCADE is often not what you want. Customers usually need something much more complicated, so you'll be writing external remove-these-before-those loops and steps anyway.
For example, a platform will never really want to wipe out the entire subgraph of records for a Customer or Product. Instead they'll want to "temporarily deactivate" the account, or at most "mark as permanently disabled, erase some sensitive or bulky information, and insert some audit/reason medatata."
Exceptions might be where the related-tables are purely a technical implementation detail, and don't represent domain objects the business thinks or cares about.
I've never seen a database with FKs that I regretted were there but plenty without FKs that caused issues. Also - generally make sure you index your FKs.
Here's one: Even if it looks convenient, ON DELETE CASCADE is often not what you want. Customers usually need something much more complicated, so you'll be writing external remove-these-before-those loops and steps anyway.
For example, a platform will never really want to wipe out the entire subgraph of records for a Customer or Product. Instead they'll want to "temporarily deactivate" the account, or at most "mark as permanently disabled, erase some sensitive or bulky information, and insert some audit/reason medatata."
Exceptions might be where the related-tables are purely a technical implementation detail, and don't represent domain objects the business thinks or cares about.
reply