数据库表设计，三大范式是什么？

前言

公司新项目现在是从0到1设计，不知道怎么数据库设计交给了我和同桌两个人。明确需求之后我两就开始设计数据库表了。MySQL中说数据库设计需要遵守三大范式，今天顺便看看这三范式是什么东西。

首先在设计表之前，需要对需求进行抽象，将需求抽象为业务实体，实体对应着数据库中的表，实体属性就是表中的字段。要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。所以我们下面来细说以下三范式

第一范式

第一范式要求表中的字段不能继续拆分，满足原子性字段。

比如创建用户表，包含id和name-age字段，这是万万不可的，name和id不符合第一范式，应该将其拆分为2个字段。

字段的原子性对于我们设计者来说是主观的，例如一个住址信息的字段，我住在上海市徐汇区xx街道xx路xx号，是否需要把住址信息拆分到省、市、区、街道、路呢？ 对于不同的场景，划分是不一样的。我记得在填报国企面试的时候，这些字段都是拆分开填的，那么对应的存在他们国企数据库中的，也是分开的。

第二范式

第二范式是在第一范式基础上简历起来的，要想满足第二范式，必须要先满足第一范式。

第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。也就是实体都是通过主键区分的，并且是完全依赖这个主键，不能依赖主键的一部分。

eg：比赛表中包含球员编号，姓名，年龄，比赛编号，比赛时间，比赛场地等属性

（球员编号，比赛编号）->（姓名，年龄，比赛时间，比赛场地，得分）

如果设计（球员编号，比赛编号）为主键，可以看到字段（姓名，年龄）完全依赖（球员编号），而不是（球员编号，比赛编号），只依赖了主键（球员编号，比赛编号）的一部分，同理（比赛时间，比赛场地）也是如此。

这样的表会发生很严重的数据冗余：

1. 如果球员参加m场比赛，球员姓名和年龄就重复了m-1次，比赛有n个球员参加，比赛时间和场地也重复了n-1次

同时 如果想要插入一场新的比赛，球员还没确定，增加不了数据；某个球员退役想要删除这个球员编号，那么他的比赛信息也会都删除掉。

所以通常来说，我们会设计出三张表

1. 球员表 球员编号+姓名+年龄
2. 比赛表 比赛编+比赛时间+比赛场地
3. 球员比赛关系表 球员编号+比赛编号+得分

这样设计就符合第二范式了，实体完全依赖并只依赖主键，而非依赖主键的部分。

第三范式

第三范式把第二范式更加细化，第二范式强调的是实体依赖于主键，通过主键区分一个个实体，第三范式强调实体中的其他非主键键只依赖于这个主键。

例如：

部门信息表：部门编号，部门名称，部门简介

员工信息表：员工编号，姓名，部门编号

当员工信息表中有了部门编号后，就不必再写部门名称，部门简介这种信息了，否则会存在很多数据冗余。

另一个例子：

如下的商品表，不符合第三范式，其中商品类别名称还强依赖于商品类别id

应该设计为两张独立的表：通过category_id进行关联

反范式设计

完全符合范式化的设计并非完美，很多实际业务中其实本来就是存在大量数据冗余的，因为表关联对于查询性能的影响更加严重。

反范式设计就是为了性能和读取效率而适当违反范式规范，允许存在冗余字段，用空间换时间。

例如上述的商品表中冗余存储商品类别名称，在查询的时候，直接就把商品和商品类别名称查出来了，不用关联单独的商品类别表查出这个数据。

反范式的优缺点：

1. 优点

   1. 减少表关联，提高查询速度
   2. 更好的进行索引优化，因为表关联会带来索引失效。

2. 缺点

   1. 有冗余字段，空间变大
   2. 一个表中修改值，冗余表也要修改，否则数据不一致
   3. 数据量小的时候，表关联带来的查询损耗很小，反范式不能体现出优势，还会让数据库设计复杂化

数据表的设计原则总结

1. 数据表的个数越少越好
2. 数据表中的字段个数越少越好
3. 数据表中联合主键的字段个数越少越好
4. 使用主键和外键越多越好

库表设计理念建议

关于库

1. 【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字
   母开头。
2. 【强制】库名中英文 一律小写 ，不同单词采用 下划线 分割。须见名知意。
3. 【强制】库的名称格式：业务系统名称_子系统名。
4. 【强制】库名禁止使用关键字（如type,order等）。
5. 【强制】创建数据库时必须 显式指定字符集 ，并且字符集只能是utf8或者utf8mb4。
   创建数据库SQL举例：CREATE DATABASE crm_fund DEFAULT CHARACTER SET ‘utf8’ ;
6. 【建议】对于程序连接数据库账号，遵循 权限最小原则
   使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号 原则上不准有drop权限 。
7. 【建议】临时库以 tmp_ 为前缀，并以日期为后缀；
   备份库以 bak_ 为前缀，并以日期为后缀。

关于表、列

1. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议
   以 英文字母开头 。
2. 【强制】 表名、列名一律小写 ，不同单词采用下划线分割。须见名知意。
3. 【强制】表名要求有模块名强相关，同一模块的表名尽量使用 统一前缀 。比如：crm_fund_item
4. 【强制】创建表时必须 显式指定字符集 为utf8或utf8mb4。
5. 【强制】表名、列名禁止使用关键字（如type,order等）。
6. 【强制】创建表时必须 显式指定表存储引擎 类型。如无特殊需求，一律为InnoDB。
7. 【强制】建表必须有comment。
8. 【强制】字段命名应尽可能使用表达实际含义的英文单词或 缩写 。如：公司 ID，不要使用
   corporation_id, 而用corp_id 即可。
9. 【强制】布尔值类型的字段命名为 is_描述 。如member表上表示是否为enabled的会员的字段命
   名为 is_enabled。
10. 【强制】禁止在数据库中存储图片、文件等大的二进制数据
    通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随
    机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
11. 【建议】建表时关于主键： 表必须有主键 (1)强制要求主键为id，类型为int或bigint，且为
    auto_increment 建议使用unsigned无符号型。 (2)标识表里每一行主体的字段不要设为主键，建议
    设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机
    插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。
12. 【建议】核心表（如用户表）必须有行数据的 创建时间字段 （create_time）和 最后更新时间字段
    （update_time），便于查问题。
13. 【建议】表中所有字段尽量都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT值 。 因为使用
    NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问
    题。
14. 【建议】所有存储相同数据的 列名和列类型必须一致 （一般作为关联列，如果查询时关联列类型
    不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
15. 【建议】中间表（或临时表）用于保留中间结果集，名称以 tmp_ 开头。
    备份表用于备份或抓取源表快照，名称以 bak_ 开头。中间表和备份表定期清理。