数据库设计概述

复习定位

数据库设计不是拿到需求直接CREATE TABLE。设计过程分为六个阶段——需求分析、概念设计(ER图)、逻辑设计(ER转关系模型)、物理设计、实现实施、运行维护。考试中主要是需求分析→ER图→关系模型的转换过程——特别是ER图向关系模型的转换规则(实体变表、联系变外键或关联表)。

数据库设计步骤

需求分析——了解用户需要什么数据、数据的业务规则、数据之间的约束关系——输出需求说明书、数据字典(各类数据的数据项定义)——输出ER图的基础。

概念结构设计——用ER(entity-relationship)图建立独立于任何数据库管理系统的概念模型。实体用矩形框表示、属性用椭圆、联系用菱形。E-R图描述组织的数据需求——不涉及任何DBMS的具体操作。它在计算机语言环境中独立于任何数据库系统——当从Oracle换到PostgreSQL时——概念设计不需要改变重新开发。

逻辑结构设计——将概念结构(E-R图)转换成目标数据库管理系统支持的关系模式(表)。转换规则：每个实体转换为一张表——实体的属性转换为表的列——实体的标识符(码)转换为表的主键。联系转换规则在下一节详述。

物理设计——确定数据的存储结构(哪些表建立索引、索引类型)、文件组织方式(顺序/散列/B+树)、确定存储位置(表空间)、确定系统配置参数。

E-R图到关系模式的转换

每个实体→一张表。表的列=实体的属性。实体标识符=表的主键。

1:1联系——可以在实体的任一侧添加外键——引用另一实体的主键。

1:N联系——在N侧实体(多端)对应的表中加入外键——引用1端主键。

M:N联系——创建一张独立的关系表(联系表)——包含双方实体的主键作为外键——以及联系本身的属性(如成绩、时间)。该联系表的组合主键通常是双方外键的组合。

三个或以上的实体之间的多元联系——也生成一张独立的关系表——外键多个(参加联系的各实体主键的组合作为复合主键)。

实际设计的规范性

从E-R图转换得到的关系模型通常还需要经过范式(NF)的检查——判断是否满足3NF或BCNF——以消除数据冗余和插入删除更新异常——必要时进行分解——但分解可能降低查询效率(多表JOIN需要比单表更多的I/O)——因此要权衡范式化和反范式化。

复习检查

1. 数据库设计的六个阶段中——哪几个阶段与具体DBMS无关——哪几个阶段依赖数据库产品？ER图在哪个阶段被产出？

2. 将下列E-R图转换为关系模式：一个学生可选多门课程、一门课程可被多个学生选修且选修后记录成绩——画出这个关系的ER图(简图)——然后转换为关系模式。

3. 1:1联系——将外键添加到实体1还是实体2有区别吗——考虑访问频率——为什么将外键放到经常需要被访问的实体侧？

4. 概念设计的独立性体现在哪里——为什么数据库设计者要在概念设计中理清需求、画好E_R图——而不是直接上手CREAT TABLE？

5. 为什么M:N联系不能像1:N那样只在一个实体表中加外键表示——而必须引入一张单独的关联表？

数据库设计的六个阶段详解

需求分析阶段——与用户沟通——了解用户需要存储哪些数据(如学生信息、课程信息、选课成绩)、数据有什么业务约束(同一时间同一教室不能排两个班、学生年龄需在合理范围内)、数据之间有什么关联关系(选课依赖于学生和课程的存在)。输出——需求说明书、数据字典(定义每个数据项的名称、类型、长度、取值范围等)。需求分析是数据库设计过程的基础——如果需求搞错了——后续所有设计都基于错误的前提。

概念结构设计阶段——将需求分析结果转化为独立于任何DBMS的概念模型(E-R图)。此阶段不涉及具体的技术实现——不管将来用MySQL还是Oracle——概念层面的数据间关联关系不变。实体用矩形表示——属性用椭圆表示——联系用菱形表示。E-R图的深度不同版本包括基本E-R和扩展E-R——扩展E-R增加了分类、聚合、特殊化/泛化等语义表达——对复杂的现实世界模型更能准确描述。

逻辑结构设计阶段——将E-R图转换为关系数据库管理系统支持的关系模式(表)。实体转换为表——属性转换为表的列——码(主键)转换为表的主键约束。联系的转换遵循上一节所述的规则(1:1、1:N、M:N分别以外键加在任一侧、加在N侧、或新建关联表的方式处理)。

物理设计阶段——确定数据库在物理存储上的实现方式：选择索引类型(B+树还是哈希)、确定存储引擎(InnoDB vs MyISAM)、表的分区策略(按时间或按ID范围分区)、磁盘上文件的分布和放置、配置缓冲池大小等参数。此阶段与具体的DBMS产品密切相关——例如MySQL InnoDB使用聚簇索引而Oracle在Oracle Database中直接存储行数据是不同的物理实现。

数据库实施阶段——在选定的DBMS上执行DDL语句(CREATE TABLE等)创建数据库对象——同时编写存储过程、触发器和应用程序接口——插入测试数据进行验证。

运行和维护阶段——数据库上线后——根据运行情况和新的需求变更——进行索引优化、性能调优、数据备份与恢复策略的执行——并可能根据需求变化修改表结构。

E-R图向关系模式的转换详细规则

1:1联系的转换——在两张实体表中——选择任意一张表添加另一张表的主键作为外键——并在外键上建立唯一性约束。外键加在哪里需要考虑访问频率——如果实体A经常需要查询关联的实体B的信息——将外键加在A表可以减少JOIN操作。

1:N联系的转换——在N端(多的那端)的表中添加1端的主键作为外键。例如一个系(1)有多个学生(N)——在学生表中添加系别ID作为外键。系表中的一条记录可以在学生表中关联多条记录——这是1:N的典型。

M:N联系的转换——创建一张独立的关联表(也称为联系表或连接表)——包含两个实体的主键作为外键——它们的组合作为关联表的复合主键——同时可以记录联系本身的属性(如选课的成绩、时间)。关联表在逻辑结构设计阶段一定要识别清晰——避免将多对多误认为一对多而丢失数据。

多元联系的转换(三个以上实体的联系)——同样创建独立的关联表——其主键是所有参与实体的主键的组合。

数据库设计说明书的内容

实际的产品级别的数据库设计会输出一个数据库设计说明书——在其中详细列出以下内容：

1. 需求分析结果——数据字典——每个表的业务含义和用户期望的阈值
2. E-R图——实体关系模型
3. 关系模式列表——每个表的字段名、类型、约束、默认值
4. 索引设计——主键/唯一/复合索引——覆盖索引的定义策略
5. 存储过程/触发器的功能需求和解析
6. 分库分表的策略——当数据超过单个表处理上限时的扩展路线

对于开发数据库应用的新手或中大规模系统中的后续维护方来说——数据库设计说明书是非常关键的参考文档——避免因人员交接带来后续维护的不一致。

复习检查（续）

6. 数据库设计的六个阶段中——需求分析和概念设计(ER图)与具体DBMS无关——逻辑、物理设计和后面阶段依赖数据库产品。

7. 为什么M:N联系不能简单在其中一个实体表中加外键——因为M:N中一个实体的一条记录对应另一个实体的多条记录——并且反之亦然——在单表加外键无法在不产生冗余的情况下表示这种双向多对多关系——必须用独立的关联表。

8. 1:N联系中外键加在N端(多端)的原因——避免冗余——如果外键加在1端——一条1端记录关联N个N端记录——1端表中每个外键只能存一个值——需要用多行存储——造成数据冗余和插入/删除异常。

9. 概念结构设计独立于DBMS的意义——当业务需要从MySQL迁移到PostgreSQL时——概念模型(E-R图)不需要改变——只需修改逻辑/物理设计的DDL语句即可。

10. 数据库设计说明书的主要输出内容——数据字典、E-R图、关系模式定义、索引设计、存储过程定义和分库分表策略。

E-R图的扩展概念——弱实体、复合属性、多值属性

弱实体——一个实体的存在依赖于另一个实体——例如"家属"实体依赖于"员工"实体——没有员工就不能有家属。弱实体在E-R图中用双矩形表示——其主键由依赖实体的主键和弱实体本身的标识符组合而成。

复合属性——可以进一步拆分的属性——例如"地址"可以拆分为"省/市/街道/邮编"——在E-R图中用属性下面按层次连接子属性的方式表示。

多值属性——一个实体可以有多个值的属性——例如一个员工的多个电话号码——在E-R图中用双椭圆表示。多值属性在转换关系模式时需要创建一张单独的表来存放——以员工电话为例——需要另外创建员工电话表(employee_id, phone_type, phone_number)。

实体标签图转化为关系模式的完整示例

需求: 学生选课系统——学生(学号,姓名,系别)——课程(课程号,课程名,学分)
学生 与 课程 的关系：一个学生可选多门课程——一门课程可被多个学生选修——选课后有成绩

E-R图:
[学生] --(选修)--> [课程]
 |                   |
学号(主键)          课程号(主键)
姓名                课程名
系别                学分
     成绩(选课联系的属性)

关系模式转换:
学生(学号,姓名,系别) —— 主键: 学号
课程(课程号,课程名,学分) —— 主键: 课程号
选课(学号,课程号,成绩) —— 主键: (学号,课程号)
    外键: 学号→学生(学号), 课程号→课程(课程号)

选课关系表中的学号和课程号组合唯一确定了某位学生选修某门课程的成绩——这就是多对多联系的标准转换方式。

物理设计的主要工作内容

物理设计将逻辑表结构转化为物理存储结构——关键决策包含：

1. 索引的选择——在频繁作为查询条件的列上创建索引(B+树/哈希/全文)
2. 存储引擎的选择——MySQL InnoDB支持事务但空间更大——MyISAM不支持事务但读更快(8.0后InnoDB已是默认)
3. 表分区——将一个大表水平分割为多个物理分区——提高查询和管理效率——特别是按时间范围分区的日志表管理非常便利
4. 缓存参数——数据库缓存(如InnoDB buffer pool)建议设为物理机可用内存的70-80%

物理设计阶段的决策直接影响数据库的性能——需要根据数据的读写比例、查询模式(点查vs范围查vs全表扫描)、数据量和增长趋势来综合决定。

复习检查（续二）

11. 弱实体在转换为关系模式时的标准处理方式——弱实体的主键由依赖实体的主键加自己的标识符组合——例如家属表的主键为(员工ID,家属姓名)。

12. 多值属性转换为关系模式时——需要创建一张单独的表来存放——例如员工电话表(员工ID,电话类型,电话号码)——主键为(员工ID,电话类型)——确保一个员工不会录入两个同类型的电话号码。

13. E-R图中复合属性的例子——"地址"拆分为"省/市/街道/邮编"——在转换时所有子属性成为表的独立字段。

14. 物理设计阶段的主要选择——索引类型、存储引擎、表分区策略——这些选择直接影响数据库的查询性能和存储效率。

15. 表分区(partitioning)在物理设计中的应用——按时间范围(例如订单按月份分区)可以减少查询的扫描数据量——便于冷热数据分离——老分区的数据可直接删除(drop partition)而不是逐行delete。

数据库反范式化的工程考虑

在需求分析和物理设计的交界处——有时会发现严格按范式设计会导致过多的表关联(JOIN)——在查询性能要求很高(如首页大屏展示毫秒级加载)时——通过反范式化——在表中适当增加冗余列——减少查询的JOIN次数——换取查询速度的优化可能是一种工程上的可行性选择。

反范式化的常见做法：在订单表中冗余存储客户姓名和地址(而不需要每次都JOIN客户表)；
定期交易明细系统的物化视图(预先JOIN并存储结果表用于快照查询)；
在用户表中冗余存储"最后登录时间"——以加速用户列表显示接口——不再需要JOIN日志表。

反范式化的代价：冗余数据可能导致更新异常(修改客户姓名时必须同时更新所有订单中的冗余姓名列)——所以必须通过应用逻辑或数据库触发器保证冗余列的一致性。反范式化不应该是一开始就做的——应该在系统性能瓶颈明确是由于过多JOIN引起时——在必要的目标上做有针对性地反范式化——而不是全盘放弃范式设计。

复习检查（续三）

16. 反范式化的适用场景——查询性能瓶颈明确由过多表JOIN引起时——在订单表中冗余存储高频查询字段——但必须用触发器或应用逻辑保证冗余数据的一致性。

17. 订单表中冗余客户姓名和地址的反范式化处理——客户姓名改变时需要更新订单表中的所有冗余字段——否则出现不一致——这是反范式化的典型代价。

18. 数据库的物理设计中索引使用的基本原则(应该写在开发规范中的)——在WHERE条件列和JOIN条件列上建索引——在高频SELECT的列上可以考虑覆盖索引——避免在大表的低选择性列(如性别)上建单列索引。

19. E-R图在需求变化频繁时的开发管理策略——E-R图的迭代应与需求同步——当需求变化涉及实体或联系变化时——必须更新E-R图并由此重新生成关系模式——直接修改表而不更新E-R图会使后期文档与设计脱节。

20. 分库分表(sharding)在数据库设计中的定位——当单表数据过亿时——即使索引优化也难以满足性能需求——水平切分将不同的数据划分到不同的物理库/表中——减少单表的数据规模——但同时也增加了跨库跨表查询的复杂性——通常只在数据量达到数十TB时才最优先考虑——在此之前应该先测试索引优化、读写分离等更轻量级的手段是否满足业务目标。

数据库设计不同阶段的输出汇总

需求分析 → 数据字典、数据流图、需求规格说明书
概念设计 → E-R图(实体关系图) - 独立于DBMS
逻辑设计 → 关系模式集合(各表的字段/约束/索引定义)
物理设计 → 存储引擎、表空间、缓存参数、索引详细定义
实施     → DDL脚本、存储过程代码、单元测试、数据初始化
维护     → 备份恢复策略、性能监控优化日志、结构变更记录

数据库设计不是"一蹴而就"的过程——随着业务的增长和需求的变更——数据库结构需要不断地演进(增加新表、增加索引、调整分库分区策略)——在整个软件生命周期持续进行增量式设计调整。数据库的初始设计的稳定性和扩展性——直接决定了业务在快速发展阶段的数据库改造难度和成本。

复习检查（续四）