mysql搭建项目管理系统数据库表设计与优化-mysql教程-PHP中文网

pms mysql表设计应聚焦“人、事、时、物”四类关系：用户与权限分离（user/role/user_role三表）、任务状态机拆分（task+task_status_log）、树形结构用parent_id+path替代闭包表、附件元数据与引用解耦（attachment+attachment_ref）。

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

项目管理系统（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'，看似简单，但无法追溯谁、何时、为什么改了状态，也难做「退回上一状态」或「跳过审批」等业务逻辑。

状态变更应视为独立事件记录：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

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 层深度。

更轻量的做法是：在 task 表加 parent_id 和 path 字段：

ALTER TABLE `task`
ADD COLUMN `parent_id` BIGINT UNSIGNED DEFAULT NULL,
ADD COLUMN `path` VARCHAR(512) NOT NULL DEFAULT '',
ADD INDEX `idx_path` (`path`);

path 存类似 '/1001/1005/1022/' 的格式（开头结尾都带斜杠），查某任务所有子任务用 WHERE path LIKE '/1005/%'
插入新子任务时，应用层拼好 path 再写入，避免数据库里用函数拼接（MySQL CONCAT 在 WHERE 中无法走索引）
路径长度限制 512 字符，足够支撑 20 层嵌套（每 ID 平均按 4 字符算），超深结构本身说明模型设计有问题，该拆服务或换领域模型

附件与关联数据：用 `attachment` 表 + `attachment_ref` 多对多解耦，别存物理路径或 Base64

把文件内容用 MEDIUMBLOB 存进数据库，或者把 Base64 字符串塞进 TEXT 字段，会导致备份膨胀、主库 I/O 压力飙升、无法利用 CDN。

正确方式是只存元信息，文件走对象存储：

CREATE TABLE `attachment` (
  `id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `original_name` VARCHAR(255) NOT NULL,
  `storage_key` VARCHAR(255) NOT NULL COMMENT 'OSS/MinIO 的 key',
  `size_bytes` BIGINT UNSIGNED NOT NULL,
  `mime_type` VARCHAR(100),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `attachment_ref` (
  `attachment_id` BIGINT UNSIGNED NOT NULL,
  `ref_type` VARCHAR(30) NOT NULL COMMENT 'task, project, comment',
  `ref_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`attachment_id`, `ref_type`, `ref_id`),
  INDEX `idx_ref` (`ref_type`, `ref_id`)
);