目录一. 设计数据表需要注意的点二. 范式2.1 范式简介2.2 范式有哪些2.3 第一范式(1NF)2.4 第二范式(2NF)2.5 第三范式(3NF)2.6 小结一. 设计数据表需要注意的点1首先要考虑设计这张表的用途这张表都要存放什么数据2还要保证数据表中数据的正确性在进行插入删除更新时应该做出哪些约束检查3要考虑如何降低数据表的数据冗余度可以允许数据量变大但要考虑数据量不会因为插入而急速增长4在设计时还要考虑日后的数据维护问题不能使表中的数据维护工作复杂二. 范式2.1 范式简介范式的英文名称位 Normal Form简称NF。在关系型数据库中关于数据表设计的基本原则规则称之为范式。可以简单的理解为一张数据表的设计结构需要满足某种设计标准的级别满足某种规则。2.2 范式有哪些目前关系型数据库的范式一共有6种按照范式的级别从低到高分别是 第一范式(1NF)第二范式(2NF)第三范式(3NF)巴斯-科德范式(BCNF)第四范式(4NF)第五范式(5NF)。范式的阶层越高数据的冗余度越低但要求也会越来越严格高范式都是在低范式的基础上推导出来的所以高范式一定满足低范式的规范要求。但在绝大多数企业设计数据表的时候一般遵循到3NF有些更为严格的表会设计到BCNF。不仅如此有些时候我们还会根据业务需要破坏范式要求适当增加表的冗余度来提高查询的性能这就是理论和实践结合的使用。2.3 第一范式(1NF)第一范式主要是确保数据表中每个字段都具有原子性每个字段都不可再进行拆分的最小单元像下面这种情况就违背了第一范式address 可以拆分为省和市除非说你的业务中只会用到查询整个地址的业务不会用到细粒度的地址查询功能可以这样设计但还是建议拆分成两个如果有需要可以在代码层再进行拼接。下面就是正确的表字段将原来的 address 拆分为 province 和 city每个字段都是最小字段不可再拆分满足了第一范式的要求。2.4 第二范式(2NF)第二范式要求在满足第一范式的基础上还要满足数据表中的每一条数据记录都是可唯一标识的。而且所有的非主键字段都须完全依赖于主键不能只依赖于主键的一部分。如果知道了主键的值就能检索到任意一行的任意一个具体字段的值。如下sid 表示学生编号cid 表示课程编号grades 表示课程成绩在这个数据表中想要查询到成绩必须知道学生号和课程号才能查询的得到一个学生会有多科成绩。如果只知道学生号将查询到多条数据如果只知道课程号将查询到所有同学当前课程的成绩只有学生号和课程号都确定才能查找到一条唯一的成绩记录所以 (学号课程)——成绩学号和课程虽然是两个字段但都是主键。我们再来看一个反例比赛表 player_game 中包含球员id比赛id球员姓名球员年龄比赛时间比赛地点比赛分数但是细细分析会发现nameage跟球员具有强关联timeaddress跟比赛具有强关联score 跟球员id和比赛id都有关联但是现在放在了一张表中是不合理的所以这种表的设计都是垃圾表。正确的做法是将上面的一张表拆分为3张表分别是球员信息表比赛信息表球员得分表。球员信息表球员id为主键通过球员id可以查询到详细的球员信息比赛信息表比赛id为主键通过比赛id可以查询到比赛的具体信息球员得分表球员id和比赛id为联合主键通过球员id和比赛id可以查询到某位球员在某场比赛的得分2.5 第三范式(3NF)第三范式是在第二范式的基础上确保数据表中每一个非主键字段都和主键字段直接相关所有的非主键字段不能依赖与其他的非主键不能存在依赖传递。比如说一张表现有三个字段 ABC且A是主键我要查询C应当通过主键A直接就可以查询到C。不能先通过A查询B再经过B才能查出C如果是这样就出现了依赖传递不符合第三范式的要求。如下设计一张关于商品的数据库表可以看到通过非主键字段商品类别id category_id 可以确定商品类别名称通过商品主键id 也可以确定商品类别名称中间具有传递性不满足第三范式的要求也商品类别名称这个字段在这张表中属于冗余字段。正确做法应该把商品类别id 和商品部类别名称单独放在另外一张表中然后把商品类别id 作为商品表的一个外键如果想要查询商品的分类名称再通过外键去另一张表中查询即可。2.6 小结第一范式确保每列的原子性。第二范式确保每列和主键完全依赖。第三范式确保每列和主键直接关联而非间接关联。范式的优点有助于消除数据库的数据冗余第三范式通常认为在性能扩展性数据完整性方面达到了最好的平衡。范式的缺点降低了查询效率其实同学们可以看出范式的等级越高拆分出来的表越多而多表查询在数据库层面是一个比较耗时的操作直接影响到了我们的业务吞吐能力因此在实际设计数据表的时候我们有时候会为了达到一种平衡违反第三范式来追求业务的性能但第一范式和第二范式都是几乎会遵守的。有些时候我们会违反第三范式的要求将一部分数据放在一张表中虽然会有一定的冗余但是能减少多表查询次数提高了数据的查询效率增大了业务吞吐能力这就是我们常说的牺牲空间换时间。对于用户而言最不喜欢的就是等待所以大多数场景下我们对于业务的响应能力和响应速度都有很高的要求对于数据库表的设计就比较严格选择合适的范式级别约束表字段。因此在实际设计数据表的时候需要根据业务需求而定如果是一个查询比较频繁的业务可以适当违反范式要求如果是一个增删改比较频繁的业务可以适当增大范式规范降低数据的冗余度提高修改数据的效率。
