Hi,
Im building a web application which is a process management app. Several different employee types will be shown a list of Tasks to do and as each one completes a task then it is moved on to the next employee to work on.
The Task hierarchy is Batch > Load > Assembly > Part > Task. There are currently 8 rules for determining which Task should be worked on first for each Employee type. These rules apply to size of part, and also how that parts completion will affect the Hierarchy e.g if part A is completed then it completes an entire Batch where as Part B will not as there are still other parts remaining in its Batch to be completed.
Anyway that's the elevator pitch of how the system works. What i am trying to figure out is an efficient, fast and maintainable way of doing this bearing in mind the rules might change and more rules may be added.
Initially I was intending to let the DB (sql 2005) do all the heavy lifting but i have a worry that the more complicated rules will be difficult to implement with a DB. So an alternative is to pull out a list of Tasks into a middle tier and create a Collection of objects and the apply each of the rules to the Collection. I have no doubt that each rule could be translated to T-SQL in isolation but ordering by up to 8 criteria depending on the task type feels like a lot of hassle.
One benefit i can see with the the middle tier approach is that i can create a more loosely restricted system where Task flow can be changed, that would be more difficult in the DB i think.
So what would you guys recommend? Is there a third alternative i haven't thought of ?
EDIT[1] Just to qualify this a bit more, The DB is not expected to change from what i initially develop it in.