1. 前言

在面向对象语言中涉及到诸多的设计模式,例如单例模式、适配器模式,设计模式的存在是为了让系统中的代码逻辑更加清晰,帮助开发者建立更加健壮的系统,同时满足易修改特性和易扩展特性。数据库设计时也存在类似设计模式的通用规范,被称为数据库范式。满足范式的数据库是简洁的,表与表之间的关系也清晰且明确,不会存储过多的冗余信息,在增删改查的时候也可以避免冗余的操作。

2. 数据库设计三大范式

面试官提问: 请描述下数据库设计的三大范式?

题目解析: 回答本题时,可以从总分的结构来阐述,即先阐述数据库范式的定义,再挨个解释每种范式的设计原则。

数据库范式定义:为了建立逻辑结构合理、冗余较小的数据库,在设计数据表时必须要遵循的设计规范。

接下来可以分点阐述第一、第二、第三范式的定义和案例。

2.1 数据库第一范式(1NF)

数据库第一范式是设计数据库时需要满足的最基本范式:

① 定义:第一范式(First Normal Form)要求数据库表中的所有字段都是不可拆分的原子字段,换句话说,每个字段不可以再进行拆分。

② 案例解释:对于一张最简单的用户信息表,定义了用户编号、姓名、年龄、电话这三个字段,user_info 表如下:

用户编号 (user_id) 姓名 (username) 年龄 (age) 电话 (phone)
1 小明 20 10086
2 小红 21 10087
3 小王 22 10088

其中电话 (phone) 这个字段可能存储的是座机电话号码、也可能是手机电话号码,定义上并不明确,这就违背了第一范式的原子性。所以为了满足第一范式,我们可以将电话字段拆分为座机电话 (fixed_phone) 和手机电话 (cell_phone) 两个字段,拆分后的 user_info 表如下:

用户编号 (user_id) 姓名 (username) 年龄 (age) 座机电话 (fixed_phone) 手机电话 (cell_phone)
1 小明 20 10086 18010002000
2 小红 21 10087 18010002001
3 小王 22 10088 18010002002

③ 范式优点:拆分之后,字段定义定义清晰。在查询数据库时我们可以明确过滤的是座机号码还是手机号码,方便业务层逻辑开发,而且后续维护也方便。

2.2 数据库第二范式(2NF)

在满足第一范式的基础上,数据库第二范式对字段定义进行了更严格的约束:

① 定义:第二范式(Second Normal Form)要求数据库中的每一列都和主键相关,不能和主键的一部分相关。

② 案例解释:在电商环境下,我们需要设计一个订单表,因为订单和商品绑定, 所以将商品编号和订单编号作为订单表的联合主键,初始设计的订单(order)表如下:

订单编号 (order_id) 商品编号 (good_id) 购买数量 (order_num) 单位 (unit) 商品单价 (good_price) 购买时间 (purchase_time)
10001 8888 1 千克 100 2020-10-11
10002 8888 1 千克 100 2020-10-12
10003 8890 3 300 2020-10-13

仔细观察,我们就能发现这种设计的问题在于:good_id = 8888 的商品,对于 order_id = 10001 和 10002 记录都存储了相同的单位和商品价格,这种冗余存储在数据量大的场景下是不能接收的,并且违反了第二范式设计原则,商品价格只和商品编号有关,和订单编号无关,我们将这张表进行拆分:

拆分的原则是:将属于商品的信息单独提炼为一张商品表,在原有的订单表只保留商品编号作为联合查询时的查询依据,优化后的订单(order)表如下:

订单编号 (order_id) 商品编号 (good_id) 购买数量 (order_num) 购买时间 (purchase_time)
10001 8888 1 2020-10-11
10002 8888 1 2020-10-12
10003 8889 3 2020-10-13

单独拆分出的商品(good)表如下:

商品编号 (good_id) 单位 (unit) 商品单价 (good_price)
8888 千克 100
8889 300

③ 范式优点:拆分之后,降低了数据库的冗余存储,并且逻辑清晰,要查询商品信息即走 good 表,要查询订单信息即走 order 表。

2.3 数据库第三范式(3NF)

① 定义:第三范式(Third Normal Form)要求数据库表中的每个字段和主键都直接相关,不能间接相关。

② 案例解释:还是以第一范式中的 user_info 表作为案例,如果要存储每个用户的省份和省会城市,我们可能会设计出下面这样一张表:

用户编号 (user_id) 姓名 (username) 年龄 (age) 座机电话 (fixed_phone) 手机电话 (cell_phone) 省份 (province) 省会城市 (city)
1 小明 20 10086 18010002000 北京市 北京市
2 小红 21 10087 18010002001 黑龙江省 哈尔滨市
3 小王 22 10088 18010002002 贵州省 贵阳市

我们将用户编号 (user_id) 作为主键,则姓名、年龄、座机电话、手机电话都和 "用户" 这个主体强相关,和主键直接相关,而省份和省会城市则和 "用户" 这个主体是弱相关,和主键间接相关,并且存在依赖关系:用户编号 -> 姓名,姓名 -> 省份,省份 -> 省会城市,这样构建了用户编号 -> 省会城市的间接传递关系,这种关系会导致数据冗余,而且在执行删除 / 修改 / 增加操作的时候,会产生异常情况:删除所有 "贵州省" 下的用户信息(即 user_id = 3 的记录),"贵州省" 和 "贵阳市" 的信息也被删除了(显然不合理,因为省份这个定义和省份下的人员记录并没有关系)。

所以我们需要将 user_info 表拆分,我们通过省份构建数据关系,优化后的用户(user_info)表如下:

用户编号 (user_id) 姓名 (username) 年龄 (age) 座机电话 (fixed_phone) 手机电话 (cell_phone) 省份 (province)
1 小明 20 10086 18010002000 北京市
2 小红 21 10087 18010002001 黑龙江省
3 小王 22 10088 18010002002 贵州省

独立拆分出的省份(province)表如下:

省份 (province) 省会城市 (city)
北京市 北京市
黑龙江省 哈尔滨市
贵州省 贵阳市

③ 范式优点:提高了表的独立性,降低数据存储冗余。

3. 小结

作为开发,在日常设计数据库表的时候可能不会特意注意使用数据库范式,但是细心关注大部分企业项目的表结构,就会发现大部分表都是遵循数据库范式设计的,第二范式和第三范式可能会混淆概念,第二范式的核心是关注非主键列是否依赖主键或者主键的一部分,地三藩市的核心是关注非主键列是否依赖主键,还是依赖其他的非主键列。