数据库设计规范

最近学习了数据库相关的知识，体会到了数据库设计的重要性，一个好的数据库设计能为我们后面奠定开发基础，就好比建筑一样，数据库设计就是其中的地基，地基打稳了，后面的工程才能顺利进行。如果数据库设计的不好，地基不稳，那么我们的系统可能会不太稳定，对维护和开发带来了麻烦。

数据库设计

简单来说，数据库设计就是根据业务系统的具体需要， 结合我们所选用的DBMS(Database Management System)，例如Oracle，MySQL，SQLserver等，为这个业务系统构造出最优的数据存储模型，并建立好数据库中的标结构及表与表之间的联系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。

一般来说，数据库设计分为以下四个步骤：

• 需求分析
  • 数据是什么
  • 数据有哪些属性
  • 数据和属性各自的特点有哪些
• 逻辑设计
  • 使用ER图对数据库进行逻辑建模
• 物理设计
  • 具体的代码设计
  • 根据所选数据库自身的特点把逻辑设计转换为物理设计
• 维护优化
  • 新的需求进行建表
  • 索引优化
  • 大表拆分

需求分析

首先拿到一个需求，首先要进行需求分析，而不是一开始就着手代码的编写和数据库的建立。我们需要了解系统中需要存储的数据是什么，这些数据有什么特点，比如有一些数据是具体时效性的，对于这些数据我们可以采用过期清理的方式，减少不必要的数据存储。了解数据的生命周期，比如有一些数据不是数据库的核心数据，就不适合存储在数据库中。例如，日志类的文件，日志这种文件增长量非常大，同时它也不是数据库的核心数据，一般不适合存储在数据库中。

另一方面，要弄清楚实体与实体之间的关系，是1对1,1对多，还是多对多？实体所包含的属性是什么，哪些属性或属性的组合可以唯一标识一个实体，也就是超码。

ER图

需求分析做完之后我们就可以进行数据库逻辑模型的设计，一般我们采用ER图的形式对逻辑模型进行展示。
ER图中的相关名词：

• 关系：一个关系对应通常所说的一张表
• 元组：表中的一行即为一个元组
• 属性：表中的一列即为一个属性，每个属性都有一个名称，称为属性名
• 候选码：表中的某个属性或属性组，它可以唯一确定一个元组
• 主码：一个关系有多个候选码，选定其中一个为主码
• 域：属性的取值范围
• 分量：元组中的一个属性

数据库设计范式

数据的设计要遵循一定的规范，这样出来的数据库才是简洁的，结构清晰的，能让开发人员一目了然，看到这个数据库就能大概知道它对应的功能。优良的数据库设计可以节约存储空间，减少数据冗余，避免数据维护异常(如数据插入，更新，删除的异常)，同时也能进行高效的访问，不给开发人员制造不必要的麻烦。
常见的数据库范式包括：

• 第一范式(1NF)
• 第二范式(2NF)
• 第三范式(3NF)
• BC范式(BCNF)
• 第四范式(4NF)
• 第五范式(5NF)

目前重点放在前四个范式上，这也是目前大多数数据库设计所要遵循的范式，一般要求数据库的设计至少要满足第三范式，更高的会要求满足BCNF。

第一范式

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。
简而言之，第一范式要求数据库中的表都是二维表。

满足第一范式

id	name	sex
1	water	male

第二范式

首先第二范式肯定是要满足第一范式的(废话)。第二范式的定义是：

数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖

部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况

换句话说：所有单关键字的标都符合第二范式

如下表：

学号	姓名	年龄	课程名称	成绩	学分
2015xxx	water	20	DBMS	90	3

上表的关键字为组合关键字(学号，课程名称)，存在以下决定关系：
（学号，课程名称）→ （姓名，年龄， 成绩，学分）

这个表不满足第二范式，因为存在如下决定关系（部分函数依赖）：
（课程名称）→ （学分）
（学号）→ （姓名，年龄）

即存在关键字中的字段决定非关键字的情况。

由于上表不满足第二范式，那它会存在以下问题：

• 插入异常
• 删除异常
• 更新异常
• 数据冗余

插入异常
假如现在要开设一门新的课程，这门课程还没有人选修。这样，由于还没有“学号”关键字，课程名称和学分也无法记录入数据库。

删除异常
假如一学期结束，一批学生已经完成课程的选修，这些选修记录应该从数据库表中删除。但是，与此同时，课程名和学分信息也被删除，很显然，这也会导致插入异常。

更新异常
若调整了某一门课程的学分，那么对应数据表中的所有“学分”值都要更新，否则就会出现异常，同一门课程学分不同的情况。

数据冗余
很显然，同一门课程由多个学生选修，“学分”就重复了多遍，另一方面，同一个学生选修了多门课程，姓名和年龄也会重复。

为了解决这种情况，进行表的拆分，使它满足第二范式，如下：

student表

学号	姓名	年龄
2015xxx	water	20

course表

课程名称	学分
DBMS	3

takes表

学号	课程名称	成绩
2015xxx	DBMS	90

以上表都是单关键字的，满足第二范式，这样就消除了插入异常，删除异常，更新异常和数据冗余。

第三范式

第三范式是在第二范式的基础之上定义的，如果数据表中不存在非关键字段对任意任意候选关键字段的传递函数依赖则符合第三范式。
所谓传递函数依赖，指的是如果存在“A → B → C”的决定关系，则C传递函数依赖于A。
简单来说，第三范式就是在第二范式的基础上消除传递依赖，即属性不依赖于其他非主属性。
看下面例子：

商品名称	价格	商品描述	重量	有效期	分类	分类描述
苹果	8.00		500g		生鲜食品	水果

如上表，它存在以下的函数依赖关系：
(商品名称) → （分类）
（分类） → （分类描述）
也就是说存储非关键字段“分类描述”对关键字段“商品名称”的传递函数依赖，所以上表不满足第三范式。

同样，不满足第三范式的数据表也存在着数据冗余，插入，删除，更新的异常。例如：（分类，分类描述）对于每一个商品都会进行记录，所以存在着数据冗余，同时，也存在着其他三个异常。

为了解决这个问题，同样对表进行拆分

goods表

商品ID	商品名称	价格	商品描述	重量	有效期
1	苹果	8.00		500g

category表

分类ID	分类	分类描述
1	生鲜食品	水果

联系表

分类ID	商品ID
1	1

这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。

BC范式
BC范式比第三范式要求更加严格，在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。
也就是说，如果存在复合关键字，则复合关键字之间也不能存在函数依赖关系。
假如存在以下关系表：

仓库	管理员	存储物品	数量
深圳仓	water	iPhone X	100

已知条件有：

• 某公司有若干仓库
• 每个仓库只有一名管理员，一名管理员只能在一个仓库工作
• 一个仓库可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应数量。

由此已知，该关系表存在以下依赖关系：
（仓库，存储物品）→ （管理员，数量）
（管理员，存储物品）→（仓库， 数量）
（仓库）→ （管理员）
（管理员）→ （仓库）
表中的唯一非关键字段为数量，不存在非关键字段对关键字段（码）的部分函数依赖和传递依赖函数，所以它符合第三范式。
但是，由于存在以下关系：

（仓库）→ （管理员）
（管理员）→ （仓库）

即存在关键字段决定关键字段的情况，即存在关键字段（仓库名）对 （管理员，物品名）的部分函数依赖，所以其不符合BCNF。

它会出现如下异常：

插入异常
当仓库没有存储物品的时候，无法给仓库分配管理员。因为存储物品也是主属性，根据实体完整性的要求，主属性不能为空。

删除异常
当仓库被清空后，即“存储物品”和“数量”内删除的同时，“仓库”和“管理员”信息也被删除了。

更新异常
如果仓库更换了管理员，则表中所有行的管理员都要修改。

分解表：

仓库

仓库	管理员
深圳仓	water

库存

仓库	物品	数量
深圳仓	iPhone X	1000

这样的数据库表是符合BCNF的，消除了删除异常、插入异常和更新异常。

范式之间的关系

image.png

推荐更详细的讲解：https://www.zhihu.com/question/24696366