简介这份《OA办公系统大数据库设计》文档面向OA办公自动化管理系统的设计、开发、验收、评审与测试人员帮助读者理清从数据分析到物理数据库落地的完整思路。文档以MSSQL SERVER 2008 R2为数据库环境数据库命名为OASYSDB内容涵盖引言、数据字典设计与数据库设计三大板块其中数据字典部分以卡片形式逐项定义人员编号、姓名、性别、部门、岗位、工资、密码等个人信息数据项的数据类型与位置数据库设计部分则涉及系统物理结构、表设计、表间关联、存储过程、触发器及Job设计等模块并附有变更记录与签字确认页结构规范、层次清晰。资源包内含1个doc文档大小约211KB适合数据库设计初学者、课程设计或项目开发人员作为建模参考与文档模板使用。目前已有144人学习可帮助读者快速掌握OA系统数据字典编写规范与数据库建模要点。1. OA 办公系统数据库设计为什么“能跑”和“扛得住”是两码事很多团队做 OA 办公系统第一版数据库设计往往半天就画完了用户表、部门表、请假表、审批表外键一连ER 图一贴开发直接开干。上线三个月数据量一上来审批流查询开始变慢待办列表翻页卡顿统计报表跑一次要几十秒。这时候才回头改表结构代价是数据迁移、代码重写、线上停服。OA 办公系统的数据库设计和普通业务系统最大的区别在于它的数据模型天然是“树 流 权限”三件事叠在一起。组织架构是树审批流程是流数据可见范围是权限。这三样东西如果不在建表阶段想清楚后面每加一个功能都是在还债。这篇笔记面向的是正在做或准备做 OA 系统的后端开发我会把用户信息表、组织架构、审批流、权限控制这几块的表设计思路和踩过的坑讲清楚给出可以直接抄的建表 SQL 和参数说明。适合新手跟着建表也适合熟手对照检查自己的设计有没有留隐患。2. 用户信息表与组织架构OA 数据库的地基怎么打2.1 用户信息表不只是存账号密码很多人设计用户信息表习惯性照着“第1关:数据库表设计 - 用户信息表”那种教学模板来id、username、password、email、phone完事。放到 OA 场景里这张表至少还要承担四个职责关联组织节点、标记账号状态、支持多端登录、承载扩展属性。先说组织节点。OA 里一个人可能同时属于行政部门和某个项目组如果只在用户表里放一个 dept_id后面做矩阵式管理就要加字段。常见做法是用户表和部门表之间用一张关联表来维护多对多关系用户表本身只存主部门 ID 用于默认展示。账号状态字段看起来简单但 OA 里“离职”和“禁用”是两回事。离职账号要保留历史审批记录禁用账号只是临时冻结。所以状态字段至少要有正常、禁用、离职、待激活四种。用 tinyint 存不要用 varchar 存中文否则索引效率差。CREATE TABLE oa_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(64) NOT NULL COMMENT 登录名, password_hash VARCHAR(128) NOT NULL COMMENT 密码哈希不存明文, real_name VARCHAR(64) NOT NULL DEFAULT COMMENT 真实姓名, primary_dept_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 主部门ID, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 2禁用 3离职 4待激活, email VARCHAR(128) NOT NULL DEFAULT COMMENT 邮箱, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, ext_json JSON DEFAULT NULL COMMENT 扩展属性存工号、职级等, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username), KEY idx_dept_status (primary_dept_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTOA用户表;这段建表语句里几个关键点password_hash长度给到 128因为 bcrypt 出来的哈希值通常在 60 位左右留足余量ext_json用 JSON 类型而不是多个预留字段OA 系统里工号、职级、入职日期这些属性各公司差异大JSON 列配合 MySQL 5.7 以上的虚拟列索引可以兼顾灵活性和查询效率idx_dept_status这个联合索引是为了“查某个部门下所有正常状态的人”这个高频查询准备的顺序不能反部门在前状态在后因为部门筛选的区分度更高。2.2 组织架构树用 parent_id 还是路径枚举部门表的设计是 OA 数据库里第一个容易翻车的地方。最常见的做法是parent_id邻接表简单直观但查“某个部门及其所有子部门下的员工”时要么递归查询要么在应用层循环。MySQL 8.0 支持 CTE 递归但很多公司还在用 5.7。另一种做法是路径枚举存一个path字段比如/1/3/7/查子树用LIKE /1/3/%就能搞定。缺点是部门移动时路径要批量更新。我一般会同时保留parent_id和pathparent_id用于维护父子关系path用于快速查询子树。移动部门这种低频操作批量更新路径可以接受。CREATE TABLE oa_dept ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL COMMENT 部门名称, parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 父部门ID0为根, path VARCHAR(512) NOT NULL DEFAULT COMMENT 路径枚举如/1/3/7/, sort_order INT NOT NULL DEFAULT 0 COMMENT 同级排序, leader_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 部门负责人, status TINYINT NOT NULL DEFAULT 1 COMMENT 1启用 0停用, PRIMARY KEY (id), KEY idx_parent (parent_id), KEY idx_path (path(255)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTOA部门表;path字段长度给到 512按每级 ID 平均 6 位加斜杠算可以支撑 70 层以上的嵌套实际 OA 组织架构不会超过 10 层。idx_path前缀索引取 255 是因为 utf8mb4 下索引长度限制实际路径很少超过这个长度。leader_user_id冗余在部门表里是为了审批流找审批人时不用再关联用户表。用户和部门的多对多关系用一张关联表CREATE TABLE oa_user_dept ( user_id BIGINT UNSIGNED NOT NULL, dept_id BIGINT UNSIGNED NOT NULL, is_primary TINYINT NOT NULL DEFAULT 0 COMMENT 是否主部门, PRIMARY KEY (user_id, dept_id), KEY idx_dept (dept_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户部门关联表;主键用联合主键而不是自增 ID因为这张表就是纯粹的关联关系联合主键天然去重而且 InnoDB 的聚簇索引直接按 (user_id, dept_id) 组织数据查“某用户属于哪些部门”时走聚簇索引效率最高。idx_dept是为了反向查询“某部门有哪些人”。注意如果公司组织架构经常调整建议在部门表加一个effective_date和expire_date做时间维度版本管理否则历史审批记录里的部门信息会随着组织调整而“漂移”。3. 审批流数据建模怎么让流程可追溯又不拖慢查询3.1 流程模板、流程实例、审批任务分三层OA 系统最核心的功能就是审批流。很多初级设计把审批流做成一张大表每走一步更新一行记录结果就是查询待办时全表扫描历史记录和当前状态混在一起。正确的做法是分三层流程模板定义、流程实例、审批任务。流程模板定义描述“这个审批长什么样”比如请假审批需要先部门主管审批再 HR 审批。流程实例描述“这一次审批”关联发起人和模板。审批任务描述“这一步该谁审”关联实例和审批人。三层分开之后待办查询只需要查审批任务表里状态为“待处理”且审批人等于当前用户的记录索引一建毫秒级返回。CREATE TABLE oa_flow_template ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(64) NOT NULL COMMENT 模板编码如leave_approval, name VARCHAR(128) NOT NULL COMMENT 模板名称, version INT NOT NULL DEFAULT 1 COMMENT 版本号, node_config JSON NOT NULL COMMENT 节点配置存审批链, status TINYINT NOT NULL DEFAULT 1 COMMENT 1启用 0停用, PRIMARY KEY (id), UNIQUE KEY uk_code_version (code, version) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT审批流程模板;node_config用 JSON 存审批链配置比如[{node:dept_leader,type:user},{node:hr,type:role}]。这样做的好处是加签、改审批人不需要改表结构。uk_code_version保证同一个模板编码可以有多个版本流程实例创建时锁定版本号这样即使模板后来改了历史实例的审批链不会变。CREATE TABLE oa_flow_instance ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, template_code VARCHAR(64) NOT NULL, template_version INT NOT NULL, initiator_id BIGINT UNSIGNED NOT NULL COMMENT 发起人, biz_type VARCHAR(32) NOT NULL COMMENT 业务类型如leave、expense, biz_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 业务数据ID, status TINYINT NOT NULL DEFAULT 1 COMMENT 1审批中 2通过 3驳回 4撤销, form_data JSON DEFAULT NULL COMMENT 表单快照, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_initiator (initiator_id, status), KEY idx_biz (biz_type, biz_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT审批流程实例;form_data存表单快照很重要。OA 里经常出现这种情况员工提交请假单时填了 3 天后来表单字段改了再看历史记录发现数据对不上。存快照就是留后悔药保证任何时候回看都是当时提交的原始数据。idx_initiator支持“我发起的审批”列表查询idx_biz支持从业务单据反查审批流。CREATE TABLE oa_flow_task ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, instance_id BIGINT UNSIGNED NOT NULL, node_name VARCHAR(64) NOT NULL COMMENT 节点名称, assignee_id BIGINT UNSIGNED NOT NULL COMMENT 审批人, status TINYINT NOT NULL DEFAULT 1 COMMENT 1待处理 2已通过 3已驳回 4已转交, comment VARCHAR(512) NOT NULL DEFAULT COMMENT 审批意见, handled_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_assignee_status (assignee_id, status), KEY idx_instance (instance_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT审批任务;待办列表的核心查询就是WHERE assignee_id ? AND status 1idx_assignee_status直接覆盖。已办列表查assignee_id ? AND status IN (2,3,4)同样走这个索引。idx_instance用于查看某个审批实例的所有任务节点。3.2 审批人解析角色、主管、指定人怎么统一处理审批流里最麻烦的不是表结构是“这一步该谁审”。常见的有三种指定人审批、角色审批如 HR 角色所有人、主管审批发起人的直接上级。如果每种类型写一套代码后面加一种类型就要改代码。我一般会在应用层做一个审批人解析器输入是节点配置和流程上下文输出是审批人 ID 列表。节点配置里用type区分user直接取指定人role查角色关联表leader根据发起人的primary_dept_id查部门表的leader_user_id。如果部门负责人为空往上找父部门负责人直到根部门。def resolve_assignees(node_config, context): node_config: {type: leader, level: 1} context: {initiator_id: 123, dept_id: 5} 返回审批人ID列表 node_type node_config[type] if node_type user: return [node_config[user_id]] elif node_type role: # 查角色关联表返回该角色下所有正常状态用户 return query_role_users(node_config[role_code]) elif node_type leader: # 从发起人部门往上找负责人 dept_id context[dept_id] for _ in range(node_config.get(level, 1)): dept get_dept(dept_id) if dept and dept[leader_user_id]: return [dept[leader_user_id]] dept_id dept[parent_id] if dept else 0 return [] # 找不到负责人需要兜底策略 return []这段代码的关键在于leader类型的向上查找逻辑。level参数控制找几级默认 1 级就是直接主管。如果直接主管为空继续往上找直到根部门。如果根部门也没有负责人返回空列表这时候流程引擎要有兜底策略要么转给管理员要么自动通过。我一般会转给系统管理员避免流程卡死。注意审批人解析结果建议在任务创建时固化到oa_flow_task表里不要每次查询待办时实时解析。否则组织架构一调整历史待办会“换人”这是 OA 系统里最容易被投诉的问题之一。4. 权限与数据可见范围别让员工看到不该看的审批单4.1 功能权限和数据权限要分开建表OA 系统的权限分两层功能权限决定“能不能进这个页面”数据权限决定“进了页面能看到哪些数据”。很多设计把两者混在一张权限表里结果就是加一个数据规则要改权限框架。功能权限用经典的 RBAC 模型用户关联角色角色关联权限点。权限点用编码表示比如flow:approve、report:view。数据权限单独用一张规则表描述“什么角色在什么条件下能看到什么范围的数据”。CREATE TABLE oa_role ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(64) NOT NULL, name VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_code (code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT角色表; CREATE TABLE oa_user_role ( user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (user_id, role_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户角色关联; CREATE TABLE oa_permission ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(128) NOT NULL COMMENT 权限编码, name VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_code (code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT权限点; CREATE TABLE oa_role_permission ( role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (role_id, permission_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT角色权限关联;这四张表是 RBAC 的标准结构没什么花哨的。关键是权限编码的设计要有层次比如flow:approve、flow:view、flow:admin方便做通配符匹配。4.2 数据权限规则表怎么设计才灵活数据权限的常见需求普通员工只能看自己的审批单部门主管能看本部门的HR 能看全公司的。如果把这些规则硬编码在 SQL 里每加一个角色就要改代码。我一般用一张数据权限规则表来配置CREATE TABLE oa_data_rule ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, role_id BIGINT UNSIGNED NOT NULL, resource VARCHAR(64) NOT NULL COMMENT 资源标识如flow_instance, scope VARCHAR(32) NOT NULL COMMENT self/dept/dept_tree/all, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_role_resource (role_id, resource) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT数据权限规则;scope字段四个取值self只看自己dept看本部门dept_tree看本部门及子部门all看全部。查询时根据当前用户的角色查出 scope然后在 SQL 里拼对应的 WHERE 条件。def build_data_scope_sql(user_id, resource): 根据用户角色返回数据权限过滤条件 rules query_data_rules(user_id, resource) if not rules: return 10 # 无规则则无权限 # 取最宽松的规则 scopes [r[scope] for r in rules] if all in scopes: return 11 if dept_tree in scopes: dept_ids get_dept_tree_ids(get_user_dept(user_id)) return fdept_id IN ({,.join(map(str, dept_ids))}) if dept in scopes: return fdept_id {get_user_dept(user_id)} return finitiator_id {user_id}这段代码里get_dept_tree_ids利用部门表的path字段做前缀匹配一次查询就能拿到所有子部门 ID。build_data_scope_sql返回的字符串直接拼到业务查询的 WHERE 子句里。注意取最宽松规则这个策略如果一个用户有多个角色只要有一个角色给了all就按all处理。注意数据权限的 SQL 拼接一定要用参数化查询不要直接字符串拼接用户输入。上面代码里user_id和dept_id是内部查询出来的整数风险可控但如果是外部传入的参数必须走占位符。5. 避坑与排查OA 数据库设计里最容易翻车的 5 个点5.1 审批记录被覆盖历史查不到现象员工查看三个月前的审批单发现审批意见变成了最新的或者审批人显示的是当前部门负责人而不是当时的负责人。原因审批任务表只存了当前状态没有存历史快照。审批人 ID 存的是用户表外键用户改名或调岗后关联查询出来的就是新信息。解决审批任务表里冗余存审批人姓名和部门名称的快照字段或者用 JSON 存完整的审批上下文。历史数据一旦生成就不再更新保证“所见即当时”。5.2 待办列表越查越慢现象系统上线半年后待办列表加载时间从 200ms 涨到 3 秒。原因oa_flow_task表数据量到了百万级idx_assignee_status索引虽然存在但查询时没有覆盖instance_id和node_name导致回表次数过多。解决把待办查询需要的字段做成覆盖索引比如(assignee_id, status, instance_id, node_name, created_at)。如果数据量继续涨按created_at做分区表每月一个分区历史分区可以归档到冷存储。5.3 组织架构调整导致审批流断链现象部门合并后之前发起的审批流找不到审批人流程卡在中途。原因审批人解析是实时查部门负责人部门调整后负责人变了或者部门被删了。解决流程实例创建时就把所有节点的审批人解析出来存到任务表里。如果某个节点审批人为空走兜底策略转给管理员。组织架构调整时只影响新发起的流程不影响进行中的。5.4 用户表手机号字段长度不够现象插入国际号码时报错Data too long for column phone。原因手机号字段设了VARCHAR(11)只考虑了国内号码。解决改成VARCHAR(20)并且存的时候带上国家码。OA 系统如果有海外员工这个坑迟早会遇到。5.5 JSON 字段查询没走索引现象用form_data-$.days查询请假天数大于 3 的记录全表扫描。原因JSON 字段上的查询默认不走索引除非建了虚拟列索引。解决对高频查询的 JSON 字段建虚拟列然后在虚拟列上建索引ALTER TABLE oa_flow_instance ADD COLUMN leave_days INT GENERATED ALWAYS AS (form_data-$.days) VIRTUAL, ADD KEY idx_leave_days (leave_days);这样查询WHERE leave_days 3就能走索引了。虚拟列不占存储空间只在查询时计算。6. 从单库到分库OA 数据库的扩展节奏怎么把握6.1 什么时候该考虑分表OA 系统的数据增长主要来自三张表审批任务、操作日志、消息通知。用户表和部门表通常几千到几万行不需要分。审批任务表如果日增超过 10 万行一年就是 3650 万行单表查询开始吃力。我一般按这个节奏走单表 500 万行以内加索引能扛住500 万到 2000 万考虑按时间分区超过 2000 万做分表。分表键选instance_id或者assignee_id看查询模式。如果待办查询是主要场景按assignee_id分保证同一个人的待办在同一张表里。6.2 分表后的待办查询怎么改分表之后WHERE assignee_id ? AND status 1这个查询需要路由到正确的分表。如果按assignee_id分直接取模路由即可。但如果要查“我发起的审批”按initiator_id查而分表键是assignee_id就需要扫所有分表。这种场景下常见做法是建一张索引表或者用异构索引把initiator_id到instance_id的映射单独存一份查的时候先查映射表拿到instance_id列表再按instance_id路由到分表。索引表本身也可以分表按initiator_id分。CREATE TABLE oa_flow_instance_index ( initiator_id BIGINT UNSIGNED NOT NULL, instance_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (initiator_id, instance_id), KEY idx_created (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT审批实例索引表;这张索引表只存三个字段数据量再大也能扛住。写入时和主表在同一个事务里保证一致性。查询“我发起的”时先走这张表拿到instance_id列表后再去主表查详情。6.3 一个验证设计是否合理的小技巧建完表之后别急着写代码。先往每张表里插 10 万条测试数据然后用 EXPLAIN 跑一遍核心查询待办列表、已办列表、我发起的、部门人员查询、审批历史。看 type 是不是 ref 或 range看 rows 是不是在预期范围内看 Extra 里有没有 Using filesort 或 Using temporary。如果待办查询的 EXPLAIN 结果里出现了 Using filesort说明排序字段没走索引需要调整索引顺序或者加覆盖索引。如果出现了 Using temporary说明有 GROUP BY 或 DISTINCT 操作没走索引需要检查查询语句。我自己的习惯是每张核心表的每个查询场景都要有一条对应的 EXPLAIN 记录贴在设计文档里。后面加索引或者改查询时对照着看有没有退化。这个习惯帮我省了很多次线上排查的时间。注意测试数据要用真实分布不要用自增 ID 连续插入。真实场景下assignee_id是散列的status分布也不均匀用真实分布才能测出索引的真实效果。希望帮到你。本文还有配套的精品资源点击获取