| There's no perfect way to do anything in software, especially when it comes to a database.
There are always tradeoffs. There are times when I'd skip constraints and trust the application logic to handle it, sounds like you've run into those as well. Personally I just have a really high bar when it comes to moving data integrity out of the db and into the app code. Stale data isn't usually my concern, I'm more concerned with how simply and clearly I can define the data contract with anything consuming the data. When I can guarantee that a column marked as a foreign key will always be a valid foreign key, consumers aren't at risk of a whole class of errors when reading and writing data. Any one query may be marginally slower because the constraint is in the database and always executed, but I know for sure that I'll never have a frontend blowing up because they didn't realize the foreign key isn't really a foreign key, or a backend accidentally writing bad data because the foreign key value wasn't manually checked for validity before being written. I can say that the larger the team I've been on the less I've seen issues with data integrity loving outside the database, assuming the project is architected well. When an entire team is dedicated to the database and another team, or teams, are dedicated to just the application logic that manages the db, it tends to be much better documented and tested. Smaller teams have a tendency to along code around much faster and write fewer tests while still finding product-market fit. In those cases, database constraints are an absolute must in my opinion, app logic is just moving too quickly to have any faith in it maintaining data integrity and teams are often growing quickly enough that stuff falls through the cracks. Honestly as long as someone on the project is seriously considering the tradeoffs, the team is in a pretty damn good spot regardless of what their needs and preferences end up being though! |
Nice to chat!