数据库设计三范式

1、数据库设计三范式作为一个数据库的学习者，搞懂关系数据库的三大范式是很有用的。然而有关数据库范式的介绍都是采用学术性的定义，语法羞涩，让人难懂，故写下自己对数据库范式的理解，给初学者提供帮助。关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式，才能设计出高效率、优雅的数据库，否则可能会设计出错误的数据库。目前，主要有六种范式：第一范式、第二范式、第三范式、BC范式、第四范式和第五范式。满足最低要求的叫第一范式，简称1NF。在第一范式基础上进一步满足一些要求的为第二范式，简称2NF。其余依此类推。范式可以避免数

2、据冗余，减少数据库的空间，减轻维护数据完整性的麻烦，但是操作困难，因为需要联系多个表才能得到所需要数据，而且范式越高性能就会越差。要权衡是否使用更高范式是比较麻烦的，一般在项目中，用得最多的也就是第三范式，使用到第三范式也就足够了，性能好而且方便管理数据。本文不介绍规范化程度高于3NF的范式，对于很多大型复杂的系统，其数据库设计都没有遵循所谓的范式，这也是为什么会出现所谓的逆规范化，范式也需要考虑使用场景，不可一切东西都要范式化。在没有更多实践经验的情况下，遵循范式是非常好的选择。在实例中理解三大范式1NF:字段不可分强调的是列的原子性，即列不能够再分成其他几列。例1，学生信息表 学生编号 姓

3、名 性别 联系方式 张三 男 email:,phone: 李四 女 email:,phone: 以上的表就不符合，第一范式：联系方式字段可以再分，所以变更为正确的是： 学生编号 姓名 性别 电子邮件 电话 张三 男 李四 女 例2，学生班级信息 学生编号 姓名 班级 小明 高三1班 小叶 高三2班 以上的表就不符合，第一范式：班级字段可以再分，所以变更为正确的是： 学生编号 姓名 年级 班级 小明 高三 1班 小叶 高三 2班例3，员工信息表 员工编号 姓名 工作年限 小明 20092011 小叶 20062012 以

4、上的表就不符合，第一范式：工作年限可以再分，所以变更为正确的是： 员工编号 姓名 工作年份 离职年份 小明 2009 2011 小叶 2006 2012例4，学生成绩表 学生编号 姓名 课程成绩 小明 80,70,90 小叶 60,70,85 以上的表就不符合，第一范式：课程成绩可以再分，所以变更为正确的是： 学生编号 姓名 语文 数学 外语 小明 80 70 90 小叶 60 70 85例5，联系人信息表 姓名 性别 电话 小明 男 0101-, 小叶 女 0101-, 以上的表就不符合，第一范式：电话可以再分，所以变更为正确的是： 姓名 性别 座机 手机 小明 男 0101- 小叶 女 0

5、101- 例6，公司信息表 公司编号 名称 地址 谷歌 美国加利福尼亚州圣克拉拉县山景市 百度 中国北京市海淀区上地十街10号百度大厦 以上的表就不符合，第一范式：地址可以再分，所以变更为正确的是： 公司编号 名称 国籍 地址 谷歌 美国 加利福尼亚州圣克拉拉县山景市 百度 中国 北京市海淀区上地十街10号百度大厦对于例6地址的拆分可根据需求进行，不一定非要拆分。如果需知道哪个国家并按其分类，那么显然第一个表格是不容易满足需求的，也不符合第一范式。因此是否符合第一范式的要求在一定程度上取决于后期对数据的查询和使用上，当然，第一范式是前人总结的通用方法，遵循它会得到意想不到的好处。2NF:有主键

6、，非主键字段依赖主键首先是满足 1NF，另外包含两部分内容，一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键。主键很重要，要记住在设计表的时候无论如何也要添加主键，没有主键的表会给你带来噩梦般的体验，会给系统开发、功能维护、数据维护带来不必要的麻烦。举个例子，上面例5，联系人信息表就没有添加主键，如下数据你想删除小明的信息该如何操作，根据姓名吗?不行，因为有重名的情况，只能通过姓名+电话两个字段组合为一个唯一的条件进行删除。姓名 性别 电话 小明 男 0101-, 小叶 女 0101-, 小朱 女 0101-, 小明 男 0101-,而有了主键，情况会是怎样的呢?你只需要根据联

7、系人编号即可删除，一步到位。联系人编号 姓名 性别 电话 1 小明 男 0101-, 2 小叶 女 0101-, 3 小朱 女 0101-, 4 小明 男 0101-,切记，在任何时刻，一张表一定要有主键，如果你无法确定业务中哪个字段作为主键，那么你就建立一个ID字段作为主键，多一个ID字段不会影响什么。例1，学生信息表(主键学号) 学号 姓名 性别 年龄 课程名称 学分 2008 张三 男 15 语文 45 2008 张三 男 15 数学 55 2009 李四 女 16 语文 45 2009 李四 女 16 数学 55以上的表就不符合，第二范式：主键(学号)无法唯一确定课程名称和学分，也就是

8、说部分非主键字段不依赖主键，所以变更为正确的是：学生信息表 学号 姓名 性别 年龄 2008 张三 男 15 2008 张三 男 15课程表 课程名称 学分 语文 45 数学 55 学生选课表 学号 课程名称 2008 语文 2008 数学 2009 语文 2009 数学例2，学生借书表 学生证号 学生证办理时间 借书证号 借书证办理时间 2008 2010年9月1号 2010年10月1号 2009 2010年9月2号 2011年10月1号以上的表就不符合，第二范式：借书证号和借书证办理时间这些非主键字段不依赖学生证号这个主键，所以变更为正确的是：学生证表 学生证号 学生证办理时间 2008

9、2010年9月1号 2009 2010年9月2号借书证表 借书证号 借书证办理时间 2010年10月1号 2011年10月1号例3，订单表2NF在1NF的基础之上更进一层。2NF需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示：订单信息表(订单编号和商品编号为联合主键) 订单编号 商品编号 商品名称 数量 单位 价格 客户 所属单位 联系方式 0

10、01 1 挖掘机 1 台 ￥ 张三 上海玖智 020- 001 2 冲击钻 8 把 230￥ 张三 上海玖智 020- 002 3 铲车 2 辆 ￥ 李四 北京公司 010-这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了2NF的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示：订单信息表 订单编号 客户 所属单位 联系方式 001 张三 上海玖智 020- 002 李四 北京公司 010-订单项目表

11、订单编号 商品编号 数量 001 1 1 001 2 8 002 3 2商品信息表 商品编号 商品名称 单位 商品价格 1 挖掘机 台 ￥ 2 冲击钻 个 230￥ 3 铲车 辆 ￥这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。3NF:非主键字段不能相互依赖首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。 通俗解释：任意一个字段都只依赖表中的同一个字段。例1，家庭成员表 户主 儿子 女儿 女儿的小熊 女儿的海绵宝宝 Jack Tom Lucy

12、 Bear spongebob Jobs july Lily Bear2 spongebob2以上的表就不符合，第三范式：其中 儿子 ，女儿 等非主键列都完全依赖于主键(户主)，所以符合 2NF，不过问题是 女儿的小熊 ，女儿的海绵宝宝 直接依赖的是 女儿字段 (非主键列)，而不是直接依赖于主键，它通过传递才依赖于主键，所以不符合 3NF。 所以变更为正确的是：户主信息表 户主 儿子 女儿 Jack Tom Lucy Josb July Lily女儿信息表 女儿 女儿的小熊 女儿的海绵宝宝 Lucy Bear spongebob Lily Bear2 spongebob2例2，订单表(主键是O

13、rderID) OrderID OrderDate CustomerID CustomerName CustomerAddr CustomerCity 101 2011年 100 xx联合公司 中央大街100号 纽约 102 2012年 100 xx联合公司 中央大街100号 纽约 103 2014年 200 yy联合公司 白宫 纽约以上的表就不符合，第三范式：其中 OrderDate，CustomerID等非主键列都完全依赖于主键(OrderID)，所以符合 2NF，不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID(

14、非主键列)，而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。 所以变更为正确的是：订单表 OrderID OrderDate CustomerID 101 2011年 100 102 2012年 100 103 2014年 200客户信息表 CustomerID CustomerName CustomerAddr CustomerCity 100 xx联合公司 中央大街100号 纽约 200 yy联合公司 白宫 纽约例3，学生信息表(主键是学号) 学号 姓名 所在系 系名称 系地址 101 小明 001 数学系 1号楼 102 小叶 002 文学系 5号楼 103 小炫 0

15、03 物理系 6号楼以上的表就不符合，第三范式：其中学号，姓名，所在系等非主键列都完全依赖于主键(学号)，所以符合 2NF，不过问题是系名称，系地址直接依赖的是所在系(非主键列)，而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。 所以变更为正确的是：学生表 学号 姓名 所在系 101 小明 001 102 小叶 002 103 小炫 003院系信息表 系编号 系名称 系地址 001 数学系 1号楼 002 文学系 5号楼 003 物理系 6号楼第二范式(2NF)和第三范式(3NF)的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分;3NF：非主键列是直接依赖于主键，还是直接依赖于非主