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?