3. 数据建模与表设计

数据建模不是画几张 ER 图就结束，而是把业务语言翻译成数据库可约束、可查询、可演进的结构。以“员工—部门—项目—工时”这一常见业务为例：业务方说“一个部门有多名员工，一名员工只属于一个部门；一个项目可有多名员工参与，一名员工也可参与多个项目；员工每天为某个项目填报工时”。这段话里已经隐含了实体、属性和基数，建模的任务就是把它显式化。

一、从需求到实体关系模型

先做名词提取：部门、员工、项目、工时记录。再判断哪些是实体、哪些是关系。部门、员工、项目是独立实体；工时记录不是简单关系，因为它带有“日期、工时数、工作说明”等属性，应升级为独立实体（关联实体）。接着确定主键与基数：部门与员工是 1:N，员工与项目是 M:N，而工时记录把 M:N 拆成两条 1:N，并额外承载业务属性。

这一步的关键判断是：带属性的多对多关系必须实体化。如果只建 employee_project 关联表而不放日期和工时，就无法表达“同一员工同一项目在不同日期填不同工时”。因此工时表的主键通常采用 (employee_id, project_id, work_date) 组合，或使用代理主键加唯一约束。

员工—部门—项目—工时 实体关系模型 部门 Department PK dept_id dept_name location 员工 Employee PK emp_id emp_name hire_date FK dept_id 项目 Project PK project_id project_name status 工时 Timesheet PK ts_id FK emp_id FK project_id work_date / hours 1 : N M : N 1 : N 1 : N M:N 关系通过 Timesheet 实体化，并承载 work_date、hours 等业务属性

二、规范化与反规范化的权衡

得到 ER 模型后，需要按范式检查结构。第一范式要求字段原子，例如不要把“张三,李四”塞进一个字段；第二范式要求非主属性完全依赖主键，工时表中 hours 依赖 (emp_id, project_id, work_date) 整体，符合 2NF；第三范式要求消除传递依赖，员工表中 dept_name 不应出现，因为 dept_name 依赖 dept_id 而非 emp_id，应放在部门表。

但规范化到 3NF 后，查询“员工姓名 + 部门名称 + 项目名称 + 工时”需要四表连接。在高频报表场景下，连接成本可能成为瓶颈。此时可考虑反规范化：在工时表冗余 dept_id，或建立汇总表 monthly_hours(emp_id, project_id, month, total_hours)。反规范化的判断依据是读多写少、连接代价高、且冗余字段的更新频率可控。如果部门会频繁调整，冗余 dept_name 就会带来一致性风险，应改用视图或物化视图。

权衡原则：OLTP 核心交易表优先保持 3NF，保证写入一致性和更新简单；OLAP 报表或高频查询可适度反规范化，但必须明确冗余字段的同步方式（触发器、定时任务或应用层双写）。

三、DDL 编写：约束即文档

DDL 不只是建表，更是把业务规则写进数据库。以下示例基于 MySQL 8.0，展示核心表的建表语句。注意主键、外键、唯一约束、非空和检查约束的配合使用。

CREATE TABLE department (
  dept_id     INT PRIMARY KEY AUTO_INCREMENT,
  dept_name   VARCHAR(64) NOT NULL UNIQUE,
  location    VARCHAR(128)
) ENGINE=InnoDB;

CREATE TABLE employee (
  emp_id      INT PRIMARY KEY AUTO_INCREMENT,
  emp_name    VARCHAR(64) NOT NULL,
  hire_date   DATE NOT NULL,
  dept_id     INT NOT NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
    REFERENCES department(dept_id)
) ENGINE=InnoDB;

CREATE TABLE project (
  project_id   INT PRIMARY KEY AUTO_INCREMENT,
  project_name VARCHAR(128) NOT NULL,
  status       VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
  CONSTRAINT chk_project_status
    CHECK (status IN ('ACTIVE','CLOSED','PAUSED'))
) ENGINE=InnoDB;

CREATE TABLE timesheet (
  ts_id       BIGINT PRIMARY KEY AUTO_INCREMENT,
  emp_id      INT NOT NULL,
  project_id  INT NOT NULL,
  work_date   DATE NOT NULL,
  hours       DECIMAL(4,1) NOT NULL,
  CONSTRAINT fk_ts_emp FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
  CONSTRAINT fk_ts_proj FOREIGN KEY (project_id) REFERENCES project(project_id),
  CONSTRAINT uq_ts UNIQUE (emp_id, project_id, work_date),
  CONSTRAINT chk_hours CHECK (hours > 0 AND hours <= 24)
) ENGINE=InnoDB;

这里有几个评审要点：uq_ts 防止同一员工同一天对同一项目重复填报；chk_hours 把“工时必须在 0 到 24 之间”的业务规则下沉到数据库；fk_ts_emp 保证工时不会指向不存在的员工。如果业务允许员工跨部门借调，则 employee.dept_id 可能需要改为多对多关系，这属于模型演进，应在评审时提出。

四、DDL 评审清单

评审不是走过场，而是用固定维度逐项检查。以下清单可直接用于团队评审：

评审维度检查项示例判断
主键是否有稳定主键，是否使用自增或业务键timesheet 用 ts_id 代理键，同时保留唯一约束
外键引用关系是否完整，删除策略是否明确员工离职时工时记录应保留，外键用 RESTRICT
约束非空、唯一、检查约束是否覆盖业务规则hours 必须大于 0 且不超过 24
范式是否存在传递依赖或部分依赖员工表不应出现 dept_name
反规范化冗余字段是否有同步方案和一致性验证汇总表需每日凌晨重算并比对源表
索引高频查询条件是否有索引支撑timesheet(emp_id, work_date) 支持按人按月查询
命名表名、字段名、约束名是否统一规范外键统一 fk_表_引用表，唯一约束 uq_表_字段

评审时还应关注变更成本：如果项目已上线，新增非空列需要默认值或分步迁移；修改主键几乎不可行，应在设计阶段避免。对于工时表，若未来要支持“一个员工一天为多个项目填报”，当前唯一约束 (emp_id, project_id, work_date) 已经允许，因为不同 project_id 不冲突；但若要限制“一天总工时不超过 24”，则需要应用层或触发器汇总校验，单行 CHECK 无法完成跨行约束。

常见误区：为了“查询方便”在员工表冗余 dept_name，却没有同步机制，导致部门改名后员工表仍显示旧名称。反规范化必须配套同步方案和定期一致性校验，否则宁可多一次 JOIN。

最后，把模型和 DDL 纳入版本管理，每次变更通过迁移脚本执行，并在评审记录中保留决策理由。这样，数据模型才能随着业务演进而可控地生长，而不是在后期变成难以维护的“大泥球”。

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