Design and Implementation of an OA Approval Workflow with Core Database Tables

Office automation (OA) approval processes are introduced, describing how a form is filled, an approver is selected from the department tree, and the approval flow proceeds step by step.

The approval flow allows dynamic selection of approvers and automatically adjusts the number of approval levels based on the number of approvers.

Two core tables are defined: AuditFlow (the main approval flow table) and AuditFlowDetail (the detail table). The relationship between them is one‑to‑many, with the number of detail records depending on the number of approvers.

Additional tables such as a user table, department table, and a linking table are needed to associate users with departments, enabling role‑based approver selection.

For specific business scenarios, like an overtime request, an OverTimeAsk table is created and linked to the core approval tables via a flow number (FlowNo).

When a form is submitted, the system performs four operations in a single transaction: insert the business form data, insert a record into AuditFlow , insert multiple records into AuditFlowDetail (one per approver), and send a notification to the first approver.

Key points include ensuring all three inserts share the same FlowNo, using transactions to maintain data integrity, and setting appropriate initial audit statuses (e.g., 2 for "pending my approval" and 1 for "in review").

Approval handling involves querying pending approvals, updating detail records based on approval decisions, propagating the next approver when necessary, and finally updating the main AuditFlow status to "approved" or "rejected" based on the aggregate results.

The article concludes that the entire multi‑level approval process is built around these two core tables and can be extended to other form types such as work report approvals, with optional attachment tables for file uploads.