Should data model logic live in database schema?

Question

(Apologies if some of my terminology is off, I have not seen much on this subject and am using be best terms I could come up with)

Should data model logic live in the database schema?

I am making a distinction here between business logic and data model logic. By data model logic I mean things specifically to do with data integrity, like cascading deletes (ex. deleting a customer should also delete all the customer's addresses, deleting a user should remove all that user's group memberships), rather than higher level business logic and rules. I have read through "How much business logic should the database implement?" and "Business logic: Database vs code" and absolutely agree with the general consensus there that business logic should live in code, not the database.

Part of me likes the idea of the database enforcing the structural rules of the data model. It can be argued that it is a database concern. It can simplify the code side of things; you just need to delete the main entity itself and the database will handle the appropriate cleanup automatically. And it'll help reinforce the structure of the data if anything is ever bypassing the application and hitting the database directly (ex. making manual changes via SQL, pulling in data from another source via an ETL type stored procedure).

But then the other part of me feels like that is the wrong approach. The application code now has to assume that the database will handle this properly. If ever a data model logic concern comes up that cannot be handled by the database and simple constraints, now you have data model logic living in two different places: some in code, some in the database. And if you ever choose to change your data storage to something else, you now have to re-implement that same logic on the new storage system.

Answer 1

Yes!

Why? Because unique constraints are in everyone's best interest.

Now sure some could argue that the application can do that and might even have use cases where the unique constraint needs to be violated temporarily.

To which I say, "That's not a unique constraint!"

Of course this is about more then unique constraints. Just a handy example.

The database should NOT be the center of your application. But it should be the controlling authority of what makes sense in itself. I don't go around telling files how to allocate themselves on the hard drive for exactly the same reason.

This is a line that can be blurred and abused. The key is to use the database to enforce only the rules needed to make the database reliably useful (regardless of what any app is trying to do). Not to use the database in every possible way it could be used.

I like DB's that say, "OK I've no idea what you're doing but that's some nonsense right there".

---

As for cascading deletes that's a grey area. In the object world we talk about composition vs aggregation. Aggregates come and go but if you're composed of something you need it so you can work correctly. It's lifetime is entwined with yours. At least it should be if you don't want to throw exceptions because something tried to use you when you're in a bad state.

In the database world though this is data. If you delete a customer then does that mean you never sold something to this customer? Do you have any business deleting customers that we have sales records with?

This is a relational constraint only because of a business need. The database will work fine storing records of transactions with a customer who now is nothing but a foreign key number. Why the business wants to delete customer records is anyone's guess. Maybe the legal team came in waving a court order at the IT team. This will likely break some reports but that's the way it goes.

Now if table A_audit is the history of everything that's ever been in table A that's the databases business. Nothing else has any greater authority over this kind of thing because this is the databases job. So a database enforced cascading delete here makes sense, when it's called for. Maybe there's a field for some data that it turns out you never should have collected. Now it needs to be expunged.

Of course all this is dependent on what you're using delete to represent. This is still a data MODEL. So there isn't just one reason to delete things. That's where things get tricky.

Personally, I prefer CR to UD. It's surprising how much you can do working that way. But our ideals are seldom our accomplishments.