当前位置：首页 > article >正文

MySQL表设计（三大范式表的设计）

article 2024/11/6 23:10:51

1.上讲约束复习：

1.NOT NULL 非空约束，被指定NOT NULL的列，值不允许为空(必填)

2. UNIQUE 唯一约束，这个列里的值在表中是唯一的，也就是说不能重复

3. PRIMARY KEY 主键约束，可以看做是NOT NULL和UNIQUE的组合可以用一个bigint类型列为表单独指定主键，同时也可以指定主键自增，用auto increment关键字自增操作时，会找到当前列中最大的值，然后在这个基础上加1(+1)

4.DEFAULT 默认约束，如果写入一行记录的时候，指定了默认约束的列，如果在写入数据时没有指定一个确定的值就会用默认值进行填充该字段的值

5.FOREIGN KEY 外键约束，一张表要与另一张表的主键或唯一键进行关键，说明表与表之间的关联关系

2.三大范式

1.范式

数据库的范式是⼀组规则。在设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，⼜称完美范式），越⾼的范式数据库冗余越⼩。然⽽，普遍认为范式越⾼虽然对数据关系有更好的约束性，但也可能导致数据库IO更繁忙，因此在实际应⽤中，数据库设计通常只需满⾜第三范式即可。

2.第一范式确保每个字段的原子性

定义：
• 数据库表的每⼀列都是不可分割的原⼦数据项，⽽不能是集合，数组，对象等⾮原⼦数据。

• 在关系型数据库的设计中，满⾜第⼀范式是对关系模式的基本要求。不满⾜第⼀范式的数据库就不能被称为关系数据库

要求：

每个字段只能包含一个值（没有重复组）。
表中的每一行应当是唯一的，可以通过主键进行标识。

使用：

第一范式要求每个字段（列）只能有一个值，不能是多个值的集合。

不符合第一范式的设计：

假设我们一开始设计了一个学生表，课程 字段存储了多个课程：

插入数据时，可以看到 courses 列存储了多个课程：

我们可以看出这个设计存在问题，courses 列可以储存多个值，违反了第一范式。我们应该将每个课程单独存储。

符合第一范式的设计：

为了遵循第一范式，我们需要将课程数据拆成每个学生每门课程一行：

学生表：

课程表：

然后插入数据：

这样，每个课程都拆成了单独的一行，符合第一范式。

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

定义：
在满⾜第⼀范式的基础上，不存在⾮关键字段对任意候选键的部分函数依赖。存在于表中定义了复合主键的情况下。

候选键：可以唯⼀标识⼀⾏数据的列或列的组合，可以从候选键中选⼀个或多个当做表的主键

要求：

表必须符合第一范式（1NF）。
所有非主键字段必须完全依赖于主键，而不是依赖于主键的一部分。
部分依赖是指，当主键由多个字段组成时，某些非主键字段仅依赖于主键的其中一部分，而不是整个主键。
问题：

当我们使用复合主键时，如果某些列只依赖于主键的一部分而不是整个复合主键，那么这种依赖就叫做“部分依赖”，这违反了第二范式。

不符合第二范式的设计：

假设我们设计了一个订单表，其中 订单号 和 商品ID 组成复合主键：

假设插入以下数据：

解释：

主键：(order_id, product_id)，这个复合主键的意思是每个订单（order_id）包含多个商品（product_id），每个商品的数量（quantity）将会是订单明细的一部分。
然而，product_name 字段并不依赖于 order_id（订单号），它仅依赖于 product_id（商品ID）。也就是说，product_name 是与 product_id 紧密相关的，但不依赖于 order_id。

具体来说：

订单号 (order_id) 决定了订单的唯一性。
商品ID (product_id) 决定了商品的唯一性。
但商品的名字（product_name）只与商品ID (product_id) 相关，而与订单号 (order_id) 无关。

因此，product_name 并没有完全依赖于整个复合主键（order_id 和 product_id），它只是部分依赖于 product_id。这就违反了第二范式的要求。

为什么这违反了第二范式？

根据第二范式的要求，所有非主键字段必须完全依赖于复合主键，而不能只依赖于主键的一部分。在我们的设计中，product_name 只依赖于 product_id，而不是完整的复合主键 (order_id, product_id)。因此，product_name 存在部分依赖，违反了第二范式。

符合第二范式的设计：

为了使表符合第二范式，我们需要消除部分依赖。具体来说，我们可以将 product_name 字段移到一个单独的表中，专门存储商品信息，这样 product_name 就会只依赖于 product_id，而不是依赖于复合主键 (order_id, product_id)。

新的设计方案：

订单表 (orders)：存储订单信息。

2.商品表 (products)：存储商品信息，每个商品有一个 product_id 和 product_name。

3.订单明细表 (order_items)：存储每个订单的商品信息。主键依然是 (order_id, product_id)，但是商品信息不再在此表中存储，而是通过 product_id 关联到 products 表。

数据插入示例：

插入订单数据：

插入商品数据：

插入订单明细数据：

解释：

product_name 现在存储在 products 表中，且只依赖于 product_id。它不再依赖于复合主键 (order_id, product_id)。
order_items 表只存储每个订单的商品和数量，不再包含商品名称，商品名称通过 product_id 关联到 products 表中。

通过这种设计：

product_name 不再存在部分依赖，它完全依赖于 product_id。
order_items 表现在只存储与订单相关的信息，完全符合第二范式。

不满⾜第⼆范式时可能出现的问题

1. 数据冗余

学⽣的姓名、年龄、性别和课程的学分在每⾏记录中重复出现，造成了⼤量的数据冗余。

2. 更新异常

如果要调整MySQL的学分，那么就需要更新表中所有关于MySQL的记录，⼀旦执⾏中断导致某些记录更新成功，某些数据更新失败，就会造成表中同⼀⻔课程出现不同学分的情况，出现数据不⼀致问题。

3. 插⼊异常

⽬前这样的设计，成绩与每⼀⻔课和学⽣都有对应关系，也就是说只有学⽣参加选修课程考试取得了成绩才能⽣成⼀条记录。当有⼀⻔新课还没有学⽣参加考试取得成绩之前，那么这⻔新课在数据库中是不存在的，因为成绩为空时记录没有意义。

4. 删除异常

把毕业学⽣的考试数据全都删除，此时课程和学分的信息也会被删除掉，有可能导致⼀段时间内，数据库⾥没有某⻔课程和学分的信息。

5.解决方案：

将依赖于主键一部分的字段（如 product_name）移动到另一个独立的表中，确保每个非主键字段都完全依赖于整个复合主键。

第三范式（3NF）：避免不必要的间接依赖

什么是第三范式？
第三范式要求数据表符合第二范式，并且消除传递依赖。传递依赖是指：如果字段A依赖于字段B，字段B又依赖于字段C，那么字段A间接依赖于字段C。为了避免这种情况，我们需要确保每个字段只直接依赖于主键。

第三范式（3NF）的要求：

表必须符合第二范式（2NF），即消除部分依赖。
消除传递依赖：如果一个非主键字段依赖于另一个非主键字段，那么这就是传递依赖。第三范式要求非主键字段只能直接依赖于主键，而不能间接依赖于主键。

什么是传递依赖？

假设有一个表，表中的字段依赖于主键的某些其他字段，而这些字段并不直接依赖于主键本身，而是通过其他非主键字段间接依赖。这种依赖称为传递依赖。

不符合第三范式的设计示例：

假设我们设计了一个员工表（employees），包含 部门编号 和 部门名称 两个字段。

插入数据：

为什么这个设计违反了第三范式？

传递依赖的存在：
- employee_id 是主键，它唯一标识每一行。
- department_id 依赖于 employee_id，而 department_name 依赖于 department_id。因此，department_name 通过 department_id 间接依赖于主键 employee_id。
- 问题：department_name 不应该依赖于 department_id，而是应该依赖于 department_id 的表。我们应该消除这种间接依赖，确保所有的非主键字段只直接依赖于主键。

这种情况下，department_name 是通过 department_id 间接依赖于主键 employee_id，这就是传递依赖。根据第三范式，非主键字段（department_name）应当直接依赖于主键（employee_id），而不是依赖于其他非主键字段。

符合第三范式的设计：

为了消除传递依赖，我们可以将 department_name 字段移动到一个独立的表中，专门存储部门信息。这样，department_name 就会直接依赖于 department_id，而不会通过 employee_id 间接依赖。

新的设计方案：

员工表 (employees)：只存储与员工相关的信息，去掉 department_name 字段。

2.部门表 (departments)：存储部门信息，每个部门有 department_id 和 department_name。

数据插入示例：

插入部门数据：

插入员工数据：

解释：

在 employees 表中，department_id 作为外键引用了 departments 表中的 department_id，不再存储 department_name。
departments 表专门存储了部门信息，department_name 直接依赖于 department_id，而 department_id 在 employees 表中作为外键，依赖于 employee_id。

通过这种方式，department_name 不再依赖于 employee_id，而是直接依赖于 department_id，从而消除了传递依赖，符合第三范式。

总结：

传递依赖：在原设计中，department_name 通过 department_id 间接依赖于主键 employee_id，这就是传递依赖。
符合第三范式的设计：将 department_name 移到独立的部门表中，department_name 直接依赖于 department_id，而 employee_id 只与员工信息相关，消除了传递依赖，符合第三范式。

通过这样的调整，表结构更加简洁，避免了数据冗余和更新异常，提高了数据库的一致性和完整性。