数据库设计-规范化规则

SQL反模式一书在附录章节给出了设计关系数据库的规范化规则，一个简明的规范化规则清单。

关系是什么

在规范化之前，我们先要理解下关系。

数学中关系定义：两个不同数据域上的值的集合，通过一个条件得到的一个所有可能组合的子集。

怎么理解呢？书中以棒球队和城市举例，比如有一个包含所有棒球队的集合，还有一个包含所有城市的集合，

如果把每个城市和每个球队随意组合进行列表，列表会很长，但其实我们只需要关注“球队和其所属城市的集合”。

所以“关系”：

1. 作为一种规则，比如城市是指某一支队伍所属城市“
2. 过滤子集的规则

然后将这个子集存储在数据库一张表中，关系即表示的是表中列与列之间的关系。

数据库表需要满足以下条件：

1. 行之间没有上下顺序
2. 列之间没有左右顺序
3. 重复记录不允许
4. 每一列只有一个类型，每一行只有一个值
5. 列中都是数据值，而不是物理存储标识，如Oracle的ROWNUM伪列等

规范化的神话

以下一些对规范化的理解是错误的

1. 规范化让数据库更慢，不规范让数据库更快
2. 规范化就是把数据移到子表之中
3. 规范化就是将属性尽可能的拆分开
4. 没人需要超过第三范式的规范化标准

很久以前我也是这么认为的，现在经过项目实践以及阅读SQL反模式这本书后，这些观念都是不正确的，比如不规范只能让某一种查询变快，其他业务的查询可能呢个就很困难和很慢，再比如不符合范式使得程序含有了隐藏的Bug。

规范化是保证我们正确的存储数据，保证数据的完整性，如果没有这个基础，就不用谈其他的了。

什么是规范化

书中简明给出了规范化的目标如下：

1. 以一种我们能理解的方式表示事物
2. 减少数据的冗余存储，防止异常或者不一致的数据
3. 支持完整性约束

提供数据的性能并不在以上列表中，但规范化本身保证数据的正确存储，很多时候项目因为错误、不一致、重复的数据出现很多问题，但如果一开始就进行规范化设计，就会避免这些问题，所以在某种程度是提升了效率。

范式

以前只了解一到四范式以及BCNF范式，从本书了解到第六范式和DKNF范式，一般的讲述都是以主键、候选键，主属性、非主属性以及依赖关系来解释范式的，这里了解到不同的解释。

 

第一范式

1. 该表必须是一个关系，满足前面关系中数据库表的条件
2. 表中没有重复组合，并不是记录重复，是指一行中可能有多个来自于同一个集合的值，

比如书中以产品缺陷管理举例，如缺陷标签，下面两个图都不满足第一范式。

 

第一个表，tag1、tag2、tag3都是来自同一个集合tag

第二个表，tags字段的值为多个值也是来自同一个集合tag

始终记住：关系中的每一行，都是从多个集合的每个集合中选一个值形成的组合

满足第一范式的如下图：

 

第二范式

1. 表中存在字段其取值与部分主键字段无关
2. 表中数据有冗余，更新不当会造成数据异常。

还是刚刚的缺陷标签表，现在要往BugsTags表中增加打标签者以及标签创建者，下图就不符合第二范式

 

tagger字段是打标签者，coiner是标签创建者，BugsTags表是bug_id和tag复合主键，coiner只与tag有关，与Bug是无关的，即只依赖部分主键，并且coiner取值是重复的，所以不符合第二范式。

如果需要修改某个标签的创建者，如果没有修改掉该表中所有tag为该标签的coiner，就会出现数据不一致，这种情况尤其可能出现在多个人同时修改该值的时候。

但是我这里有一个疑问，这张表符合第一范式吗？tagger和coiner字段都应该来账户集合（如Account表），有两列来自相同的集合应该不符合第一范式呢。除非理解标签创建者和打标签者属于不同集合。

而满足第二范式的设计，是增加一个标签表tags，如下图：

 

 

第三范式

第二范式是存在字段只依赖于组合主键中的部分，而第三范式是存在字段不依赖于任何主键，而是依赖其他字段。

如果需要记录处理Bug的工程师的email，下图的设计不符合第三范式：

 

assigned_email与主键bug_id无关，只由assigned_to这个非主键字段决定，所以不符合第三范式，assigned_email取值也是冗余的，也会有更新问题。

符合第三范式的设计是应该把email放到Accounts表中，email直接与主键关联没有冗余。如下图

 

BCNF（博伊斯-科德范式）

BCNF范式与第三范式的差异很细微，在第三范式中，所有非主键字段都必须直接依赖于这张表中的所有主键列，而BCNF范式要求主键字段也必须遵循这个规则，一般在一张表有复合主键时有效。

比如，我们有三种Tag类型，描述Bug所造成影响的tag，描述Bug影响子系统的tag，以及Bug修复状态的tag，且要求每一个Bug对于每一种tag类型只能有一个tag，所以这里可能的复合主键有bug_id加上tag，或者复合主键为bug_id加上tag_type,这两种组合都可以定位到一行。

存在两种复合主键，所以下图的设计就不符合BCNF，

 

这里一个隐含的假设是不同tag_type下的tag是不同的，且一个tag只属于一种tag_type，如果没有这个假设，bug_id加上tag是定位不到一行的。

按照这个假设，以前面符合第二范式和第三范式的设计很项，微妙的差别在于该字段在表中的作用是一般字段呢还是复合主键，符合第三范式的设计如下图：

 

第四范式

    随着业务应用复杂度的提升，比如需要支持多个用户报告同一个Bug，并可以分配给多个开发工程师，然后由多个质量工程师验证。这属于多对多的关系，   所以我们需要额外的一张表，如下图不符合第四范式：

 

这里不能单独使用bug_id作为主键。每个Bug需要多行数据来实现各个字段都支持多个账号的目的，主键需为bug_id、reported_by、assigned_to、verified_by的复合主键，然而bug报告时，是不需要立即进行分配和验证的，所以assigned_to和verified_by需要可以为null，而标准情况下主键是不能为null的。

另外的一个问题就是当报告问题者大于分派者，或者大于验证者就会出现数据冗余。

所以当只用一个表描述多个多对多关系时，就违背了第四范式，正确的做法如下图：

 

第五范式

任何满足BCNF范式并且没有复合主键的表将同时满足第五范式。

比如业务需要指定有些工程师只能为某些产品进行服务。

业务还需要了解哪些工程师在为哪些产品服务，以及修复了哪些Bug，下面的设计不符合第五范式

 

但这个表只说明了这个工程师当前指派去服务哪些产品，它不能说明哪个工程是可以被指派为哪些产品服务

与第四范式一样，也是在一张表中存储多种独立的多对多关系而产生的，微妙的差别是没有null的情况。

bug_id与assigned_to是多对多的关系，assigned_to与product_id是多对多的关系，而bug_id与product_id应该假定是多对一关系存在Bugs表中，每个Bug只属于一个product。

所以解决方案是把每个多对多关系放在一个表中，如下：

 

DK范式

DK范式涵盖了第三、四、五范式和BCNF范式。并认为表上的每个约束是这张表的数据域约束和主键约束的逻辑结果，如何理解呢？

比如状态为新建的bug应该是没有任何工作时间记录的，也不需要指派验证工程师的，一个做法是在状态这个非主键字段上建立触发器或者check约束，来进行验证如果状态为新建，没有值是允许的，其他情况就不允许。但这种在非主键字段建议此约束的做法不符合DK范式。

第六范式

第六范式目标是消除所有的联结依赖，通常用于支持记录字段取值的变更历史。

如Bug的状态随着时间推移产生变化，任何发生的变更，变更的时间，谁做的变更，以及其他可能的细节，需要另一张表进行记录。

可以想象，如果Bugs这张表需要满足第六范式，则需要变更的列都需要附带一个历史记录表，会导致表的数量过多，多于大多业务来讲，为每列变更都建立一个历史记录表是没有必要的，在一些数据仓库技术中会使用到第六范式。

个人体会：规范是为了减少数据冗余、提高数据的一致性必须的，二、三、四、BCNF、五范式的差异很微妙，随着业务系统的复杂度的提升，需要考虑更高层次的范式。

转载于:https://www.cnblogs.com/bwater/p/8203770.html