数据库设计三范式

数据库设计三范式作为一个数据库的学习者，搞懂关系数据库的三大范式是很有用的。然而有关数据库范式的介绍都是采用学术性的定义，语法羞涩，让人难懂，故写下自己对数据库范式的理解，给初学者提供帮助。关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式，才能设计出高效率、优雅的数据库，否则可能会设计出错误的数据库。目前，主要有六种范式：第一范式、第二范式、第三范式、BC范式、第四范式和第五范式。满足最低要求的叫第一范式，简称1NF。在第一范式基础上进一步满足一些要求的为第二范式，简称2NF。其余依此类推。范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦，但是操作困难，因为需要联系多个表才能得到所需要数据，而且范式越高性能就会越差。要权衡是否使用更高范式是比较麻烦的，一般在项目中，用得最多的也就是第三范式，使用到第三范式也就足够了，性能好而且方便管理数据。本文不介绍规范化程度高于3NF的范式，对于很多大型复杂的系统，其数据库设计都没有遵循所谓的范式，这也是为什么会出现所谓的逆规范化，范式也需要考虑使用场景，不可一切东西都要范式化。在没有更多实践经验的情况下，遵循范式是非常好的选择。在实例中理解三大范式1NF:字段不可分强调的是列的原子性，即列不能够再分成其他几列。例1，学生信息表学生编号姓名性别联系方式20080901张三男email:[e-mail],phone:[phone]20080902李四女email:[e-mail],phone:[phone]以上的表就不符合，第一范式：联系方式字段可以再分，所以变更为正确的是：学生编号姓名性别电子邮件电话20080901张三男[e-mail]8888666620080902李四女[e-mail]66668888例2，学生班级信息学生编号姓名班级20080901小明高三1班20080902小叶高三2班以上的表就不符合，第一范式：班级字段可以再分，所以变更为正确的是：学生编号姓名年级班级20080901小明高三1班20080902小叶高三2班例3，员工信息表员工编号姓名工作年限20080901小明2009~201120080902小叶2006~2012以上的表就不符合，第一范式：工作年限可以再分，所以变更为正确的是：员工编号姓名工作年份离职年份20080901小明2009201120080902小叶20062012例4，学生成绩表学生编号姓名课程成绩20080901小明80,70,9020080902小叶60,70,85以上的表就不符合，第一范式：课程成绩可以再分，所以变更为正确的是：学生编号姓名语文数学外语20080901小明80709020080902小叶607085例5，联系人信息表姓名性别电话小明男0101-3464554,13699170707小叶女0101-3464674,13623450707以上的表就不符合，第一范式：电话可以再分，所以变更为正确的是：姓名性别座机手机小明男0101-346455413699170707小叶女0101-346467413623450707例6，公司信息表公司编号名称地址20080901谷歌美国加利福尼亚州圣克拉拉县山景市20080902百度中国北京市海淀区上地十街10号百度大厦以上的表就不符合，第一范式：地址可以再分，所以变更为正确的是：公司编号名称国籍地址20080901谷歌美国加利福尼亚州圣克拉拉县山景市20080902百度中国北京市海淀区上地十街10号百度大厦对于例6地址的拆分可根据需求进行，不一定非要拆分。如果需知道哪个国家并按其分类，那么显然第一个表格是不容易满足需求的，也不符合第一范式。因此是否符合第一范式的要求在一定程度上取决于后期对数据的查询和使用上，当然，第一范式是前人总结的通用方法，遵循它会得到意想不到的好处。2NF:有主键，非主键字段依赖主键首先是满足1NF，另外包含两部分内容，一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键。主键很重要，要记住在设计表的时候无论如何也要添加主键，没有主键的表会给你带来噩梦般的体验，会给系统开发、功能维护、数据维护带来不必要的麻烦。举个例子，上面例5，联系人信息表就没有添加主键，如下数据你想删除小明的信息该如何操作，根据姓名吗?不行，因为有重名的情况，只能通过姓名+电话两个字段组合为一个唯一的条件进行删除。姓名性别电话小明男0101-3464554,13699170707小叶女0101-3464674,13623450707小朱女0101-3464675,13623450705小明男0101-3464676,13623450706而有了主键，情况会是怎样的呢?你只需要根据联系人编号即可删除，一步到位。联系人编号姓名性别电话1小明男0101-3464554,136991707072小叶女0101-3464674,136234507073小朱女0101-3464675,136234507054小明男0101-3464676,13623450706切记，在任何时刻，一张表一定要有主键，如果你无法确定业务中哪个字段作为主键，那么你就建立一个ID字段作为主键，多一个ID字段不会影响什么。例1，学生信息表(主键学号)学号姓名性别年龄课程名称学分2008张三男15语文452008张三男15数学552009李四女16语文452009李四女16数学55以上的表就不符合，第二范式：主键(学号)无法唯一确定课程名称和学分，也就是说部分非主键字段不依赖主键，所以变更为正确的是：学生信息表学号姓名性别年龄2008张三男152008张三男15课程表课程名称学分语文45数学55学生选课表学号课程名称2008语文2008数学2009语文2009数学例2，学生借书表学生证号学生证办理时间借书证号借书证办理时间20082010年9月1号2010012010年10月1号20092010年9月2号2010112011年10月1号以上的表就不符合，第二范式：借书证号和借书证办理时间这些非主键字段不依赖学生证号这个主键，所以变更为正确的是：学生证表学生证号学生证办理时间20082010年9月1号20092010年9月2号借书证表借书证号借书证办理时间2010012010年10月1号2010112011年10月1号例3，订单表2NF在1NF的基础之上更进一层。2NF需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示：订单信息表(订单编号和商品编号为联合主键)订单编号商品编号商品名称数量单位价格客户所属单位联系方式0011挖掘机1台1200000￥张三上海玖智020-12345670012冲击钻8把230￥张三上海玖智020-12345670023铲车2辆980000￥李四北京公司010-1234567这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了2NF的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示：订单信息表订单编号客户所属单位联系方式001张三上海玖智020-1234567002李四北京公司010-1234567订单项目表订单编号商品编号数量001110012800232商品信息表商品编号商品名称单位商品价格1挖掘机台1200000￥2冲击钻个230￥3铲车辆980000￥这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。3NF:非主键字段不能相互依赖首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。通俗解释：任意一个字段都只依赖表中的同一个字段。例1，家庭成员表户主儿子女儿女儿的小熊女儿的海绵宝宝JackTomLucyBearspongebobJobsjulyLilyBear2spongebob2以上的表就不符合，第三范式：其中儿子，女儿等非主键列都完全依赖于主键(户主)，所以符合2NF，不过问题是女儿的小熊，女儿的海绵宝宝直接依赖的是女儿字段(非主键列)，而不是直接依赖于主键，它通过传递才依赖于主键，所以不符合3NF。所以变更为正确的是：户主信息表户主儿子女儿JackTomLucyJosbJulyLily女儿信息表女儿女儿的小熊女儿的海绵宝宝LucyBearspongebobLilyBear2spongebob2例2，订单表(主键是OrderID)OrderIDOrderDateCustomerIDCustomerNameCustomerAddrCustomerCity1012011年100xx联合公司中央大街100号纽约1022012年100xx联合公司中央大街100号纽约1032014年200yy联合公司白宫纽约以上的表就不符合，第三范式：其中OrderDate，CustomerID等非主键列都完全依赖于主键(OrderID)，所以符合2NF，不过问题是CustomerName，CustomerAddr，CustomerCity直接依赖的是CustomerID(非主键列)，而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合3NF。所以变更为正确的是：订单表OrderIDOrderDateCustomerID1012011年1001022012年1001032014年200客户信息表CustomerIDCustomerNameCustomerAddrCustomerCity100xx联合公司中央大街100号纽约200yy联合公司白宫纽约例3，学生信息表(主键是学号)学号姓名所在系系名称系地址101小明001数学系1号楼102小叶002文学系5号楼103小炫003物理系6号楼以上的表就不符合，第三范式：其中学号，姓名，所在系等非主键列都完全依赖于主键(学号)，所以符合2NF，不过问题是系名称，系地址直接依赖的是所在系(非主键列)，而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合3NF。所以变更为正确的是：学生表学号姓名所在系101小明001102小叶002103小炫003院系信息表系编号系名称系地址001数学系1号楼002文学系5号楼003物理系6号楼第二范式(2NF)和第三范式(3NF)的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分;3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。通过对每个范式的定义介绍，加上几个在实践中的反例进行讲解，可在一定程度上加快理论的理解，缩短理论和实践之间的距离，可快速上手3范式在开发中的应用。【本文为51CTO专栏作者“朱国立”的原创稿件，转载请通过作者微信公众号“开发者圆桌”获取联系和授权】戳这里，看该作者更多好文【编辑推荐】1.如何学习数据库系统知识2.58沈剑：数据库秒级平滑扩容架构方案3.关系型数据库管理系统MySQL提权基础4.58到家数据库30条军规解读5.MySQL数据库反弹端口连接提权