第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自动当成历史销售收入。

本节练习

设计“学生选课”,说出学生、课程、选课三种关系表及成绩应放哪里;若允许重修,为什么学号+课程号不一定唯一?


上一节:18 查询结果的并交差 | 下一节:20 用视图保存常用查询 | 课程目录