Sherfiter's wiki for fun

第 6 章 · ER 模型与概念设计

数据库设计的第一步从来不是建表,是跟业务方聊天。上一章的范式负责「拆得对」,但「该有哪些表」这个问题,范式一个字都帮不上:它只验收,不创作。创作阶段的主流工具就是 ER 模型(实体-联系图):先用图形把「有哪些东西、它们怎么关联」画清楚,谈的对象是业务里的名词和动词,用户能看懂、能纠正,确认之后再机械地翻成关系模式。本章就走一遍完整流程,拿一个真实的课程设计需求(图书借阅系统)从需求文档一路做到建表语句。

6.1 从需求到 ER 图:图书借阅系统

需求文档(业务方原话,故意保持含糊):「读者可以借书,一本书可以被很多人借过;每次借阅要记谁借的、什么时候、还了没;图书有多个副本,同书名的书馆里可能有 10 本;读者分学生和教师,借阅上限不同。」

从这段话里挖 ER 的三样东西:

  • 实体集(名词,独立存在的东西):读者、图书、副本。注意「副本」是从「一本书可以被很多人借过」和「有 10 本」里抠出来的,同一书名对应多个物理副本,借的是副本,这是需求里最容易被漏掉的实体。
  • 联系(动词,连接两个实体):「借阅」连接读者和副本,「属于」连接副本和图书。
  • 属性(描述,挂到实体或联系上):读者有编号、姓名、类型;图书有书号、书名;副本有(书号 + 副本号)联合标识;而「借出日期、归还日期」既不是读者的属性也不是副本的属性,它属于借阅这个联系。

画成 ER 图(教材符号:矩形实体、菱形联系、椭圆属性,下划线标码属性):

  读者 ──────◇借阅◇────── 副本 ──────◇属于◇────── 图书
(读者号)   借出日/归还日   (书号+副本号)             (书号,书名)
 姓名                          状态
 类型

两个画图心得,都是被退回重画换来的。其一,联系是动作不是名词:「读者拥有借书卡」如果画成「借书卡」实体,后面会发现借书卡和读者 1:1 且属性完全冗余,基本是画蛇添足。其二,属性挂错地方最常见:「借出日期」挂到副本上,副本的历史借阅记录就没地方放了,挂到联系上才对,判断标准是「这个描述描述的是一次关联,还是实体本身」。

6.2 基数:1:1、1:N、M:N 的判断与后果

联系两端的数量关系,决定翻表时怎么处理:

  • 1:1:一个班一个班长。通常把一端的主码外码到另一端即可,甚至直接合并成一张表。
  • 1:N:一个副本属于一本书(同一本书 10 个副本,每个副本只属于一本书)。把「1」端的主码(书号)塞进「N」端(副本)当外码。
  • M:N:读者和副本,一个读者借过多个副本,一个副本被多个读者借过。必须单独建联系表,两端主码拼成组合主码。

判断时拿两个方向各问一遍:「一个读者能借几个副本?多个。一个副本能被几个读者借过?多个。」两问都是多个,M:N。这种双向提问的习惯能挡掉八成设计错误,特别是在「1:N 还是 M:N」摇摆的场合(比如「部门-员工」要不要支持一人多部门,业务答完再定,别替业务拍板)。

6.3 ER 翻译成关系模式:四条机械规则

确认 ER 图后,翻译是纯体力活,规则四条:

  1. 每个实体集一张表,属性即列,码即主键。
  2. 1:1 联系:任选一端,把另一端主码当外码并入(联系属性跟着走)。
  3. 1:N 联系:「1」端主码并入「N」端当外码。
  4. M:N 联系:单独建联系表,两端主码组合成主码,联系属性(借出日期等)作普通列。

图书系统落出来就是:

CREATE TABLE reader (
  rid      TEXT PRIMARY KEY,
  rname    TEXT NOT NULL,
  rtype    TEXT CHECK (rtype IN ('学生','教师'))
);
CREATE TABLE book (
  bid      TEXT PRIMARY KEY,
  btitle   TEXT NOT NULL
);
CREATE TABLE copy (
  bid      TEXT,
  copy_no  INTEGER,
  status   TEXT DEFAULT '在馆',
  PRIMARY KEY (bid, copy_no),
  FOREIGN KEY (bid) REFERENCES book(bid)
);
CREATE TABLE borrow (
  rid      TEXT,
  bid      TEXT,
  copy_no  INTEGER,
  out_date DATE NOT NULL,
  in_date  DATE,
  PRIMARY KEY (rid, bid, copy_no, out_date),  -- 同一读者同一副本可多次借,加日期
  FOREIGN KEY (rid) REFERENCES reader(rid),
  FOREIGN KEY (bid, copy_no) REFERENCES copy(bid, copy_no)
);

翻完再用第 5 章的范式逐表验收一遍(本例各表已无部分/传递依赖),合格才进第 9 章的物理设计(索引、分区)。这条流水线,概念(ER)→ 逻辑(关系模式 + 范式)→ 物理(索引存储),正是第 1 章三级模式思想在设计流程上的展开。

6.4 弱实体:依附别人的实体

副本其实是个「弱实体」:它自己不足以被识别,「1 号副本」只有加上「哪本书的」才有意义。教材用双矩形表示,规则是:弱实体的主码 = 自己的区分号 + 所属实体的主码(copy 表的主码 (bid, copy_no) 正是这个套路)。现实里到处是弱实体:订单项(第几项 + 属于哪个订单)、章节(第几章 + 属于哪本书)。识别出弱实体,主码自然就对了,不会犯「给副本发全局唯一 ID」这种过度设计。

常见错误

  • 把联系画成实体。症状:某「实体」只包含两个外码没有任何自己的属性,九成该还原成联系(或联系表)。
  • M:N 直接在两张表里互相存外码。学生表存课程号列表、课程表存学生号列表,双冗余且没法表达联系属性(成绩放哪?)。M:N 的唯一正解是中间表。
  • 属性挂错层级。「借出日期」挂副本上,历史借阅记录直接没地方存。挂属性前问一句:它描述实体,还是描述一次关联?

练习

  1. 设计:给「学生社团管理系统」画 ER 图(学生、社团、活动,学生可入多个社团,社团组织活动,活动有签到)。标出基数,指出哪些是弱实体。
  2. 翻译:把第 1 题的 ER 图按四条规则翻成关系模式,写出 CREATE TABLE。
  3. 判断:「订单-商品」是 M:N 吗?订单行(某订单里的某商品及其数量、单价)说明什么?联系属性有哪些?
  4. 思考:需求说「一本书可以被很多人借过」,为什么实体清单里冒出来的是「副本」而不是直接「读者-图书」?如果删掉副本实体直接让读者和图书 M:N,会丢掉哪些现实约束?(提示:馆里 10 本同书,借出的是其中 1 本。)
豫ICP备2026027944号