第19节 从业务需求设计数据库
前面亲手用过一张表和多张表,现在回头学设计,才容易理解“为什么拆表、外键为什么放那里”。本节对应考纲的数据库设计,不要求照着某套试题背表名。
1 从需求中找实体、属性和联系
业务说:“图书归一个分类,一种图书可多次卖出,每次记录数量与时间。”
- 实体是独立关注的对象:图书、分类、销售记录。
- 属性是描述它的信息:书名、单价、分类名、销售数量。
- 联系是对象之间的关系:图书属于分类、销售记录对应图书。
先把这些说清楚,再选择字段和类型,而不是一开始就写CREATE TABLE。
2 E-R图表达关系
%% 一种分类可有0到多本书;Book.CategoryId可空,所以图书可暂不对应分类 %% 一种图书可有0到多条销售;每条Sale的BookId非空,必须对应一本现有图书 erDiagram Category o|--o{ Book : classifies Book ||--o{ Sale : sold_in Category { int CategoryId PK nvarchar CategoryName } Book { int BookId PK nvarchar Title decimal Price int CategoryId FK } Sale { int SaleId PK int BookId FK int Quantity datetime SaleDate }
矩形/实体块代表实体,字段对应属性;连线端点表达一和多。这里是Mermaid源码,Obsidian显示成图,不是复制到SSMS执行的SQL。
一对多的外键通常放“多”的一侧。多对多要增加中间表:例如读者可收藏多本书,一本书可被多位读者收藏,就建 Favorite(ReaderId,BookId)。一对一可用外键加UNIQUE表达;是否合表仍看业务。
3 一套完整设计流程
| 步骤 | 主要产物 | 自问 |
|---|---|---|
| 需求分析 | 数据与业务规则清单 | 一条销售只含一种书,还是一张订单含多种书? |
| 概念设计 | 实体、属性、联系、E-R图 | 哪些对象应该分开? |
| 逻辑设计 | 关系表、键、规范化 | 怎么转换成表和外键? |
| 物理设计 | 类型、索引、文件与存储 | 实际如何保存和访问? |
| 实施 | 建表、数据、程序与测试 | 是否能运行且满足规则? |
| 运行维护 | 权限、备份、调整 | 长期使用是否可靠? |
概念结构关心业务,逻辑结构关心表和依赖,物理结构关心存储。另可了解三级模式:外模式是用户局部视图,模式是整体逻辑结构,内模式是内部存储;映射帮助实现数据独立性。
4 键与函数依赖
候选键:能唯一确定一行的最小属性集合;从中选一个作主键。能唯一确定但未必最小的叫超键。
BookId → Title,Price,CategoryId表示编号确定一本书的其他信息;CategoryId → CategoryName表示分类编号确定分类名。这种“左边值确定后,右边也确定”的关系叫函数依赖。它由业务规则决定,不是只看当前几行碰巧没有重复。
5 为什么需要规范化
假设有借阅表 R(ReaderId,ReaderName,BookId,Title,CategoryId,CategoryName,BorrowTime),业务规定一名读者同一本书可以多次借,因此不能直接把(ReaderId,BookId)当作唯一键。
为单独说明部分依赖,换成“每位读者对每本书最多收藏一次”的收藏清单:
FavoriteAll(ReaderId,ReaderName,BookId,Title,CategoryId,CategoryName,AddedAt),候选键为 (ReaderId,BookId)。
| 范式 | 要求的直观理解 | 这个清单的问题 |
|---|---|---|
| 1NF | 每格一个原子值,不塞可重复列表 | 不能把多个BookId存成逗号字符串 |
| 2NF | 在1NF上,非主属性不部分依赖候选键 | ReaderName只依赖ReaderId,Title只依赖BookId |
| 3NF | 在2NF上,避免非主属性经其他非主属性传递依赖键 | BookId→CategoryId→CategoryName形成传递依赖 |
可以拆为Reader、Book、Category、Favorite四张表,AddedAt放Favorite,因为它描述“某人收藏某本书”这个关系,而不是读者或书本本身。
不拆时会出现:改分类名要改多行(更新异常);没收藏记录不能单独保存新读者(插入异常);删最后一条收藏顺带丢失图书信息(删除异常)。
以上是常见初学判断法;更严谨的3NF要求每个非平凡依赖X→A中,X是超键或A是主属性。加个自增主键不等于自动满足3NF,仍需看全部候选键和业务依赖。
6 非规范化为什么仍存在
为了具体的查询性能需求,有时有意保存冗余或汇总。例如把销量汇总存起来,读取快,但每次销售变化都要同步,增加不一致风险。先保证清晰正确,再针对实际性能取舍,不是遇到JOIN就把所有信息塞回一个大表。
本课程Sale是单种图书销售的教学简化,不保存历史成交价。真实销售系统应保存当时成交单价;多商品订单还需订单头和明细。不能拿当前Book.Price自动当成历史销售收入。
本节练习
设计“学生选课”,说出学生、课程、选课三种关系表及成绩应放哪里;若允许重修,为什么学号+课程号不一定唯一?
参考思路
学生(StudentId,Name),课程(CourseId,Title),选课(EnrollmentId,StudentId,CourseId,Term,Grade)。成绩属于一次选课,应放选课表。重修可能同人同课跨学期多次,要按业务增加学期/选课事件标识,不应擅自禁止。
上一节:18 查询结果的并交差 | 下一节:20 用视图保存常用查询 | 课程目录