mysql搭建项目管理系统数据库表设计与优化

PMS MySQL表设计应聚焦“人、事、时、物”四类关系用户与权限分离user/role/user_role三表、任务状态机拆分tasktask_status_log、树形结构用parent_idpath替代闭包表、附件元数据与引用解耦attachmentattachment_ref。项目管理系统PMS的 MySQL 表设计核心不是堆字段而是理清「人、事、时、物」四类关系——用户与角色权限、项目与任务层级、时间线与状态流转、附件与关联数据。盲目加索引或过度分表反而拖慢写入和维护。用户与权限分离用 user、role、user_role 三张表代替 user 单表加 role_type 字段常见错误是把角色硬编码进 user 表比如加 is_admin、is_pm 布尔字段或用字符串枚举 role。这会导致权限变更要改代码、无法动态增删角色、难以支持多角色如某人既是开发又是测试。正确做法是拆成三张表CREATE TABLE user ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, status TINYINT DEFAULT 1 COMMENT 0禁用,1启用);CREATE TABLE role ( id TINYINT PRIMARY KEY, name VARCHAR(50) NOT NULL COMMENT admin, pm, developer, tester);CREATE TABLE user_role ( user_id BIGINT UNSIGNED NOT NULL, role_id TINYINT NOT NULL, PRIMARY KEY (user_id, role_id), FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE, FOREIGN KEY (role_id) REFERENCES role(id));user_role 必须设复合主键避免重复绑定ON DELETE CASCADE 保证用户删除时自动清理权限查询某用户所有角色时用 JOIN 而非 IN 子查询性能更稳不要在 user 表里冗余 last_login_at 等操作字段——这类高频更新字段单独建 user_activity 表避免锁表影响登录主流程任务状态机用 task 表 task_status_log 表替代单字段 status 更新直接在 task 表里用 status 字段存 todo/in_progress/done看似简单但无法追溯谁、何时、为什么改了状态也难做「退回上一状态」或「跳过审批」等业务逻辑。状态变更应视为独立事件记录CREATE TABLE task ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, project_id BIGINT UNSIGNED NOT NULL, assignee_id BIGINT UNSIGNED, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);CREATE TABLE task_status_log ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, task_id BIGINT UNSIGNED NOT NULL, from_status VARCHAR(30), to_status VARCHAR(30) NOT NULL, operator_id BIGINT UNSIGNED NOT NULL, reason TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_task_created (task_id, created_at));task_status_log 的 INDEX 按 task_id created_at 排序查某任务最新状态时可用 ORDER BY created_at DESC LIMIT 1 高效获取不推荐用触发器自动写日志——MySQL 触发器不可跨库、调试困难且事务内失败会中断主流程应在应用层统一调用日志写入接口如果状态流转有严格规则如不能从 done 直接切回 todo校验逻辑必须放在应用层数据库只负责存不负责判项目与任务的树形结构慎用闭包表Closure Table优先用 parent_id 路径前缀项目下套任务、任务再拆子任务需要查「某项目下所有子孙任务」。有人直接上闭包表ancestor/descendant但对中小 PMS 来说维护成本高、写入开销大且多数场景只需查 2~3 层深度。 Shakespeare 一款人工智能文案软件能够创建几乎任何类型的文案。