Flowwork Learning (database)

R & D department of simple workflow Database Design: Luo daijun

Item)
Project ID (Itemid) project description (itemname) process ID (routid) applicant ID (applyuserid) State (State) project type (itemtype)
1 Zhengzhou business trip Loan 1 1 borrow ticket
2 Zhengzhou travel reimbursement 3 1 Reimbursement Form

The project here refers to the data that needs to be transferred, such as official documents, borrow orders, and reimbursement orders.

Task List)
Task ID (taskid) Project ID (Itemid) Step ID (actorid) status (state) version)

1 1 1 Check out 100
2 2 3 detected 1001
3 3 3 pending detection 1002

After applying for a project, insert a record to the task list. after your application is approved or rejected, update the ID of the current step (previous step or next step ). there may be multiple approvers in a specific step. If you want to approve the application, you must first check it out. the version field is used for optimistic lock control to ensure that only one person can be checked out.





Taskhistory)
ID (ID) Project ID (Itemid) Step ID (actorid) Note (Memo) operator ID (operateuserid) Creation Time (createdate)

1 1 Chengdu 1
1 1 2 approval 2
1 1 3 approval 3
Operations in each step are written into the task history.


Process (rout)
Process ID (routid) process description (routname) department ID (deptid) version number (Version) State)
1 loan process 1 1 release
2 reimbursement process 1 1 Draft
2 budget approval process 1 1 stop

The draft Process status can be modified or deleted, and the publishing status cannot be modified or deleted. Only one version can be added, a new process can be added, or the process can be stopped.

STEP (actor)
Step ID (actorid) Step No (sortno) step description (actorname) process ID (routid)

1 1 Loan Application 1
2 2 Department Manager approval 1
3 3 Financial Manager approval 1

The sequence number is the sequence of steps. during the approval process, locate the next step based on the current sequence number, and then update the step ID of the task list in the next step. If the approval is rejected, locate the previous step, then update the step ID of the task list

Actoruser)
Step ID (actorid) handler ID (operateuserid)
1 1
2 2
2 3

One step is to have multiple handlers. The handler first checks out the task list before approving the task.

 

View: Work to be processed
Select t1.taskid, t1.itemid, t3.operateuserid

From tasklist T1

Join actor T2 on t1.actorid = t2.actorid

Join actoruser T3 on t2.actorid = t3.actorid

Where t1.state = 'Waiting for checkout'

View: My applied jobs
Select t1.itemid, t1.itemname, t1.state, t1.applyuserid, t2.actorid

From item T1

Join tasklist T2 on t1.itemid = t2.itemid




Application Time
"1 -- find the first step of the selected Process
Select actorid from actor
Where routid = 1
Order by sortno
Limit 0, 1

2 -- insert task list tasklist
Insert into tasklist (actorid, state, version, Itemid)
Values ()

3 -- insert task history
Insert into taskhistory (Itemid, actorid, memo, operateby, createdate)
Values ()
4 -- change the project item status to pending approval
Update item set state = 'wait _ to_approve 'Where Itemid = 1"







Approved
"1 -- step ID of the update task list is the ID of the next step.
Update tasklist set actorid =
(Select actorid from actor
Where routid = (select routid from actor where actorid = 1)
And actorid> 1
Order by sortno
Limit 0, 1
)
Where taskid
2 -- insert task history
Insert into taskhistory (Itemid, actorid, memo, operateby, createdate)
Values ()
3 -- change the project item status to under approval
Update item set state = 'apache' where Itemid = 1"






Rejected
"1 -- the step ID of the update task list is the ID of the first step.
Update tasklist set actorid =
(Select actorid from actor
Where routid = (select routid from actor where actorid = 1)
Order by sortno
Limit 0, 1)
Where taskid = 1
2 -- insert task history
Insert into taskhistory (Itemid, actorid, memo, operateby, createdate)
Values ()
3 -- change the project item status to rejected
Update item set state = 'jujue' where Itemid = 1"