OA system 15: leave function one: leave process database design (database design of linear workflow!); (unfinished, to be modified...)

Database design of multi-level leave approval process:

For more specific process requirements may have a look: OA System II: Project Description: Demand description; function display; . For the above workflow, [how to design the database table at the bottom level] and [how to control these data in the program] are the key points!

Note: The leave application submitted by the department manager, no matter how long the leave application is, requires the approval of the general manager.

………………………………………………

(1) LeaveForm leave form: When an employee applies for leave, he needs to fill in the form, and the form information is stored in this form; (PS: Leave means leave; it also means vacation and permission)
(2) ProcessFlow approval task flow table: After the employee submits the leave form, the ProcessFlow form will be automatically created, that is, once the leave form is submitted, whether the general manager's approval is required has been determined, so the charge-do items are used as data in the ProcessFlow form Stored therein, through the ProcessFlow table, after logging in, each level of leadership can see the to-do items to be done ;
(3) Notice message notification form: an auxiliary form; (we hope this: after a grassroots employee submits an application for leave, then the leader of the grassroots employee will receive a message [a grassroots employee has asked for leave after logging in to the system Apply, please approve quickly] message notification; then, after the leader receives the message, he can go for timely approval.)
On the whole, the leave process is not complicated, but here is a common database design at work: [Database design of task process]. Asking for leave is a typical workflow. For this linear workflow, how to represent it in data is the focus of this part.

………………………………………………

(1) Each record in the LeaveForm request form is an approval process;
(2) If employees want to ask for leave, they need to fill in a leave form on the front desk page, and then increase the price of the form, and then the leave form data will be stored in the [LeaveForm leave form]; then, it will follow the business rules to go Generate the approval task of the department manager or general manager (because if the leave time is less than 3 days, the approval of the general manager is not required), which is to create the corresponding record in the [ProcessFlow Approval Task Flow Table];
(3) This is easy to understand; for example, the department manager of the R&D department can only approve the leave applications submitted by the employees of the R&D department; the general manager can approve the leave applications of all department employees;
(4) There is nothing to say;
(5) There is nothing to say;
(6) Any operation that is generated must be notified by the system to inform relevant people, etc.; (the purpose of this article is to make the system have a better user experience)

………………………………………………

Specific table design:

(1) LeaveForm leave form:

Every employee (including ordinary employees, department managers, general managers) fills out a leave application form and submits a leave application; a record will be added to the LeaveForm table.
Description:
(1) form_type: The leave type is determined when the form is designed; therefore, when writing logic code in the background after the front-end design, it is necessary to be clear about the different meanings of the different values ​​of the form_type field;
(2) start_time and end_time: indicate the start and end time of asking for leave, usually limited to hours;
(3) create_time: the creation time of this record; this is not a business-related field; general employees fill out the leave form on the front desk page and click submit, and then the corresponding leave form in [LeaveForm leave form] A record will be created in the, create_time is the time when the record was created;
(4) state: the current state of the leave application; ( PS: when we were working on the project, these fixed values ​​were all written to death in some configuration files when writing java code, we directly call... )

(2) ProcessFlow approval task flow chart:

Employees (including ordinary employees, department managers, and general managers) fill out a leave application form after submitting a leave application; after adding a record in the LeaveForm form;;; because after the leave application is submitted, this application needs to go through an approval process, The ProcessFlow table records this approval process.
Description:
(1) form_id: is the primary key in the LeaveForm leave form table; that is, [ProcessFlow approval task flow table] and [LeaveForm leave form table] form a primary foreign key association through the form_id field; [form_id field in the ProcessFlow approval task flow table 】It is mainly used to explain which application the current execution process is for;
(2) operator_id: the operator number; because the current approval task needs different people to complete (for example, this approval task needs to be approved by the department manager); operator_id indicates who needs to handle this task. In fact, the operator_id corresponds to the employee table. For example, a grassroots employee of the R&D department submits a leave application, then the approval task needs to be processed by Qi Zimo, the department manager of the R&D department, then the operator_id is 2;
(3) action: apply indicates that the current process is an application process, and audit indicates that the current process is an approval process.
(4) result: indicates the result of the approval; indicates the result of the approval: approved or rejected; naturally, this field can be empty; (for specific reasons, you can see the following example)
(5) reason: Approval comments; naturally this field can also be empty; (for specific reasons, you can see the following example)
(6) audit_time: approval time: it is the approval time; naturally, this field can also be empty; (for specific reasons, you can see the following example)
(7) order_no: the sequence number of the task; for example, after an ordinary employee submits an application for leave for more than 72 hours, then this application (assuming the department manager will approve it) needs to go through [Ordinary employee submits leave task], [department manager approval 】 【General Manager Approval】Three processing stages, then 1, 2, 3 can be used to represent 【The three stages of approval for leave application】;
(8) state: the "current" processing state of the leave application;
(9) is_last: For example, if an ordinary employee submits a leave application for less than 72 hours, then [the process of the department manager] is the final node; if an ordinary employee submits a leave application for more than 72 hours, then [ The process of the department manager] is not the final node, but the process of the general manager is the final node;

(3) Notice message notification table

To assist the normal operation of the system, the purpose of this table is to make the system have a better user experience; every time there is some [new form application] or [every time the form application goes to a new process], the system will respond accordingly Send the corresponding message;
Description:
(1) receiver_id: Who should this message be shown to; for example, when Song Caini from the R&D department of employee No. 4 submits a leave application, then the leave application needs to be approved by Qi Zimo, the manager of the R&D department, that is, it will be listed in the notice form Add a record, the receiver_id of this record is 2; it means that this message is for Qi Zimo, the R&D manager with employee_id=2;

(4) Description of table examples:

LeaveForm leave form:

(1) Because [Song Caini, a grass-roots employee of the R&D department] just submitted an application for leave, the value of the state field is processing; it means that the current state of the leave form is under review;
(2) [Song Caini, a grass-roots employee of the R&D department] submitted this leave application at [2020-09-14 09:02:35];

Process Flow Approval Task Flow Table:

The first record:
(1) Note: In the LeaveForm leave form, [Song Caini, a grass-roots employee in the R&D department] submitted the leave application at [2020-09-14 09:02:35]; then in the ProcessFlow approval task flow table, it will be in [2020] at the same time. -09-14 09:02:35] Create a process record; that is, the two can be almost at the same time, and there may be a difference of several milliseconds or tens of milliseconds or hundreds of milliseconds;
(2) Because the above three process records are for the LeaveForm leave form [the application submitted by the grass-roots employee Song Caini of the R&D department]; naturally [form_id=1 in the ProcessFlow approval task flow table] myself;
(3) Because [Song Caini, a grass-roots employee in the R&D department] submits the application for leave, the application for leave will naturally be passed on to the manager of the R&D department; therefore, this application for leave will naturally be directly approved in the link of [Song Caini, a grass-roots employee in the R&D department] , Which is the state=complete of the first record. . . . Then, [Leaf application submitted by Song Caini, a grass-roots employee of the R&D Department] is the first step of the processing flow in [Song Caini, a grass-roots employee of the R&D Department], naturally order_no=1;;;
………………………………
The second record:
(4) Because this request for leave was initiated by [Song Caini, a grass-roots employee of the R&D Department], so this request is an application for [Song Caini, a grass-roots employee of the R&D Department], that is, action=apply;;;
(5) The create_time of the first record is [2020-09-14 09:02:35]; in [Song Caini, a grass-roots employee of R&D department], approval is not required, and this application will be passed directly to the department manager; naturally , After almost a few tens of seconds (after a short period of time), after [2020-09-14 09:03:48], the leave application will be passed to [Department Manager Qi Zimo] Ring;;;;;Because the current link needs [Department Manager Qi Zimo] to approve, so operator_id=2;;;;; Then, because the leave application is in [Department Manager Qi Zimo], the operation required in this link is approval , Namely action=audit;;;;;;;;;;;
(6) Then, [Department Manager Qi Zimo] This link is approved; [2020-09-14 16:16:18] is approved;;; Then, the leave application will be passed to Next ring [the ring of General Manager Zhang Xiaotao];
………………………………………………
The third record:
(7) The leave application was approved at [2020-09-14 16:16:18] in the section of [Department Manager Qi Zimo]; then it was passed to [2020-09-14 16:18:10] The link of General Manager Zhang Xiaotao]; Then, in the link of [General Manager Zhang Xiaotao], the general manager has not approved yet, so naturally it is the above state, state=process means it is processing.
(8) When the general manager approves, the blank content of the third record will have content; then the state of the task will become complete;
(9) Because [General Manager Zhang Xiaotao this link] is the last link, that is, is_last=1; if this link is approved, that is, after state=complete, it means the entire leave application submitted by [Song Caini, a grass-roots employee of the R&D department] The process is over. . . Then in the LeaveForm leave form [Song Caini, a grass-roots employee of the R&D department], the state of the record of the leave application also needs to be modified to approved (approved) or rejected (rejected):
………………………………………………
(10) It can be found that from the state field of the ProcessFlow approval task flow table, the database DBA can know which process each leave application has gone through in the approval process; it can also know which leave applications have been approved and which leave applications have not Be approved and completed;

Notice message notification table:

(1) The time description is as follows:

The content of the above case needs to be modified; some unclear areas need to be modified; specific process issues;

At present, the general content is OK, but there are still several issues that are not particularly clear, and need to be clarified before they can be improved;