MySQL 表设计的核心原则

不知道你还记得不，大学的数据库理论课程中就介绍了数据库设计的三个范式（1NF → 2NF → 3NF），这些范式可以消除数据冗余、减少更新 / 插入 / 删除异常，规范表结构，层层递进，满足后序范式必须先满足前面所有范式。

注意：我们日常开发中不一定必须满足这些范式。理论上建议尽量贴近 3NF，但实际开发不是强制遵守，很多场景主动反范式，打破范式可能更优，至少对于开发而言是如此。

下面就开始逐一介绍这些范式：

第一范式：原子性（1NF）

第一范式要求每个字段都是不可再分的原子值，不能是集合、数组、多值拼接。

违反第一范式的例子：

-- 不好的设计：联系方式字段包含多个信息
mysql> CREATE TABLE bad_contact (
    ->     user_id INT PRIMARY KEY COMMENT '用户ID',
    ->     name VARCHAR(50) COMMENT '姓名',
    ->     contact VARCHAR(200) COMMENT '联系方式，存储：13800138000,张三@qq.com,北京市'
    -> );
Query OK, 0 rows affected (0.01 sec)

符合第一范式的设计：

-- 好的设计：每个字段只存储一种信息
mysql> CREATE TABLE good_contact (
    ->     user_id INT PRIMARY KEY COMMENT '用户ID',
    ->     name VARCHAR(50) COMMENT '姓名',
    ->     phone VARCHAR(20) COMMENT '电话',
    ->     email VARCHAR(100) COMMENT '邮箱',
    ->     address VARCHAR(200) COMMENT '地址'
    -> );
Query OK, 0 rows affected (0.01 sec)

上面示例，good_contact 表明显更好，但是也有些情况下 bad_contact 设计更优，例如：设计一张存放部门信息的表，可以使用一个字段存储部门的全路径，如“XXX集团/XXX子公司/XXX部门”，查询时更友好。

第二范式：消除部分依赖（2NF）

前提：必须先满足 1NF，即第一范式。

第二范式要求非主键字段必须完全依赖于整个主键，而不是主键的一部分。这主要针对联合主键的情况。

违反第二范式的例子：

-- 选课表，联合主键(学生ID, 课程ID)
mysql> CREATE TABLE bad_score (
    ->     student_id INT,
    ->     course_id INT,
    ->     student_name VARCHAR(50),  -- 只依赖student_id
    ->     course_name VARCHAR(100),  -- 只依赖course_id
    ->     score DECIMAL(5,2),
    ->     PRIMARY KEY (student_id, course_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

这里 student_name 只依赖于 student_id，course_name 只依赖于 course_id，这就是部分依赖。

符合第二范式的设计：

-- 拆分为三个表
mysql> CREATE TABLE students (
    ->     student_id INT PRIMARY KEY,
    ->     student_name VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE courses (
    ->     course_id INT PRIMARY KEY,
    ->     course_name VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE scores (
    ->     student_id INT,
    ->     course_id INT,
    ->     score DECIMAL(5,2),
    ->     PRIMARY KEY (student_id, course_id),
    ->     FOREIGN KEY (student_id) REFERENCES students(student_id),
    ->     FOREIGN KEY (course_id) REFERENCES courses(course_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

在实际开发中，很少使用外键 foreign key。

mysql 广告位

第三范式：消除传递依赖（3NF）

前提：满足 1NF、2NF 范式

第三范式要求非主键字段直接依赖于主键，而不是通过其他非主键字段传递依赖。

违反第三范式的例子：

-- 学生表
mysql> CREATE TABLE bad_student (
    ->     student_id INT PRIMARY KEY,
    ->     student_name VARCHAR(50),
    ->     class_id INT,
    ->     class_name VARCHAR(50),  -- 依赖class_id，而不是直接依赖student_id
    ->     department_name VARCHAR(50)  -- 依赖class_id，间接依赖student_id
    -> );
Query OK, 0 rows affected (0.01 sec)

这里 class_name 和 department_name 都依赖于 class_id，而 class_id 依赖于 student_id，这就是传递依赖。

符合第三范式的设计：

-- 创建部门表
mysql> CREATE TABLE departments (
    ->     department_id INT PRIMARY KEY,
    ->     department_name VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 创建班级表
mysql> CREATE TABLE classes (
    ->     class_id INT PRIMARY KEY,
    ->     class_name VARCHAR(50),
    ->     department_id INT,
    ->     FOREIGN KEY (department_id) REFERENCES departments(department_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 创建学生表
mysql> CREATE TABLE students (
    ->     student_id INT PRIMARY KEY,
    ->     student_name VARCHAR(50),
    ->     class_id INT,
    ->     FOREIGN KEY (class_id) REFERENCES classes(class_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

上面三张表表示的关系如下所示：

1个部门
    ├─ 班级1
        ├─ 学生1
        ├─ 学生2
    ├─ 班级2
        ├─ 学生3

反范式设计

范式化能减少数据冗余，但过度范式化会导致表过多、查询复杂。实际项目中常常需要反范式设计。

反范式的场景：

-- 订单表：冗余存储商品名称和价格（快照）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    product_name VARCHAR(200),  -- 冗余：商品可能改名，但订单应保留当时名称
    price DECIMAL(10,2),        -- 冗余：商品价格会变，订单应保留当时价格
    quantity INT,
    total_amount DECIMAL(12,2),
    order_time TIMESTAMP
);

这种冗余是合理的，因为：

1. 订单数据是历史数据，不应该随商品信息变化而变化

2. 避免了每次查询都要JOIN商品表

3. 即使商品被删除，订单记录仍然完整

反范式的原则：

• 以查询性能为主要考虑

• 冗余的数据应该稳定或者不常变化

• 要有数据同步机制（触发器或应用层维护）

 更多知识请阅读后续章节……谢谢！！！