Last post Nov 10, 2010 11:56 PM by firstname.lastname@example.org
Nov 10, 2010 05:53 AMemail@example.com|LINK
I am finding it very difficult to design business process workflow database.
I have to implement a business process that needs to be implemented whenever any new customer comes to open an account.
Here is the business process steps:
1. account opening clerk creates customer record and escalate to supervisor for data entry review.
2. If data entry is ok then he esclates this record to manager for customer approval otherwise sends back to clerk for required amendments in customer record.
3. If approver approves customer record then customer status should be set to approved otherwise rejected in case of reject action.
There are various workques exists for each role
1. clerk work queue for holding records which have been sent back for amendments
2. clerk work queue for holding records which have been approved/rejected so that he could start account opening activity on the approved ones.
3. supervisor work queue where he can see what are the customers that need data entry review.
4. approver work queue where approver can see what are the customers that need approval.
I am not able to visualize database design to handle this workflow.
What are tables that would be required to achieve this.
How customer status or workflow status/stage would be maintained during various actions 'Escalate to supervisor'/send back/approved/rejected .
How tasks would be handed over to another user of same role if user does not attend task for long time.
I would be thankful if you could suggest me the database design to handle workqueus and statues efficiently.
Nov 10, 2010 09:48 PM|henderson1134|LINK
There's several different approaches that you could take. Think of them as static vs dynamic. My preference is dynamic. By that I mean the request doesn't need to be moved around from one Que table to another... that'd just be a lot of transactions and
possibilities for issues (deadlocks, etc). I would create the customer record table/s based on what data needs to be stored for a customer record. In that table I would put a ApprovalStatus field as an int. Upon process start the status would be set to
say zero. When the first clerk is done, set it to 10. If the supervisor approves the status goes to 20... else it goes to 15. The manager gets all the status 20's and if they're approved it goes to 30, else 25. That type of thing. You won't need an actual
Que. The query to grab the records needed for Supervisor approval will be all those that are in status 10 (clerk submitted) or 15 (supervisor rejected). Similar for the Managers Doing something like this will avoid having to pop off of one Que and push
on to another (two transactions, a delete and an insert) vs doing one in-place update (the fastest kind).
A brief list of possible status':
0 = start of process
10 = clerk done
15 = supervisor rejected
20 = supervisor accepted
25 = manager rejected
30 = manager accepted
The next question is assigning the record to the proper person. I would need some more business rules to get fully into that. Keeping things dynamic, you could have a 'OwnerRank' type of field that has a 1 for clerk, 2 for Supervisor or 3 for Manager.
Then when you pull something it just goes by rank. If you want to have different sets of records for different employees (like Supervisor Tom needs to have a distinct list that are his responsibility vs Supervisor Mary who needs her list) then you can have
and OwnerId along with the ranking field. So a select out from the db would be a SELECT * FROM dbo.Records WHERE ApprovalStatus = 10 AND UserId = XXX. You could put that in a temp table and then grab any ApprovalStatus 10's where a LastUpdated date is older
than now... but how you'd assign that is based on more business rules not listed in your post. i.e. can any Supervisor grab any other Supervisor's late records or is there a specific chain of command (Tom can see Mary's but Tom can't see Dave's). The one
down side to the dynamic nature is the loss of what happened before. To that I'd say build a logging table and each time you change a status, insert a record to the log table the record id, the before & after status', the userid's of those involved, etc)
The point is, there's no need to create actual Que tables. Just have status fields and dynamically grab what's needed at run time. A state machine if you will.
Nov 10, 2010 11:56 PMfirstname.lastname@example.org|LINK
Thanks very much for your reply. As per your suggested solution a ownerID would keep the userID would would perform some review/approval action. But how record would sent back for dataentry correction to cleark again because task sender is not stored anywhere.