4、数据库设计：核心表结构设计

数据库设计这块，说实话是OA系统的地基。地基没打好，后面业务逻辑写得再漂亮也白搭。我这些年见过太多项目，上线半年后开始卡顿，一查全是表结构设计不合理导致的。

今天咱们就聊聊用户表、部门表、角色表、权限表这四张核心表。嗯，还有ER图和索引优化，一个都不能少。

4.1 用户表：最基础也最容易踩坑

用户表，说白了就是存人的信息。但你别小看它，我见过有人把用户表设计成30多个字段，结果查询慢得要命。

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(128) NOT NULL COMMENT '密码（加密后）',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：0禁用 1启用',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '所属部门ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_dept_id` (`dept_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
我的习惯：密码字段长度设128位，别问为什么。因为不同加密算法输出长度不一样，我吃过这个亏——之前用MD5只设了32位，后来升级SHA-256直接报错。

4.2 部门表：树形结构的两种玩法

部门表最头疼的是层级关系。你想想看，一个集团可能有5级部门，怎么存？

我个人推荐用闭包表，但考虑到OA系统的复杂度，邻接表其实更实用。

CREATE TABLE `sys_dept` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `name` varchar(100) NOT NULL COMMENT '部门名称',
  `parent_id` bigint(20) DEFAULT '0' COMMENT '父部门ID',
  `level` int(11) DEFAULT '0' COMMENT '层级',
  `path` varchar(500) DEFAULT NULL COMMENT '路径，如：/1/2/3',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
避坑指南：我曾经在项目里没加path字段，结果每次查子部门都要递归查询，性能惨不忍睹。后来加了path字段，用like查询，速度直接提升10倍。

4.3 角色表：权限的中间人

角色表其实很简单，就是给用户打个标签。但要注意，角色和权限是多对多的关系。

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `name` varchar(50) NOT NULL COMMENT '角色名称',
  `code` varchar(50) NOT NULL COMMENT '角色编码',
  `description` varchar(255) DEFAULT NULL COMMENT '角色描述',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

这里有个细节：角色编码一定要唯一。为什么呢？因为代码里经常用编码做判断，比如 if(roleCode == 'ADMIN')，重复了就乱套了。

4.4 权限表：RBAC模型的核心

权限设计我建议用RBAC（基于角色的访问控制）模型。说白了就是：用户→角色→权限，三层结构。

-- 权限表
CREATE TABLE `sys_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `name` varchar(100) NOT NULL COMMENT '权限名称',
  `permission` varchar(200) NOT NULL COMMENT '权限标识，如：user:add',
  `type` tinyint(1) DEFAULT '1' COMMENT '类型：1菜单 2按钮 3API',
  `parent_id` bigint(20) DEFAULT '0' COMMENT '父权限ID',
  `status` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_permission` (`permission`),
  KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 用户-角色关联表
CREATE TABLE `sys_user_role` (
  `user_id` bigint(20) NOT NULL,
  `role_id` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

-- 角色-权限关联表
CREATE TABLE `sys_role_permission` (
  `role_id` bigint(20) NOT NULL,
  `permission_id` bigint(20) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';
注意：关联表的主键用联合主键，别单独加id字段。我见过有人给关联表加自增id，查询时多一次索引扫描，完全没必要。

4.5 ER图设计：理清关系

ER图说白了就是画清楚表之间的关系。咱们这四张表的关系其实很清晰：

  • 用户 ↔ 部门：多对一。一个部门有多个用户，一个用户只属于一个部门
  • 用户 ↔ 角色：多对多。通过 sys_user_role 关联
  • 角色 ↔ 权限：多对多。通过 sys_role_permission 关联

你想想看，为什么用户和角色要拆成多对多？因为一个用户可能有多个角色，比如既是部门经理又是项目管理员。一个角色也可能分配给多个用户。

4.6 索引优化策略：别让查询卡死

索引优化这块，我踩过的坑最多。给你几个实战经验：

  1. 联合索引最左前缀原则：比如 (dept_id, status) 这个联合索引，查询时条件必须从dept_id开始，跳过dept_id直接查status就用不上索引
  2. 避免在索引列上做函数操作：比如 WHERE DATE(create_time) = '2024-01-01'，索引就废了。改成 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'
  3. 覆盖索引：查询的字段都在索引里，就不用回表了。比如 SELECT id, username FROM sys_user WHERE status = 1，如果索引是 (status, id, username)，那就完美
我的经验：曾经有个OA系统，用户表才10万条数据，但查询部门用户列表要3秒。一查发现是 LEFT JOIN 了5张表，而且没加索引。后来加了联合索引，查询降到0.02秒。所以啊，索引不是越多越好，但该加的一定要加。

4.7 总结一下

数据库设计没有银弹，但有套路。核心表结构设计记住三点：

  • 字段类型选对，别用varchar存数字
  • 关联关系理清，多对多用中间表
  • 索引策略想好，慢查询提前预防

嗯，今天就聊到这儿。下一章咱们聊聊业务模块的表设计，比如审批流、考勤这些，到时候再细说。

← 上一章 📖 返回目录 下一章 →