Approval process Database design

To make a common process system, you should probably create the following data table:

1. Flow chart (Tbl_flow)

Field name	Data Type	Notes
flow_id	Int	Process number, PRIMARY key
Flow_no	Int	Process number, unique column
Flow_name	VARCHAR2 (100)	Process Name
Remark	VARCHAR2 (500)	Note

Sample data:

flow_id	Flow_no	Flow_name	Remark
1	5	Leave process	Leave process
2	10	Reimbursement process	Reimbursement process

2. Process node table (Tbl_flow_node)

Field name	Data Type	Notes
flow_node_id	Int	Process node number, primary key
Flow_no	Int	Process number, corresponding to the process table
Flow_node_name	VARCHAR2 (100)	Process node Name
Flow_node_role	VARCHAR2 (100)	Process roles
Remark	VARCHAR2 (500)	Note

Sample data:

flow_node_id	Flow_no	Flow_node_name	Flow_node_role	Remark
1	5	Submit a leave order	No	Submit a leave order
2	5	Department manager approval	Department Manager	Department manager approval
3	5	General manager approval	General manager	General manager approval

3. Process Line table (Tbl_flow_line)

Field name	Data Type	Notes
flow_line_id	Int	Process line number, primary key
Flow_no	Int	Process number, corresponding to the process table
prev_node_id	Int	Previous node number
next_node_id	Int	Next node number
Remark	VARCHAR2 (500)	Note

Sample data:

flow_line_id	Flow_no	prev_node_id	next_node_id	Remark
1	5	1	2	Submit to department manager for approval
2	5	2	3	Submit to general manager for approval

4. Process Role _ Employee table (Tbl_flow_role_user)

Field name	Data Type	Notes
Flow_role_name	VARCHAR2 (100)	Process role Name
user_id	Int	Employee number
dept_id	Int	Department number

Sample data:

Flow_role_name	user_id	dept_id
Department Manager	13	2
Department Manager	26	4
General manager	3	1

These are the data tables required for a common approval process.

Below, let's make a simple example, that is, leave approval.

An additional two tables are required for leave approval, one of which corresponds to the leave slip, but also includes two fields for the process number and the current node number in addition to the required data for the leave order, and another table for recording approval comments during the approval process.

5. Absence form (Tbl_leave)

Field name	data type	remarks
leave_id	int	leave number, primary key
user_id	int	leave person number
user_name	varchar2 (+)	leave name
leave_type	varchar2 (+)	leave type (sick leave, leave)
Leave_reason	varchar2 (+)	reasons for leave
start_date	date	start date
end_date	date	End Date
add_date	Date	submission Date
flow_no	int	process number
current_node	int	current node number

Add: Add another field state, which represents the status of the leave order, 0 for the draft, 1 for the approval, and 2 for the end of the approval.

6. Leave approval form (Tbl_leave_audit)

Field name	Data Type	Notes
audit_id	Int	Approval number, primary key
leave_id	Int	Leave number, corresponding to the Leave table
flow_node_id	Int	Node number
user_id	Int	Approver number
User_name	VARCHAR2 (100)	Name of approving person
Audit_info	VARCHAR2 (500)	Approval comments
Audit_date	Date	Approval date

Approval process Database design