第 83 章：需求分析与数据库设计

学习目标

• 完整梳理 RBAC 系统的功能需求和非功能需求
• 设计规范化的数据库结构
• 实现建表脚本与初始化数据

一、需求分析

1.1 功能需求

1.2 用户故事

角色	故事
超管	创建/禁用用户、分配角色、管理所有数据
管理员	管理本部门及子部门用户、查看所有报表
普通员工	登录系统、修改个人信息、查看自己有权限的菜单
访客	申请账号、注册（需审核）

1.3 非功能需求

维度	指标
性能	接口平均 RT < 200ms，P99 < 1s
可用性	99.9%（每月停机 < 43 分钟）
并发	支持 1000 QPS
安全	OWASP Top 10 防护、JWT 鉴权、HTTPS
可维护	模块化、文档完整、测试覆盖 ≥ 80%
可扩展	水平扩展、多租户预留

二、ER 图

三、建表 SQL

-- ============================================
-- TaskFlow 数据库初始化脚本
-- 数据库：MySQL 8.0+
-- 字符集：utf8mb4
-- ============================================

CREATE DATABASE IF NOT EXISTS `taskflow`
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `taskflow`;

-- ============================================
-- 1. 用户表
-- ============================================
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username`    VARCHAR(50)  NOT NULL COMMENT '登录名',
    `password`    VARCHAR(100) NOT NULL COMMENT '密码（BCrypt）',
    `nickname`    VARCHAR(50)           DEFAULT NULL COMMENT '昵称',
    `email`       VARCHAR(100)          DEFAULT NULL COMMENT '邮箱',
    `phone`       VARCHAR(20)           DEFAULT NULL COMMENT '手机号',
    `avatar`      VARCHAR(255)          DEFAULT NULL COMMENT '头像URL',
    `gender`      TINYINT               DEFAULT 0 COMMENT '性别：0=未知 1=男 2=女',
    `status`      TINYINT      NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常 2=锁定',
    `dept_id`     BIGINT                DEFAULT NULL COMMENT '部门ID',
    `last_login_time`  DATETIME         DEFAULT NULL COMMENT '最后登录时间',
    `last_login_ip`    VARCHAR(50)      DEFAULT NULL COMMENT '最后登录IP',
    `create_by`   BIGINT                DEFAULT NULL COMMENT '创建人',
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by`   BIGINT                DEFAULT NULL COMMENT '更新人',
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0 COMMENT '逻辑删除：0=否 1=是',
    `version`     INT          NOT NULL DEFAULT 0 COMMENT '乐观锁',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`, `is_deleted`),
    KEY `idx_dept` (`dept_id`),
    KEY `idx_status` (`status`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ============================================
-- 2. 角色表
-- ============================================
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '角色ID',
    `name`        VARCHAR(50)  NOT NULL COMMENT '角色名称',
    `code`        VARCHAR(50)  NOT NULL COMMENT '角色编码：ROLE_ADMIN',
    `description` VARCHAR(255)          DEFAULT NULL COMMENT '描述',
    `sort`        INT          NOT NULL DEFAULT 0 COMMENT '排序',
    `status`      TINYINT      NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常',
    `data_scope`  TINYINT      NOT NULL DEFAULT 4 COMMENT '数据权限：1=全部 2=本部门及下级 3=本部门 4=仅本人 5=自定义',
    `create_by`   BIGINT                DEFAULT NULL,
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_by`   BIGINT                DEFAULT NULL,
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`, `is_deleted`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表';

-- ============================================
-- 3. 菜单/权限表（合一）
-- ============================================
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
    `parent_id`   BIGINT       NOT NULL DEFAULT 0 COMMENT '父菜单ID，0=顶级',
    `name`        VARCHAR(50)  NOT NULL COMMENT '菜单名称',
    `type`        TINYINT      NOT NULL COMMENT '类型：1=目录 2=菜单 3=按钮',
    `permission`  VARCHAR(100)          DEFAULT NULL COMMENT '权限标识（按钮才有）',
    `path`        VARCHAR(200)          DEFAULT NULL COMMENT '路由路径',
    `component`   VARCHAR(200)          DEFAULT NULL COMMENT '组件路径',
    `icon`        VARCHAR(50)           DEFAULT NULL COMMENT '图标',
    `sort`        INT          NOT NULL DEFAULT 0 COMMENT '排序',
    `visible`     TINYINT      NOT NULL DEFAULT 1 COMMENT '是否可见：0=隐藏 1=显示',
    `status`      TINYINT      NOT NULL DEFAULT 1 COMMENT '状态：0=禁用 1=正常',
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_parent` (`parent_id`),
    KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菜单权限表';

-- ============================================
-- 4. 部门表
-- ============================================
DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '部门ID',
    `parent_id`   BIGINT       NOT NULL DEFAULT 0 COMMENT '父部门ID',
    `name`        VARCHAR(50)  NOT NULL COMMENT '部门名称',
    `code`        VARCHAR(50)           DEFAULT NULL COMMENT '部门编码',
    `leader`      VARCHAR(50)           DEFAULT NULL COMMENT '负责人',
    `phone`       VARCHAR(20)           DEFAULT NULL COMMENT '联系电话',
    `email`       VARCHAR(100)          DEFAULT NULL COMMENT '邮箱',
    `sort`        INT          NOT NULL DEFAULT 0,
    `status`      TINYINT      NOT NULL DEFAULT 1,
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='部门表';

-- ============================================
-- 5. 字典表
-- ============================================
DROP TABLE IF EXISTS `sys_dict`;
CREATE TABLE `sys_dict` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT,
    `type_code`   VARCHAR(50)  NOT NULL COMMENT '字典类型编码',
    `label`       VARCHAR(100) NOT NULL COMMENT '字典项显示值',
    `value`       VARCHAR(100) NOT NULL COMMENT '字典项实际值',
    `sort`        INT          NOT NULL DEFAULT 0,
    `status`      TINYINT      NOT NULL DEFAULT 1,
    `remark`      VARCHAR(255)          DEFAULT NULL,
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted`  TINYINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_type` (`type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='字典表';

-- ============================================
-- 6. 用户-角色关系
-- ============================================
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
    `user_id` BIGINT NOT NULL,
    `role_id` BIGINT NOT NULL,
    PRIMARY KEY (`user_id`, `role_id`),
    KEY `idx_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户-角色关系';

-- ============================================
-- 7. 角色-菜单关系
-- ============================================
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` (
    `role_id` BIGINT NOT NULL,
    `menu_id` BIGINT NOT NULL,
    PRIMARY KEY (`role_id`, `menu_id`),
    KEY `idx_menu` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色-菜单关系';

-- ============================================
-- 8. 角色-部门关系（数据权限自定义用）
-- ============================================
DROP TABLE IF EXISTS `sys_role_dept`;
CREATE TABLE `sys_role_dept` (
    `role_id` BIGINT NOT NULL,
    `dept_id` BIGINT NOT NULL,
    PRIMARY KEY (`role_id`, `dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色-部门关系（数据权限）';

-- ============================================
-- 9. 操作日志表
-- ============================================
DROP TABLE IF EXISTS `sys_operation_log`;
CREATE TABLE `sys_operation_log` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT,
    `user_id`     BIGINT                DEFAULT NULL COMMENT '操作用户ID',
    `username`    VARCHAR(50)           DEFAULT NULL COMMENT '操作用户名',
    `module`      VARCHAR(50)           DEFAULT NULL COMMENT '模块名',
    `operation`   VARCHAR(100)          DEFAULT NULL COMMENT '操作描述',
    `method`      VARCHAR(200)          DEFAULT NULL COMMENT '调用的方法',
    `request_url` VARCHAR(255)          DEFAULT NULL,
    `request_method` VARCHAR(10)        DEFAULT NULL COMMENT 'HTTP 方法',
    `request_params` TEXT               COMMENT '请求参数（JSON）',
    `response_result` TEXT              COMMENT '响应结果',
    `ip`          VARCHAR(50)           DEFAULT NULL,
    `location`    VARCHAR(100)          DEFAULT NULL COMMENT 'IP 归属地',
    `user_agent`  VARCHAR(500)          DEFAULT NULL,
    `cost`        BIGINT                DEFAULT NULL COMMENT '耗时（ms）',
    `status`      TINYINT      NOT NULL DEFAULT 1 COMMENT '1=成功 0=失败',
    `error_msg`   TEXT                  COMMENT '错误信息',
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_module` (`module`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志';

-- ============================================
-- 10. 登录日志表
-- ============================================
DROP TABLE IF EXISTS `sys_login_log`;
CREATE TABLE `sys_login_log` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT,
    `username`    VARCHAR(50)  NOT NULL,
    `ip`          VARCHAR(50)           DEFAULT NULL,
    `location`    VARCHAR(100)          DEFAULT NULL,
    `browser`     VARCHAR(50)           DEFAULT NULL,
    `os`          VARCHAR(50)           DEFAULT NULL,
    `status`      TINYINT      NOT NULL COMMENT '1=成功 0=失败',
    `message`     VARCHAR(255)          DEFAULT NULL COMMENT '消息',
    `login_time`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_username` (`username`),
    KEY `idx_login_time` (`login_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登录日志';

四、初始化数据

-- ============================================
-- 初始化数据
-- ============================================

-- 1. 超级管理员（密码：admin123）
INSERT INTO `sys_user` (`id`, `username`, `password`, `nickname`, `email`, `status`, `dept_id`, `version`)
VALUES (1, 'admin', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '超级管理员', 'admin@taskflow.com', 1, 1, 0);

-- 2. 部门
INSERT INTO `sys_dept` (`id`, `parent_id`, `name`, `code`, `sort`) VALUES
(1, 0, 'TaskFlow 总部', 'HQ', 1),
(2, 1, '研发部', 'RD', 1),
(3, 1, '运营部', 'OPS', 2),
(4, 1, '销售部', 'SALES', 3),
(5, 2, '后端组', 'RD-BE', 1),
(6, 2, '前端组', 'RD-FE', 2);

-- 3. 角色
INSERT INTO `sys_role` (`id`, `name`, `code`, `description`, `sort`, `data_scope`) VALUES
(1, '超级管理员', 'ROLE_ADMIN', '拥有所有权限', 1, 1),
(2, '普通管理员', 'ROLE_MANAGER', '部门管理权限', 2, 2),
(3, '普通用户', 'ROLE_USER', '基础权限', 3, 4);

-- 4. 菜单（示例）
INSERT INTO `sys_menu` (`id`, `parent_id`, `name`, `type`, `permission`, `path`, `component`, `icon`, `sort`) VALUES
(1, 0, '系统管理', 1, NULL, '/system', NULL, 'Setting', 1),
(10, 1, '用户管理', 2, NULL, '/system/user', 'system/user/index', 'User', 1),
(11, 1, '角色管理', 2, NULL, '/system/role', 'system/role/index', 'UserFilled', 2),
(12, 1, '菜单管理', 2, NULL, '/system/menu', 'system/menu/index', 'Menu', 3),
(13, 1, '部门管理', 2, NULL, '/system/dept', 'system/dept/index', 'OfficeBuilding', 4),

(100, 10, '用户新增', 3, 'user:create', NULL, NULL, NULL, 1),
(101, 10, '用户编辑', 3, 'user:update', NULL, NULL, NULL, 2),
(102, 10, '用户删除', 3, 'user:delete', NULL, NULL, NULL, 3),
(103, 10, '用户查询', 3, 'user:query', NULL, NULL, NULL, 4),

(110, 11, '角色新增', 3, 'role:create', NULL, NULL, NULL, 1),
(111, 11, '角色编辑', 3, 'role:update', NULL, NULL, NULL, 2),
(112, 11, '角色删除', 3, 'role:delete', NULL, NULL, NULL, 3),
(113, 11, '分配权限', 3, 'role:assign', NULL, NULL, NULL, 4);

-- 5. 用户-角色
INSERT INTO `sys_user_role` (`user_id`, `role_id`) VALUES (1, 1);

-- 6. 角色-菜单（超管拥有所有菜单）
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, id FROM `sys_menu`;

-- 7. 字典
INSERT INTO `sys_dict` (`type_code`, `label`, `value`, `sort`) VALUES
('user_status', '禁用', '0', 1),
('user_status', '正常', '1', 2),
('user_status', '锁定', '2', 3),
('gender', '未知', '0', 1),
('gender', '男', '1', 2),
('gender', '女', '2', 3);

五、Flyway 数据库版本管理

<!-- pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true

src/main/resources/db/migration/
├── V1__init_schema.sql          # 建表
├── V2__init_data.sql             # 初始化数据
└── V3__add_dict_table.sql        # 后续增量

六、本章小结

要点	关键
需求	功能 + 非功能 + 用户故事
数据库	8 张核心表 + 2 张日志表
必备字段	4 个审计字段 + 软删 + 乐观锁
字符集	utf8mb4
引擎	InnoDB
索引	WHERE/JOIN/ORDER BY 涉及字段
版本管理	Flyway（V1__V2__V3...）

动手练习

练习 1：基础题

在本地 MySQL 中执行本章建表脚本，并验证所有表都创建成功。

练习 2：进阶题

为 TaskFlow 增加「数据权限配置表」和「系统参数配置表」，写出对应的 SQL。

练习 3：思考题

如果产品要支持多租户（每个租户独立的数据），数据库设计应该如何调整？是加 tenant_id 字段，还是用独立 Schema/数据库？

---

下一章：第 84 章：项目初始化与基础配置 →