数据库表怎么设计？走进范式原则

数据库范式

        在设计数据库表结构时，是无法脱离数据库三范式原则的，对于初级程序员来说，这一节的内容至关重要。它是规范化的一种方法，用于设计具有高度结构化和标准化的数据库。常见的数据库范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和Boyce-Codd范式（BCNF）。每个范式都有其特定的规则和原则，以确保数据的正确性、一致性和完整性。

        第一范式（1NF）：要求每个属性都是原子性的，即不可再分解。表必须具有唯一的主键，以确保每个行都具有唯一的标识符。

        第二范式（2NF）：要求每个非主键属性都完全依赖于主键，不存在部分依赖。

        第三范式（3NF）：要求每个非主键属性都不依赖于其他非主键属性，不存在传递依赖。

        Boyce-Codd范式（BCNF）：第三范式的扩展，要求每个属性都完全依赖于主键，不存在任何属性决定了其他非主键属性。这种范式适用于具有多个复合主键的表。

---

第一范式表设计

        现在开始实践一下，我们假设现在需要设计学生信息表，每个学生有一个唯一的身份证号、学号、姓名、年龄、性别、家庭地址、班级信息。那么此时按照一范式的设计原则，我们这里会出现几张表？

        首先从扩展性来看：其中家庭地址和班级信息这两个属性都不具备原子性，因为家庭地址可能会存在：省、市、区、街道、门牌号、邮箱等信息。班级信息可能会存在：班级标识、所属班主任、班级名称或者其它的信息。因此按照一范式的设计原则来看，这里至少需要三张表。具体如下：

学生表

字段名	数据类型	说明
id	INT	主键Id
name	VARCHAR(50)	学生姓名
age	INT	学生年龄
gender	ENUM('0','1')	学生性别(0:女;1:男)
class_id	INT	所属班级ID
address_id	INT	详细地址ID

班级表

字段名	数据类型	说明
id	INT (PRIMARY KEY)	主键ID
class_name	VARCHAR(50)	班级名称
class_teacher_id	INT	教师ID

地址信息表

字段名	数据类型	说明
id	INT (PRIMARY KEY)	主键ID
province	VARCHAR(50)	省份名称
city	VARCHAR(50)	城市名称
area	VARCHAR(50)	区县名称
street	VARCHAR(50)	街道名称
postbox	VARCHAR(50)	邮箱号

       那么性别呢？它同样存在了‘男’、‘女’这两种可能，需要拆分为一张单独的表吗？

        答案是不需要，因为性别只是一个简单的属性，也就是非女即男，可以使用Enum类型来限制性别的列，以此来保证这个字段不会被输入其它值。

        以上这样做我们就保证了每张表中的数据原子性。也就达成了一范式的设计原则。同时这也是业界广泛应用的设计方式。

        原子性：指关系数据库中的每一列都只包含一个数据项。也就是说，如果一个列需要包含多个数据项，那么它应该被拆分成多个列。

---

第二范式表设计

        再来看看二范式的设计原则。现在假设业务场景是这样子：有一家超市希望自己的货物可以通过订货的方式发送到客户所在地，并且通过订货单进行货物出售记录。这时我们会需要几张表来完成这个任务？

        先来看一个错误的例子。假设我们的订货单如下：

订货单

字段名	数据类型	说明
customer_id	varchar(50)	客户 ID
customer_name	varchar(50)	客户名字
customer_address	varchar(100)	客户所在地址
customer_phone	varchar(20)	客户电话号码
customer_order	varchar(20)	客户订单号
customer_oder_time	date	订单日期
total_amount	decimal(10,2)	订单总金额

        这会出现一个什么样的问题？我们假设行数据如下：

客户 ID	客户名字	客户所在地址	客户电话号码	客户订单号	订单日期	订单总金额
CUS001	客户一号	xx省xx市xx区xx街道	173****7662	OD001	2023-03-23	4,398
CUS001	客户一号	xx省xx市xx区xx街道	173****7662	OD002	2023-03-24	5,398
CUS002	客户二号	xx省xx市xx区xx街道	136****4432	OD003	2023-03-23	3,356

        我们可以看到CUS001号客户的信息,好像连续出现了两次,这次为什么？因为它连续两天都需要订货。所以这里我们可以看到以日期为基准，出现了客户信息的重复。

        这就会导致大面积数据冗余。比如此时CUS001号客户的电话号码发生了改变，这就会需要对订货表的记录进行逐行更新。这样做就是违背了数据库第二范式的原则。

        所以最好的办法就是将订货表和客户信息拆分开来。比如：

订货表

字段名	数据类型	说明
order_id	varchar(50)	订单ID
customer_id	varchar(50)	客户 ID
order_date	date	订单日期
total_amount	decimal(10,2)	订单总金额

        我们可以看到,客户表的客户ID为主键，其它字段都完全依赖于客户ID。订货表的订单ID为主键，其它字段都完全依赖于订单ID。且也已满足第一范式。数据列都具备了原子性。

        这样设计的好处就在于，避免了数据冗余，提高了数据的一致性和可维护性。

---

第三范式表设计

        通过上面两个范式，我们可以看出来，范式之间是逐层递进的，它们之间存在着一定的层次关系。第二范式= 第一范式的进一步规范化，第三范式=第二范式的进一步规范化。

        以二范式为基准，我们独立拆分了订货表和客户表.如下：

订货表

字段名	数据类型	说明
order_id	varchar(50)	订单ID
customer_id	varchar(50)	客户 ID
order_date	date	订单日期
total_amount	decimal(10,2)	订单总金额

        那么按照实际的业务中，我们可能会还想要知道这个订单对什么物品购买了多少数量。那么为了确保每个表都是独立的、原子的数据项。我们的表该如何进行扩展?

        加表,在我们前面的第一二范式下，表的列已经具备了原子性，也就是不可再分。如果我们需要对表进行扩展，且需要满足第三范式，那么最好的做法就是添加附属信息表。

        例如新增两张表：Order_Items（订单明细表）、Products_Info（产品信息表）

订单明细表

字段名	数据类型	说明
order_id	varchar(50)	订单ID
product_id	varchar(50)	产品ID
product_num	int(11)	订购数量

产品信息表

字段名	数据类型	说明
product_id	varchar(50)	产品ID
product_name	varchar(50)	产品名称
product_create_time	date	产品生产日期
product_end_time	date	产品到期日期
product_price	decimal(10,2)	产品单价

        这样做无疑是符合三范式的要求，每个表都只包含独立的、原子的数据项，没有重复的数据，也没有冗余的数据。

---

总结

        在日常的数据库设计过程中，范式越高就一定越好吗？

        当然不是，范式的递增随之而来的很明显的直观感觉就是，表的拆分变得更细，这会导致我们在查询数据的时候，可能需要多次的Join才能得到最终的数据，这无疑是加大了查询的成本。

        其次就是增加了数据存储、更新、删除的复杂度。若不使用强关联(主外键形式),还有可能导致数据错乱。

        有时候数据的冗余并不会对我们产生多大的影响，比如一些创建人、创建时间、更新人、更新时间这样的字段，冗余是没有问题的。

        所以在实际的表设计过程中，我们需要对数据的复杂度、查询频率、数据未来可能的扩展性等因素去选择合适的范式进行设计。