数据库设计原则笔记

1、数据库设计范式简析与举例一、 简介     数据库范式在数据库设计中的地位一直很暧昧，教科书中对于数据库范式倒是都给出了学术性的定义，但实际应用中范式的应用却不甚乐观，这篇文章会用简单的语言和一个简单的数据库DEMO将一个不符合范式的数据库一步步从第一范式实现到第四范式。 二、 范式的目标     应用数据库范式可以带来许多好处，但是最重要的好处归结为三点：     1.减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）

2、0;    2.消除异常（插入异常，更新异常，删除异常）     3.让数据组织的更加和谐          但剑是双刃的，应用数据库范式同样也会带来弊端，这会在文章后面说到。 三、 什么是范式     简单的说，范式是为了消除重复数据减少冗余数据，从而让数据库内的数据更好的组织，让磁盘空间得到更有效利用的一种标准化标准，满足高等级的范式的先决条件是满足低等

3、级范式。(比如满足2nf一定满足1nf)。 四、 DEMO让我们先从一个未经范式化的表看起,表如下：先对表做一个简单说明，employeeId是员工id,departmentName是部门名称，job代表岗位，jobDescription是岗位说明，skill是员工技能，departmentDescription是部门说明，address是员工住址对表进行第一范式(1NF)如果一个关系模式R的所有属性都是不可分的基本数据项，则R1NF。简单的说,第一范式就是每一个属性都不可再分。不符合第一范式则不能称为关系数据库。对于上表，不难看出Address是可以再分的，比如“北京市XX路XX小

4、区XX号”，这显然不符合第一范式，对其应用第一范式则需要将此属性分解到另一个表,如下:对表进行第二范式(2NF)若关系模式R1NF，并且每一个非主属性都完全函数依赖于R的码，则R2NF简单的说，是表中的属性必须完全依赖于全部主键，而不是部分主键.所以只有一个主键的表如果符合第一范式，那一定是第二范式。这样做的目的是进一步减少插入异常和更新异常。在上表中，departmentDescription是由主键DepartmentName所决定，但却不是由主键EmployeeID决定，所以departmentDescription只依赖于两个主键中的一个，故要departmentDescription

5、对主键是部分依赖，对其应用第二范式如下表：对表进行第三范式(3NF)关系模式R<U，F> 中若不存在这样的码X、属性组Y及非主属性Z（Z Í Y）, 使得XY，YZ，成立，则称R<U，F> 3NF。简单的说，第三范式是为了消除数据库中关键字之间的依赖关系，在上面经过第二范式化的表中，可以看出jobDescription(岗位职责)是由job(岗位)所决定，则jobDescription依赖于job,可以看出这不符合第三范式，对表进行第三范式后的关系图为：上表中，已经不存在数据库属性互相依赖的问题，所以符合第三范式。对表进行BC范式(BCNF)设关系模式R<

6、;U，F>1NF，如果对于R的每个函数依赖XY，若Y不属于X，则X必含有候选码，那么RBCNF。简单的说，bc范式是在第三范式的基础上的一种特殊情况，既每个表中只有一个候选键（在一个数据库中每行的值都不相同，则可称为候选键），在上面第三范式的noNf表中可以看出，每一个员工的email都是唯一的（难道两个人用同一个email?）则，此表不符合bc范式，对其进行bc范式化后的关系图为:对表进行第四范式(4NF)关系模式R<U，F>1NF，如果对于R的每个非平凡多值依赖XY（Y Í X），X都含有候选码，则R4NF。简单的说，第四范式是消除表中的多值依赖，也就是说可以减

7、少维护数据一致性的工作。对于上面bc范式化的表中，对于员工的skill，两个可能的值是”C#,sql,JavaScript”和“C#，UML,Ruby”,可以看出，这个数据库属性存在多个值，这就可能造成数据库内容不一致的问题，比如第一个值写的是”C#”,而第二个值写的是”C#.net”,解决办法是将多值属性放入一个新表，则第四范式化后的关系图如下：而对于skill表则可能的值为: 总结    上面对于数据库范式进行分解的过程中不难看出，应用的范式登记越高，则表越多。表多会带来很多问题：1 查询时要连接多个表，增加了查询的复杂度2 查询时需要连接

8、多个表，降低了数据库查询性能而现在的情况，磁盘空间成本基本可以忽略不计，所以数据冗余所造成的问题也并不是应用数据库范式的理由。因此，并不是应用的范式越高越好，要看实际情况而定。第三范式已经很大程度上减少了数据冗余，并且减少了造成插入异常，更新异常，和删除异常了。我个人观点认为，大多数情况应用到第三范式已经足够，在一定情况下第二范式也是可以的。数据库设计实际应用中的规则规则 1：弄清楚将要开发的应用程序是什么性质的（OLTP 还是 OPAP）？当你要开始设计一个数据库的时候，你应该首先要分析出你为之设计的应用程序是什么类型的，它是 “事务处理型”（Transactional） 的还是 “分析型”

9、 （Analytical）的？你会发现许多开发人员采用标准化做法去设计数据库，而不考虑目标程序是什么类型的，这样做出来的程序很快就会陷入性能、客户定制化的问题当中。正如前面所说的，这里有两种应用程序类型， “基于事务处理” 和 “基于分析”，下面让我们来了解一下这两种类型究竟说的是什么意思。事务处理型：这种类型的应用程序，你的最终用户更关注数据的增查改删（CRUD，Creating/Reading/Updating/Deleting）。这种类型更加官方的叫法是 “OLTP” 。分析型：这种类型的应用程序，你的最终用户更关注数据分析、报表、趋势预测等等功能。这一类的数据库的 “插入” 和 “更新

10、” 操作相对来说是比较少的。它们主要的目的是更加快速地查询、分析数据。这种类型更加官方的叫法是 “OLAP” 。那么换句话说，如果你认为插入、更新、删除数据这些操作在你的程序中更为突出的话，那就设计一个规范化的表否则的话就去创建一个扁平的、不规范化的数据库结构。以下这个简单的图表显示了像左边 Names 和 Address 这样的简单规范化的表，怎么通过应用不规范化结构来创建一个扁平的表结构。规则 2：将你的数据按照逻辑意义分成不同的块，让事情做起来更简单这个规则其实就是 “三范式” 中的第一范式。违反这条规则的一个标志就是，你的查询使用了很多字符串解析函数例如 substring、chari

11、ndex 等等。若真如此，那就需要应用这条规则了。比如你看到的下面图片上有一个有学生名字的表，如果你想要查询学生名字中包含“Koirala”，但不包含“Harisingh”的记录，你可以想象一下你将会得到什么样的结果。所以更好的做法是将这个字段拆分为更深层次的逻辑分块，以便我们的表数据写起来更干净，以及优化查询。规则 3：不要过度使用 “规则 2”开发者都是一群很可爱的生物。如果你告诉他们这是一条解决问题的正路，他们就会一直这么做下去，做到过了头导致了一些不必要的后果。这也可以应用于我们刚刚在前面提到的规则2。当你考虑字段分解时，先暂停一下，并且问问你自己是否真的需要这么做。正如所说的，分解应

12、该是要符合逻辑的。例如，你可以看到电话号码这个字段，你很少会把电话号码的 ISD 代码单独分开来操作（除非你的应用程序要求这么做）。所以一个很明智的决定就是让它保持原样，否则这会带来更多的问题。规则 4：把重复、不统一的数据当成你最大的敌人来对待集中那些重复的数据然后重构它们。我个人更加担心的是这些重复数据带来的混乱而不是它们占用了多少磁盘空间。例如下面这个图表，你可以看到 “5th Standard” 和 “Fifth standard” 是一样的意思，它们是重复数据。现在你可能会说是由于那些录入者录入了这些重复的数据或者是差劲的验证程序没有拦住，让这些重复的数据进入到了你的系统。现在，如果

13、你想导出一份将原本在用户眼里十分困惑的数据显示为不同实体数据的报告，该怎么做呢？解决方法之一是将这些数据完整地移到另外一个主表，然后通过外键引用过来。在下面这个图表中你可以看到我们是如何创建一个名为 “Standards”（课程级别） 的主表，然后同样地使用简单的外键连接过去。规则 5：当心被分隔符分割的数据，它们违反了“字段不可再分”前面的规则 2 即“第一范式”说的是避免 “重复组” 。下面这个图表作为其中的一个例子解释了 “重复组”是什么样子的。如果你仔细的观察 syllabus（课程） 这个字段，会发现在这一个字段里实在是填充了太多的数据了。像这些字段就被称为 “重复组” 了。如果我们

14、又得必须使用这些数据，那么这些查询将会十分复杂并且我也怀疑这些查询会有性能问题。这些被塞满了分隔符的数据列需要特别注意，并且一个较好的办法是将这些字段移到另外一个表中，使用外键连接过去，同样地以便于更好的管理。那么，让我们现在就应用规则2（第一范式） “避免重复组” 吧。你可以看到上面这个图表，我创建了一个单独的 syllabus（课程） 表，然后使用 “多对多” 关系将它与 subject（科目） 表关联起来。通过这个方法，主表（student 表）的 syllabus（课程） 字段就不再有重复数据和分隔符了。规则 6：当心那些仅仅部分依赖主键的列留心注意那些仅仅部分依赖主键的列。例如上面这

15、个图表，我们可以看到这个表的主键是 Roll No.+Standard。现在请仔细观察 syllabus 字段，可以看到 syllabus（课程） 字段仅仅关联（依赖） Standard（课程级别） 字段而不是直接地关联（依赖）某个学生（Roll No. 字段）。Syllabus（课程） 字段关联的是学生正在学习的哪个课程级别（Standard 字段）而不是直接关联到学生本身。那如果明天我们要更新教学大纲（课程）的话还要痛苦地为每个同学也修改一下，这明显是不符合逻辑的（不正常的做法）。更有意义的做法是将这些字段从这个表移到另外一个表，然后将它们与 Standard（课程级别）表关联起来。你可以

16、看到我们是如何移动 syllabus（课程）字段并且同样地附上 Standard 表。这条规则只不过是 “三范式” 里的 “第二范式”：“所有字段都必须完整地依赖主键而不是部分依赖”。规则 7：仔细地选择派生列如果你正在开发一个 OLTP 型的应用程序，那强制不去使用派生字段会是一个很好的思路，除非有迫切的性能要求，比如经常需要求和、计算的 OLAP 程序，为了性能，这些派生字段就有必要存在了。通过上面的这个图表，你可以看到 Average 字段是如何依赖 Marks 和 Subjects 字段的。这也是冗余的一种形式。因此对于这样的由其他字段得到的字段

17、，需要思考一下它们是否真的有必要存在。这个规则也被称为 “三范式” 里的第三条：“不应该有依赖于非主键的列” 。 我的个人看法是不要盲目地运用这条规则，应该要看实际情况，冗余数据并不总是坏的。如果冗余数据是计算出来的，看看实际情况再来决定是否应用这第三范式。规则 8：如果性能是关键，不要固执地去避免冗余不要把 “避免冗余” 当作是一条绝对的规则去遵循。如果对性能有迫切的需求，考虑一下打破常规。常规情况下你需要做多个表的连接操作，而在非常规的情况下这样的多表连接是会大大地降低性能的。规则 9：多维数据是各种不同数据的聚合OLAP 项目主要是解决多维数据问题。比如你可以看看下面这个图表，你会想拿到每个国家、每个顾客、每段时期的销售额情况。简单的说你正在看的销售额数据包含了三个维度的交叉。为这种情况做一个实际的设计是一个更好的办法。简单的说，你可以创建一个简单的主要销售表，它包含了销售额字段，通过外键将其他所有不同维度的表连接起来。规则 10：将那些具有“名值表”特点的表统一起来设计很多次我都遇到过这种 “名值表” 。 “名值表” 意味着它有一些键，这些键被其他数据关联着。比如下面这个图表，你可以看到我们有 Currency（货币型）和 Country（国家）这两张表。如果你仔细观察