Design and Implementation of an OA Approval Workflow with Core Tables and Transaction Handling

Hello everyone, I am a senior architect. Office Automation (OA) is familiar to most people, and this article explains the core business of OA and how the approval process is implemented step by step.

This article is full of practical content; please read it carefully.

Approval Process

First fill in the form information, then click the approver field, select the appropriate department from the company department tree, load the users of the related role, and finally designate the approvers.

The approvers can be chosen dynamically by the user, and the approval hierarchy expands or shrinks automatically according to the number of approvers.

Using an overtime request form as an example:

After specifying the details, click Submit.

The submitted form is then reviewed level by level. If any approver rejects, the whole process fails; only when all approvers approve does the process succeed.

Now we move on to the table design.

Only two core tables are needed for the approval flow; other business tables reference the flow number via the FlowNo field.

Audit Flow Main Table (AuditFlow)

The relationship between the two tables is one‑to‑many; the number of detail records depends on the number of approvers selected in the form.

ApproFlow: 1 ======> n : ApproFlowDetail

Audit Flow Detail Table (AuditFlowDetail)

With these two core tables the OA approval workflow is complete.

What about the other related tables?

First, a user table is required because both the form applicant and the approvers are linked by a user identifier. Since users belong to departments, a department table and a user‑department association table are also needed. For simple scenarios the department layer can be omitted and all users can be searched directly.

Having the user table and the core approval tables, we can now design business‑specific tables. For example, an overtime request table is added when a user needs to apply for overtime.

Overtime Request Table (OverTimeAsk)

Now the overall flow becomes clearer.

After the database design, the remaining work is to implement the logic in code.

When the overtime request form is submitted, the selected department approvers (e.g., supervisor, department manager, general manager) are recorded and the form is submitted.

Nifty open‑source projects for freelancers – collect now

Submission Operations

Insert the current business record (e.g., overtime request) into its table.

Insert a record into the AuditFlow main table.

Insert three records into the AuditFlowDetail table (one per approver).

Send a notification to the first approver.

Key Points

All three inserts must be performed within a transaction to avoid data inconsistency.

The FlowNo field of the three inserted rows must be identical.

When inserting into AuditFlow, the BusType can be set to OverTimeAsk ; the initial status is 1 (pending).

The number of AuditFlowDetail rows depends on the number of selected approvers; the first row gets status 2 (awaiting my approval), others get status 1 (in review).

Overtime requests cannot span months; the month is validated on insert.

After the submission, the approval query process begins.

A “pending my approval” view is needed to list all forms awaiting the current user’s review.

Join AuditFlow and AuditFlowDetail on FlowNo .

Filter where AuditFlow .status = 1 and AuditFlowDetail .status = 2.

Optionally filter by AuditFlow .BusType to categorize forms.

Approval actions consist of “approve” or “reject”, with possible extensions.

Implementation Logic

Determine whether the current user agrees with the request based on the submitted form.

Query AuditFlowDetail by FlowNo , AuditUserNo , and status = 2 (awaiting my approval). If no record is found, the form has already been processed.

If a record exists, update its status to approved or rejected.

If more approvers remain, set the next approver’s status to 2 and send a notification.

If all detail records are approved, set AuditFlow .status to approved.

If any detail record is rejected, set AuditFlow .status to rejected.

Implementation Details

When an approval is granted, retrieve all AuditFlowDetail rows for the given FlowNo , count approved and rejected rows, and locate the first row still in review to decide the next step (continue, finalize, or reject).

When an approval is denied, the handling is analogous.

Thus the complete multi‑level approval workflow is implemented. In practice, many other forms (e.g., work‑report approvals) can be built on the same core tables, with optional attachment tables for files or images.

All forms share the same two‑core‑table architecture.