Complicated Data Constraints — Business Rules or Database Constraints?

Question

I inherited a project that includes a database (MS SQL Server) that was filled to the brim with bad data. I've manually cleaned it up, which was a painstakingly tedious process and am working on modernizing the application (ASP.NET MVC w/ EF6).

One of the tables is for tracking an item as various processes are performed on it through the organization's facility. There are a lot of rules that go into this table to make sure an item's state is valid. Some of the many rules include:

• An item only has a process performed on it if that process is valid for the job the item is assigned. (simple look up to check)
• Process B cannot be performed until Process A was marked complete (look up that there's a record in the database that Employee X completed Process A, keeping in mind that an employee can have a record that they worked on a process, but didn't complete it)
• Employee 1 cannot mark a process complete on an item if they were working on it with Employee 2, but Employee 2 is still checked into the item for that process (multiple employees can be working on an item for the same process at the same time).
• There should never be a start time for Process B that occurs before the finish time for Process A.

There are a few other rules, but those should give anyone a feel for the type of constraints that are needed. I was able to create CHECK CONSTRAINTS for a few things, such as a process being valid for a particular item, start times are always before finish times, etc. However, some of the more complicated rules that require look-ups before insertion, updates, or deletions (as described above) I'm not sure if they should be business rules within the application or a trigger / stored procedure that prevents data from being inserted/updated/removed that could cause an invalid state for an item.

I guess I'm just not sure if it's OK to allow for the potential for an invalid state if someone attempts to perform a C_UD query directly on the database. If this is the case it's pretty clear that these would be best as business rules that I can have within an entity, but it might be faster to have the database handle these operations, as read speed is way more important than write speed for this application. What's the consensus of where within an application's stack should these types of constraints reside?

Answer 1

For complicated rules you almost always want to implement them in code not the database, especially in the modern world. The advantages in code are:

• code is vastly more capable of advanced logic functions -- things like inversion of control and dependency injection make rules much easier to implement in a flexible manner.
• code is vastly more readable than db constraints for most developers; it is also significantly easier to version control and deploy
• code is vastly easier to test -- you don't need to mock up the DB to mock up the constraints to test the constraints.
• code is vastly easier to change -- what happens when a rule changes that was implemented as a DB constraint but you need to let the data for the old stuff remain while changing new items? That gets very, very tricky with DB constraints.

DB constraints are not entirely useless but they are best left to back-stopping scenarios that should never happen. Like an order line without an associated order ID. But business stuff should be kept in the code layer wherever possible.