1、范式简介
在中，关于数据表设计的就称为。可以理解为，一张数据表的设计结构需要满足的某种设计标准的 。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
2、范式都包括哪些
：球员编号 | 姓名 | 身份证号 | 年龄 | 球队编号
：球队编号 | 主教练 | 球队所在地
- ：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等。
- ：就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
- ：我们自己选定，也就是从候选键中选择一个，比如（球员编号）。
- ：球员表中的球队编号。
- ：在球员表中，主属性是（球员编号）（身份证号），其他的属性（姓名）
（年龄）（球队编号）都是非主属性。
4、第一范式(1st NF)
字段不可再拆分，具有原子特性(最小粒度)
举例1：
其中，user_info字段为用户信息，可以进一步拆分成更小粒度的字段，不符合数据库设计对第一范式的要求。
将拆分后如下：
例如，Employees关系中雇员姓名应当使用1个（fullname）、2个（firstname和lastname）还是3个（firstname、middlename和lastname）属性表示呢？答案取决于应用程序。
5、第二范式(2nd NF)
表中字段不存在除额外的依赖关系
举例1：
（学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课
程号也不能决定成绩，所以“（学号，课程号）→成绩”就是 。
举例2：
比赛表 player_game ，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键（或主键）来决定如下的关系：
但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：
对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？
- ：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次。
- ：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
- ：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
- ：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。
为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。
告诉我们字段属性需要是的，而 告诉我们一张表就是一个，一张表只表达一个意思。
违反了第二范式，因为有非主键属性仅依赖于候选键（或主键）的一部分。例如，可以仅通过orderid找到订单的 orderdate，以及 customerid 和 companyname，而没有必要再去使用productid。
6、第三范式(3rd NF)
非主键字段，不能存在间接依赖
举例1：
：每个部门有部门编号（dept_id）、部门名称、部门简介等信息。
：每个员工有员工编号、姓名、部门编号。列出部门编号后就不能再将部门名称、部门简介
等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。
举例2：
商品类别名称依赖于商品类别编号，不符合第三范式。
修改：
表1：符合第三范式的 的设计
举例3：
：球员编号、姓名、球队名称和球队主教练。现在，我们把属性之间的依赖关系画出来，如下图所示：
如果要达到 3NF 的要求，需要把数据表拆成下面这样：
举例4：
修改第二范式中的举例3。
此时的Orders关系包含 orderid、orderdate、customerid 和 companyname 属性，主键定义为 orderid。
customerid 和companyname均依赖于主键——orderid。例如，你需要通过orderid主键来查找代表订单中客户的customerid，同样，你需要通过 orderid 主键查找订单中客户的公司名称（companyname）。然而， customerid和companyname也是互相依靠的。为满足第三范式，可以改写如下：
符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：“每个非键属性依赖于键，依赖于整个键，并且除了键别无他物”。
根据业务，适当的冗余存储字段，减少多表查询的压力
1、概述
规范化 vs 性能
- 为满足某种商业目标 ,
- 在数据规范化的同时 , 要综合考虑
- 通过在给定的表中添加额外的字段，以大量
- 通过在给定的表中插入计算列，以
2、应用举例
举例1：
员工的信息存储在 中，部门信息存储在 中。通过 employees 表中的
department_id字段与 departments 表建立关联关系。如果要查询一个员工所在部门的名称：
如果经常需要进行这个操作，连接查询就会浪费很多时间。可以在 employees 表中增加一个冗余字段department_name，这样就不用每次都进行连接操作了。
举例2：
举例3：
有 2 个表，分别是 。
商品流水表里有 400 万条流水记录，商品信息表里有 2000 条商品记录。
举例4：
，对应的字段名称及含义如下：
实验数据：模拟两张百万量级的数据表
为了更好地进行 SQL 优化实验，我们需要给学生表和课程评论表随机模拟出百万量级的数据。
我们可以通过存储过程来实现模拟数据。
反范式优化实验对比
如果我们想要查询课程 ID 为 10001 的前 1000 条评论，需要写成下面这样：
运行结果（1000 条数据行）：
如果我们想要提升查询的效率，可以允许适当的数据冗余，也就是在商品评论表中增加用户昵称字段，在 class_comment 数据表的基础上增加 stu_name 字段，就得到了 class_comment2 数据表。
这样一来，只需单表查询就可以得到数据集结果：
运行结果（1000 条数据）：
3、反范式的新问题
- 存储 了
- 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则
- 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常
- 在 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂
4、反范式的适用场景
当冗余信息有价值或者能 的时候，我们才会采取反范式的优化。
- 增加冗余字段的建议
- 历史快照、历史数据的需要
在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每次发生的 都属于 ，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。
反范式优化也常用在 的设计中，因为数据仓库通常 ，对增删改的实时性要求不强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。
1、案例
仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个
属性。这样，我们就可以找到数据表的候选键。
- ：是（管理员，物品名）和（仓库名，物品名），然后我们从候选键中选择一个作为 主键 ，比如（仓库名，物品名）。
- ：包含在任一候选键中的属性，也就是仓库名，管理员和物品名。
- ：数量这个属性。
2、是否符合三范式
如何判断一张表的范式呢？我们需要根据范式的等级，从低到高来进行判断。
- 首先，数据表每个属性都是的，符合 的要求；
- 其次，数据表中，（仓库名，物品名）决定数量，（管理员，物品名）决定数量。因此，数据表符合`` 2NF` 的要求；
- 最后，数据表中的非主属性，。因此符合的要求。
3、存在的问题
既然数据表已经符合了 3NF 的要求，是不是就不存在问题了呢？我们来看下面的情况：
- 增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因此会出现 ；
- 如果仓库更换了管理员，我们就可能会 ；
- 如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。
你能看到，即便数据表符合 3NF 的要求，同样可能存在插入，更新和删除数据的异常情况。
4、问题解决
首先我们需要确认造成异常的原因：
主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，这样就有可能导致上面的异常情况。
因此引入BCNF，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。
- 如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于BCNF。
根据 BCNF 的要求，我们需要把仓库管理关系 warehouse_keeper 表拆分成下面这样：
：（仓库名，管理员）
：（仓库名，物品名，数量）
这样就不存在主属性对于候选键的部分依赖或传递依赖，上面数据表的设计就符合 BCNF。
再举例：
所以这个表的部分主键Major依赖于非主键属性Advisor，那么我们可以进行以下的调整，拆分成2个表：
导师表：
举例1：
职工表(职工编号，职工孩子姓名，职工选修课程)。
在这个表中，同一个职工可能会有多个职工孩子姓名。
同样，同一个职工也可能会有多个职工选修课程，即这里存在着多值事实，不符合第四范式。
如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如： (职工编
号，职工孩子姓名)， (职工编号，职工选修课程)，两个表都只有一个多值事实，所以符合第四
范式。
举例2：
比如我们建立课程、教师、教材的模型。我们规定，每门课程有对应的一组教师，每门课程也有对应的一组教材，一门课程使用的教材和教师没有关系。
我们建立的关系表如下：
课程ID，教师ID，教材ID；这三列作为联合主键。
假如我们下学期想采用一本新的英版高数教材，但是还没确定具体哪个老师来教，那么我们就无法在这
个表中维护Course高数和Book英版高数教材的的关系。
除了第四范式外，我们还有更高级的第五范式（又称完美范式）和域键范式（DKNF）。
在满足第四范式（4NF）的基础上，消除不是由候选键所蕴含的连接依赖。，则称此关系模式符合第五范式。
函数依赖是多值依赖的一种特殊的情况，而多值依赖实际上是连接依赖的一种特殊情况。但连接依赖不像函数依赖和多值依赖可以由 ，而是在 时才反映出来。存在连接依赖的关系模式仍可能遇到数据冗余及插入、修改、删除异常等问题。
第五范式处理的是 ，这个范式基本 ，因为无损连接很少出现，而且难以察觉。
而域键范式试图定义一个 ，该范式考虑所有的依赖和约束类型，但是实用价值也是最小的，只存在理论研究中。
ER 模型中有三个要素，分别是、和。
- ，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用 来表示。实体分为两类，分别是 和 。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。
- ，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用 来表示。
- ，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模型中用 来表示。
注意：实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，。也就是说，属性不能包含其他属性。
1、关系的类型
在 ER 模型的 3 个要素中，关系又可以分为 3 种类型，分别是、、。
- ：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是一对一的关系。一个人只能有一个身份证信息，一个身份证信息也只属于一个人。
- ：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关
系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级都有多个学生，每个学
生则对应一个班级，班级对学生就是一对多的关系。 - ：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购商品。再比如一个选课表，有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目，这就是多对多的关系。
2、建模分析
ER 模型看起来比较麻烦，但是对我们把控项目整体非常重要。如果你只是开发一个小应用，或许简单设计几个表够用了，一旦要设计有一定规模的应用，在项目的初始阶段，建立完整的 ER 模型就非常关键
了。
开发应用项目的实质，其实就是 。
我们设计的案例是 ，由于电商业务太过庞大且复杂，所以我们做了业务简化，比如针对SKU（StockKeepingUnit，库存量单位）和SPU（Standard Product Unit，标准化产品单元）的含义上，我们直接使用了SKU，并没有提及SPU的概念。本次电商业务设计总共有8个实体，如下所示。
- 地址实体
- 用户实体
- 购物车实体
- 评论实体
- 商品实体
- 商品分类实体
- 订单实体
- 订单详情实体
（1）用户可以在电商平台添加多个地址；
（2）用户只能拥有一个购物车；
（3）用户可以生成多个订单；
（4）用户可以发表多条评论；
（5）一件商品可以有多条评论；
（6）每一个商品分类包含多种商品；
（7）一个订单可以包含多个商品，一个商品可以在多个订单里。
（8）订单中又包含多个订单详情，因为一个订单中可能包含不同种类的商品
3、ER 模型的细化
有了这个 ER 模型，我们就可以从整体上 电商的业务了。刚刚的 ER 模型展示了电商业务的框架，但是只包括了订单，地址，用户，购物车，评论，商品，商品分类和订单详情这八个实体，以及它们之间的关系，还不能对应到具体的表，以及表与表之间的关联。我们需要把 ，用 来表示，这样我们得到的 ER 模型就更加完整了。
因此，我们需要进一步去设计一下这个 ER 模型的各个局部，也就是细化下电商的具体业务流程，然后把它们综合到一起，形成一个完整的 ER 模型。这样可以帮助我们理清数据库的设计思路。
接下来，我们再分析一下各个实体都有哪些属性，如下所示。
（1） 包括用户编号、省、市、地区、收件人、联系电话、是否是默认地址。
（2） 包括用户编号、用户名称、昵称、用户密码、手机号、邮箱、头像、用户级别。
（3） 包括购物车编号、用户编号、商品编号、商品数量、图片文件url。
（4） 包括订单编号、收货人、收件人电话、总金额、用户编号、付款方式、送货地址、下单时间。
（5） 包括订单详情编号、订单编号、商品名称、商品编号、商品数量。
（6） 包括商品编号、价格、商品名称、分类编号、是否销售，规格、颜色。
（7） 包括评论id、评论内容、评论时间、用户编号、商品编号
（8） 包括类别编号、类别名称、父类别编号
4、ER 模型图转换成数据表
综合以上内容，总结出数据表设计的一般原则：“三少一多”
- 数据表的个数越少越好
- 数据表中的字段个数越少越好
- 数据表中联合主键的字段个数越少越好
- 使用主键和外键越多越好
注意：这个原则并不是绝对的，有时候我们需要牺牲数据的冗余度来换取数据处理的效率。
1、 关于库
- 【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字母开头。
- 【强制】库名中英文 ，不同单词采用 分割。须见名知意。
- 【强制】库的名称格式：业务系统名称_子系统名。
- 【强制】库名禁止使用关键字（如type,order等）。
- 【强制】创建数据库时必须 ，并且字符集只能是utf8或者utf8mb4。创建数据库SQL举例：CREATE DATAbase crm_fund ;
- 【建议】对于程序连接数据库账号，遵循 使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号 。
- 【建议】临时库以 为前缀，并以日期为后缀；备份库以 为前缀，并以日期为后缀。
2、关于表、列
- 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以 。
- 【强制】 ，不同单词采用下划线分割。须。
- 【强制】表名要求有模块名强相关，同一模块的表名尽量使用 。比如：crm_fund_item
- 【强制】创建表时必须 为utf8或utf8mb4。
- 【强制】表名、列名（如type,order等）。
- 【强制】创建表时必须 类型。如无特殊需求，一律为InnoDB。
- 【强制】建表必须有comment。
- 【强制】字段命名应尽可能使用表达实际含义的英文单词或 。如：公司 ID，不要使用corporation_id, 而用corp_id 即可。
- 【强制】布尔值类型的字段命名为 。如member表上表示是否为enabled的会员的字段命
名为 is_enabled。 - 【强制】，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
- 【建议】建表时关于主键：
(1)强制要求主键为id，类型为int或bigint，且为auto_increment 建议使用unsigned无符号型。
(2)标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。 - 【建议】核心表（如用户表）必须有行数据的 （create_time）和 ，便于查问题。
- 【建议】表中所有字段尽量都是 属性，业务可以根据需要定义 。 因为使用
NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问
题。 - 【建议】所有存储相同数据的（一般作为关联列，如果查询时关联列类型
不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。 - 【建议】中间表（或临时表）用于保留中间结果集，名称以开头。
备份表用于备份或抓取源表快照，名称以 开头。中间表和备份表定期清理。
【示范】一个较为规范的建表语句：