从业务场景实战出发：用3个案例彻底掌握E-R图与3NF设计
每次看到数据库教材里那些抽象的理论和符号，你是不是也感到头疼？E-R图、范式、实体关系...这些概念单独看都懂，但一到实际项目就无从下手。今天我们不谈枯燥的定义，直接通过选课系统、医院管理系统和超市购物系统这三个真实场景，带你一步步完成从需求分析到3NF设计的全过程。
1. 选课系统：多对多关系的经典案例
大学选课系统是理解数据库设计的绝佳起点。想象一下，每学期初学生们蜂拥而至抢课的场景——这个系统需要记录学生信息、课程详情以及谁选了哪门课。让我们拆解这个业务：
核心实体分析：
- 学生：学号（主键）、姓名、所属班级
- 课程：课程编号（主键）、名称、学分
- 班级：班级编号（主键）、专业名称
这里的关键在于识别"选课"这个行为产生的数据。成绩既不属于学生实体（因为同一学生不同课程成绩不同），也不属于课程实体（不同学生上同一门课成绩不同），它只存在于学生和课程发生关联时。
E-R图要点：
- 学生与课程之间是多对多关系，需要用菱形表示"选课"联系
- "成绩"应作为联系属性而非实体属性
- 班级与学生是一对多关系（一个班级包含多个学生）
-- 最终3NF关系模型 CREATE TABLE 班级 ( 班级编号 CHAR(6) PRIMARY KEY, 专业名称 VARCHAR(50) NOT NULL ); CREATE TABLE 学生 ( 学号 CHAR(10) PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 班级编号 CHAR(6), FOREIGN KEY (班级编号) REFERENCES 班级(班级编号) ); CREATE TABLE 课程 ( 课程编号 CHAR(8) PRIMARY KEY, 课程名称 VARCHAR(50) NOT NULL, 学分 TINYINT CHECK (学分 > 0) ); CREATE TABLE 选课 ( 学号 CHAR(10), 课程编号 CHAR(8), 成绩 DECIMAL(5,2), PRIMARY KEY (学号, 课程编号), FOREIGN KEY (学号) REFERENCES 学生(学号), FOREIGN KEY (课程编号) REFERENCES 课程(课程编号) );注意：常见错误是把"学分"放在选课表中。实际上学分是课程的固有属性，与是否选课无关，必须放在课程表中避免冗余。
2. 医院管理系统：复杂业务关系的处理
医院场景比选课系统更复杂，涉及科室、医生、病房、病人等多维关系。我们先理清业务规则：
- 一个科室有多个病房和医生
- 一个医生只属于一个科室
- 一个病房只属于一个科室
- 一个病人有唯一主管医生
- 一个医生可管理多个病人
设计过程中的关键决策点：
科室电话归属问题：
- 错误做法：放在医生表中（导致数据冗余）
- 正确做法：作为科室属性（符合3NF）
病房床位设计：
- 每张病床应有唯一标识（病房号+床位号组合键）
- 病房与科室是一对多关系
医生与病人的关系：
- "主管医生"是病人实体的外键
- 不需要单独的"诊治"联系表（因为是一对多关系）
-- 医院系统的3NF实现 CREATE TABLE 科室 ( 科名 VARCHAR(20) PRIMARY KEY, 科地址 VARCHAR(100), 科电话 VARCHAR(20) ); CREATE TABLE 病房 ( 病房号 VARCHAR(10), 床位号 VARCHAR(5), 科名 VARCHAR(20), PRIMARY KEY (病房号, 床位号), FOREIGN KEY (科名) REFERENCES 科室(科名) ); CREATE TABLE 医生 ( 工号 CHAR(8) PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 职称 VARCHAR(10), 年龄 TINYINT, 科名 VARCHAR(20), FOREIGN KEY (科名) REFERENCES 科室(科名) ); CREATE TABLE 病人 ( 病历号 CHAR(12) PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(1), 诊断 TEXT, 主管医生 CHAR(8), 病房号 VARCHAR(10), 床位号 VARCHAR(5), FOREIGN KEY (主管医生) REFERENCES 医生(工号), FOREIGN KEY (病房号, 床位号) REFERENCES 病房(病房号, 床位号) );提示：在实际项目中，病房管理系统可能还需要考虑床位状态（空/满）、病人入院时间等扩展属性，这些都应该作为病人表的属性而非病房表的属性。
3. 超市购物系统：处理交易流水数据
超市收银小票看似简单，却包含了典型的交易场景所有要素：商品、收银员、交易流水、商品明细。分析业务特征：
一对多与多对多关系并存：
- 一个购物单对应多个商品（一对多）
- 但商品也会出现在多个购物单中（多对多）
派生属性的处理：
- 小票上的"金额"是单价×数量计算得出
- 总金额是所有商品金额的汇总
设计中的典型错误与修正：
表1：常见错误设计 vs 正确方案
| 错误设计 | 问题分析 | 正确方案 |
|---|---|---|
| 把商品名称直接放在销售表中 | 违反1NF（存在重复数据） | 商品名称只存在于商品表 |
| 在购物单表存储所有商品明细 | 违反2NF（部分依赖） | 用单独的销售表记录每件商品 |
| 将总金额作为商品表的属性 | 违反3NF（传递依赖） | 总金额应属于购物单或实时计算 |
-- 购物系统的3NF模型 CREATE TABLE 商品 ( 商品编号 CHAR(8) PRIMARY KEY, 商品名称 VARCHAR(50) NOT NULL, 单价 DECIMAL(10,2) CHECK (单价 > 0) ); CREATE TABLE 收银员 ( 工号 CHAR(6) PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL ); CREATE TABLE 购物单 ( 单据编号 CHAR(14) PRIMARY KEY, 交易时间 DATETIME DEFAULT CURRENT_TIMESTAMP, 收银员工号 CHAR(6), 总金额 DECIMAL(12,2), FOREIGN KEY (收银员工号) REFERENCES 收银员(工号) ); CREATE TABLE 销售明细 ( 单据编号 CHAR(14), 商品编号 CHAR(8), 数量 SMALLINT CHECK (数量 > 0), 单价 DECIMAL(10,2), PRIMARY KEY (单据编号, 商品编号), FOREIGN KEY (单据编号) REFERENCES 购物单(单据编号), FOREIGN KEY (商品编号) REFERENCES 商品(商品编号) );性能与规范的权衡：
- 虽然理论上总金额可以通过明细计算得出，但实际系统中通常会冗余存储（避免每次查询都要聚合计算）
- 这种情况下需要建立触发器确保数据一致性：
CREATE TRIGGER 更新总金额 AFTER INSERT ON 销售明细 FOR EACH ROW UPDATE 购物单 SET 总金额 = ( SELECT SUM(单价*数量) FROM 销售明细 WHERE 单据编号 = NEW.单据编号 ) WHERE 单据编号 = NEW.单据编号;4. 从E-R图到3NF的实战方法论
通过以上三个案例，我们可以总结出一套可复用的设计流程：
步骤一：业务需求分析
- 列出所有业务单据和报表
- 提取其中的数据项作为候选属性
- 识别明显的实体（名词）和关系（动词）
步骤二：绘制E-R图
- 用矩形表示实体
- 用菱形表示重要业务关系
- 确定基数约束（1:1、1:n、m:n）
- 将属性分配到最合适的实体或关系
步骤三：转换为关系模型
- 每个实体转为一张表
- 1:n关系通过外键实现
- m:n关系需要单独建表
- 检查所有属性是否都依赖于完整的主键
步骤四：规范化到3NF
- 消除重复组（达到1NF）
- 消除部分依赖（达到2NF）
- 消除传递依赖（达到3NF）
- 必要时进行反规范化（有明确理由）
表2：常见设计问题自查表
| 问题类型 | 检查方法 | 解决方案 |
|---|---|---|
| 违反1NF | 表中是否有重复列或多值字段？ | 拆分为多行或多表 |
| 违反2NF | 非主属性是否依赖部分主键？ | 拆分出新的实体 |
| 违反3NF | 非主属性是否依赖其他非主属性？ | 移除传递依赖 |
| 冗余数据 | 相同信息是否在多个地方存储？ | 建立外键关联 |
最后记住，好的数据库设计不是一蹴而就的。在实际项目中，我通常会先画出初版E-R图，然后与业务人员确认关键关系，接着进行至少三轮的范式检查和调整。有时候适当的冗余（如订单总金额）反而能提升性能，关键是要有明确的理由和保障一致性的机制。
