- 减少数据冗余：想象一下，每次客户下单时都要存储一次客户地址。如果地址变了，你就得在多个地方更新它！规范化可以帮助你把信息存放在一个地方，并从其他地方引用它，从而尽量减少重复。
- 提高数据完整性：更少的冗余意味着更少的不一致。如果你在一个地方更新了地址，那么在所有需要的地方都会同步更新。
- 防止异常：规范化有助于防止以下问题：
- 插入异常：因为缺少相关信息而难以添加新数据。
- 更新异常：不得不在多行中更新相同的信息。
- 删除异常：在删除看似无关的内容时，不小心丢失有价值的信息。
- 更容易理解和维护：规范化后的数据库通常在逻辑结构上更清晰，也更容易理解、查询和修改。
Drizzle 关系基础
在数据库的世界里，尤其是在关系型数据库中，关系的概念绝对是基础性的。 把“关系”想象成不同数据片段之间的连接和联系。就像现实生活中， 人们彼此之间有关系，或者物体与类别相关联一样，数据库使用关系来建模不同 类型的信息是如何相互连接并协同工作的。
规范化
规范化是组织数据库中数据以减少冗余（重复）并提高数据完整性（准确性和一致性）的过程。可以把它想象成整理一个杂乱的文件柜。你不会把各种各样的文件都塞进一个文件夹里，而是会把它们按逻辑分类到不同的文件夹和类别中，这样更容易查找和管理。
为什么规范化很重要？
规范化通常用“范式”来描述（1NF、2NF、3NF 以及更高范式）。虽然细节可能会变得相当技术化，但核心思想很简单：
1NF（第一范式）：原子值
目标：每一列都应该只包含一个不可再分的值。单个单元格内不应出现重复组数据。
示例：与其使用一个 address 列存储 123 Main St, City, USA，不如把它拆分成多个独立列：street_address、city、state、zip_code。
-- 未规范化（违反 1NF）
CREATE TABLE "Customers_Unnormalized" (
"customer_id" INT4 PRIMARY KEY,
"name" VARCHAR(255),
"address" VARCHAR(255) -- 问题：一个列中包含多条信息
);
-- 规范化为 1NF
CREATE TABLE "Customers_1NF" (
"customer_id" INT4 PRIMARY KEY,
"name" VARCHAR(255),
"street_address" VARCHAR(255),
"city" VARCHAR(255),
"state" VARCHAR(255),
"zip_code" VARCHAR(10)
);2NF（第二范式）：消除依赖于主键部分的冗余数据
目标：适用于你有一个复合主键的表（由两个或多个列组成的主键）。
2NF 确保所有非键属性都完全依赖于整个复合主键，而不仅仅是它的一部分。
假设我们有一个名为 order_items 的表。这个表用于跟踪订单中的商品，我们使用复合主键（order_id、product_id），因为一个订单可能包含同一种商品的多个数量（不过在这个简化示例中，先假设每个订单里每种商品只出现一次以便说明，但复合键逻辑仍然适用）。
展开查看可视化示例
CREATE TABLE "OrderItems_Unnormalized" (
"order_id" INT4,
"product_id" VARCHAR(10),
"product_name" VARCHAR(100),
"product_price" DECIMAL(10, 2),
"quantity" INT4,
"order_date" DATE,
PRIMARY KEY ("order_id", "product_id") -- 复合主键
);
INSERT INTO "OrderItems_Unnormalized" ("order_id", "product_id", "product_name", "product_price", "quantity", "order_date") VALUES
(101, 'A123', 'Laptop', 1200.00, 1, '2023-10-27'),
(101, 'B456', 'Mouse', 25.00, 2, '2023-10-27'),
(102, 'A123', 'Laptop', 1200.00, 1, '2023-10-28'),
(103, 'C789', 'Keyboard', 75.00, 1, '2023-10-29');+------------------------------------------------------------------------------------+
| OrderItems_Unnormalized |
+------------------------------------------------------------------------------------+
| PK (order_id, product_id) | product_name | product_price | quantity | order_date |
+------------------------------------------------------------------------------------+
| 101, A123 | Laptop | 1200.00 | 1 | 2023-10-27 |
| 101, B456 | Mouse | 25.00 | 2 | 2023-10-27 |
| 102, A123 | Laptop | 1200.00 | 1 | 2023-10-28 |
| 103, C789 | Keyboard | 75.00 | 1 | 2023-10-29 |
+------------------------------------------------------------------------------------+问题：注意到当相同的 product_id 出现在不同订单中时，product_name 和 product_price 会重复出现。
这些属性只依赖于 product_id，而 product_id 只是复合主键（order_id、product_id）的一部分，并不是整个主键。
这就是部分依赖。
要实现 2NF，我们需要移除这些部分依赖的属性（product_name、product_price），并将它们放入一个单独的表中，使它们完全依赖于新表的主键。
规范化到 2NF：可视化说明
+-------------------+ 1:M +---------------------------+
| Products | <---------- | OrderItems_2NF |
+-------------------+ +---------------------------+
| PK product_id | | PK (order_id, product_id) |
| product_name | | quantity |
| product_price | | order_date |
+-------------------+ | FK product_id |
+---------------------------+CREATE TABLE "Products" (
"product_id" VARCHAR(10) PRIMARY KEY,
"product_name" VARCHAR(100),
"product_price" DECIMAL(10, 2)
);
CREATE TABLE "OrderItems_2NF" (
"order_id" INT4,
"product_id" VARCHAR(10),
"quantity" INT4,
"order_date" DATE,
PRIMARY KEY ("order_id", "product_id"), -- 复合主键保持不变
FOREIGN KEY ("product_id") REFERENCES "Products"("product_id") -- 指向 Products 的外键
);
-- 将数据插入 Products
INSERT INTO "Products" ("product_id", "product_name", "product_price") VALUES
('A123', 'Laptop', 1200.00),
('B456', 'Mouse', 25.00),
('C789', 'Keyboard', 75.00);
-- 将数据插入 OrderItems_2NF（引用 Products）
INSERT INTO "OrderItems_2NF" ("order_id", "product_id", "quantity", "order_date") VALUES
(101, 'A123', 1, '2023-10-27'),
(101, 'B456', 2, '2023-10-27'),
(102, 'A123', 1, '2023-10-28'),
(103, 'C789', 1, '2023-10-29');3NF（第三范式）：消除依赖于非键属性的冗余数据
目标：移除依赖于其他非键属性的数据。这是为了消除传递依赖。
问题：假设我们有一张 suppliers 表。我们存储供应商信息，包括他们的 zip_code、city 和 state。supplier_id 是主键。
CREATE TABLE "suppliers" (
"supplier_id" VARCHAR(10) PRIMARY KEY,
"supplier_name" VARCHAR(255),
"zip_code" VARCHAR(10),
"city" VARCHAR(100),
"state" VARCHAR(50)
);
INSERT INTO "suppliers" ("supplier_id", "supplier_name", "zip_code", "city", "state") VALUES
('S1', 'Acme Corp', '12345', 'Anytown', 'NY'),
('S2', 'Beta Inc', '67890', 'Otherville', 'CA'),
('S3', 'Gamma Ltd', '12345', 'Anytown', 'NY');+---------------------------------------------------------------+
| suppliers |
+---------------------------------------------------------------+
| PK supplier_id | supplier_name | zip_code | city | state |
+---------------------------------------------------------------+
| S1 | Acme Corp | 12345 | Anytown | NY |
| S2 | Beta Inc | 67890 | Otherville | CA |
| S3 | Gamma Ltd | 12345 | Anytown | NY |
+---------------------------------------------------------------+解决方案：要实现 3NF，我们移除依赖于非键属性的属性（city、state 依赖于 zip_code），并将它们放入一个以该非键属性本身（zip_code）为键的单独表中。
规范化到 3NF：可视化说明
+-------------------+ 1:M +--------------------+
| zip_codes | <---------- | suppliers |
+-------------------+ +--------------------+
| PK zip_code | | PK supplier_id |
| city | | supplier_name |
| state | | FK zip_code |
+-------------------+ +--------------------+CREATE TABLE "zip_codes" (
"zip_code" VARCHAR(10) PRIMARY KEY,
"city" VARCHAR(100),
"state" VARCHAR(50)
);
CREATE TABLE "suppliers" (
"supplier_id" VARCHAR(10) PRIMARY KEY,
"supplier_name" VARCHAR(255),
"zip_code" VARCHAR(10), -- 指向 zip_codes 的外键
FOREIGN KEY ("zip_code") REFERENCES "zip_codes"("zip_code")
);
-- 将数据插入 zip_codes
INSERT INTO "zip_codes" ("zip_code", "city", "state") VALUES
('12345', 'Anytown', 'NY'),
('67890', 'Otherville', 'CA');
-- 将数据插入 suppliers（引用 zip_codes）
INSERT INTO "suppliers" ("supplier_id", "supplier_name", "zip_code") VALUES
('S1', 'Acme Corp', '12345'),
('S2', 'Beta Inc', '67890'),
('S3', 'Gamma Ltd', '12345');你应该知道
还有其他范式，例如 4NF、5NF、6NF、EKNF、ETNF 和 DKNF。这里不会介绍这些内容，但我们会在指南和教程部分为它们创建一套专门的教程。
数据库关系
一对一
在一对一关系中，table A 中的每条记录最多只与 table B 中的一条记录相关联，而 table B 中的每条记录也最多只与 table A 中的一条记录相关联。这是一种非常直接、互斥的配对关系。
用例与示例
- 用户资料和用户账户详情：想象一个网站。每个用户账户（在 Users 表中）可能恰好有一个用户资料（在 UserProfiles 表中），其中包含更详细的信息。
- 员工和停车位：一个 Employees 表和一个 ParkingSpaces 表。每位员工可能最多分配一个停车位，而每个停车位最多只分配给一位员工。
- 为了组织而拆分表：有时，你可能会为了更好的组织或安全性，将一个非常宽的表拆分成两个表，并保持它们之间的一对一关系。
Table A (One Side) Table B (One Side)
+---------+ +---------+
| PK (A) | <---------> | FK (A) | (引用 Table A 的外键)
| ... | | ... |
+---------+ +---------+一对多
在一对多关系中，table A 中的一条记录可以与 table B 中的多条记录相关联，但 table B 中的每条记录最多只与 table A 中的一条记录相关联。可以把它看作一种“父子”关系。
用例与示例
- 客户和订单：一个客户可以下很多订单，但每个订单只属于一个客户。
- 作者和书籍：一位作者可以写很多书，但（这里先简化一下，认为）每本书都由一位主要作者撰写。
- 部门和员工：一个部门可以有很多员工，但每位员工只属于一个部门。
Table A (One Side) Table B (Many Side)
+---------+ +---------+
| PK (A) | ----------> | FK (A) | (引用 Table A 的外键)
| ... | | ... |
+---------+ +---------+
(One) (Many)多对多
在多对多关系中，table A 中的一条记录可以与 table B 中的多条记录相关联，而 table B 中的一条记录也可以与 table A 中的多条记录相关联。这是一种更复杂、双向的关系。
用例与示例
- 学生和课程：一个学生可以选修多门课程，而一门课程也可以有很多学生选修。
- 产品和分类：一个产品可以属于多个分类（例如，“T 恤”可以同时属于“服装”和“夏季服饰”分类），而一个分类可以包含很多产品。
- 作者和书籍：一本书可以由多位作者共同撰写，而一位作者也可以写多本书。
Table A (Many Side) Junction Table Table B (Many Side)
+---------+ +-------------+ +---------+
| PK (A) | -------->| FK (A) | <----| FK (B) |
| ... | | FK (B) | | ... |
+---------+ +-------------+ +---------+
(Many) (Junction) (Many)多对多关系不能直接通过两个主表之间的外键来实现。
相反，你需要一个 junction 表（也称为关联表或桥接表）。
这个表作为中介，将两个表中的记录连接起来。
-- 学生表（多的一侧）
CREATE TABLE "students" (
"id" INT4 PRIMARY KEY,
"name" VARCHAR(255)
);
-- 课程表（多的一侧）
CREATE TABLE "courses" (
"id" INT4 PRIMARY KEY,
"name" VARCHAR(255),
"credits" INT4
);
-- 连接表：enrollments（连接 students 和 courses - M-M 关系）
CREATE TABLE "enrollments" (
"id" INT4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 可选，但对于连接表来说是个好做法
"student_id" INT4,
"course_id" INT4,
"enrollment_date" DATE,
-- 复合外键（通常是复合主键或唯一约束的一部分）
FOREIGN KEY ("student_id") REFERENCES "students"("id"),
FOREIGN KEY ("course_id") REFERENCES "courses"("id"),
UNIQUE ("student_id", "course_id") -- 防止同一学生和课程的重复选课记录
);为什么需要外键？
你可能会把外键约束仅仅看作一种验证数据的方式——确保当你在外键列中输入一个值时，这个值确实存在于另一张表的主键列中。你说得部分正确！这种值检查就是外键所使用的机制。
但关键在于要理解，这种验证并不是最终目标，而只是通向更大目的的手段。外键约束从根本上是为了：
1. 明确定义并强制执行关系
我们已经讨论过 Customers 和 Orders 之间像 一对多 这样的关系。外键是 SQL 语言告诉数据库的方式：
嘿，数据库，我想在这里强制执行一个 1-M 关系。Orders 表中 customer_id 列里的每一个值都必须对应 Customers 表中一个有效的 customer_id。
这不仅仅是一个建议；它是数据库会主动执行的一种约束。由于外键的存在，数据库会具备关系感知能力。
2. 维护引用完整性
- 这是关系语境中“数据完整性”的核心。引用完整性意味着表之间的关系会随着时间保持一致和有效。
- 外键可以防止孤儿记录。什么是孤儿记录？在我们的 Customer-Order 示例中，Orders 表里存在的一条订单记录，如果在 Customers 表中没有对应的客户，那它就是一个孤儿。外键可以防止这种情况发生（或者控制当你尝试删除一个有订单的客户时会发生什么——通过 CASCADE、SET NULL 等）。
- 为什么防止孤儿记录很重要？孤儿记录会破坏数据的逻辑结构。如果你有一笔没有客户的订单，你就会失去关键上下文。查询会变得不可靠，报表会变得不准确，应用程序的逻辑也可能因此崩溃。
示例：
如果没有外键，你可能会不小心从 Customers 表中删除一个客户，而他们的订单仍然存在于 Orders 表中。突然之间，你就有了指向一个已不存在客户的订单！外键约束可以防止这种数据不一致。3. 促进数据库设计与理解
- 外键不仅关乎技术上的强制执行；它们也是数据库设计文档的重要组成部分。
- 当你在数据库模式中看到一个外键时，它会立刻告诉你：
表 'X' 以这种方式与表 'Y' 相关联。这是关系的清晰视觉和结构化指示。 - 这让数据库更容易理解、维护，并随着时间推移不断演进。新开发人员可以快速掌握数据库不同部分之间是如何连接的。
本质上，外键约束不仅仅是为了检查值；它们是为了：
- 定义数据关系的规则
- 在数据库层面主动强制执行这些规则
- 保证这些关系中的数据完整性和一致性
- 让你的数据库更健壮、更可靠，也更易于理解
为什么不使用外键？
虽然外键非常有益，但在某些场景下，你可能会重新考虑是否使用外键，或者谨慎使用外键。 这些通常是边缘情况，并且往往涉及权衡取舍。
1. 高写入环境中的性能开销
- 场景：超高吞吐量的事务系统（例如，实时日志记录、超高频交易平台、大规模 IoT 数据摄取）。
- 解释：每次你在带有外键的表中插入或更新数据时，数据库系统都需要执行检查，以确保引用完整性。在极端高写入场景下，这些检查可能会带来轻微但可能可察觉的性能开销。
2. 分布式数据库系统与跨节点外键：
- 场景：数据分布在多个数据库节点或集群中的系统（在分片数据库、云环境和微服务中很常见）。
- 解释： 跨节点外键可能会引入显著的复杂性和性能开销。验证引用完整性需要节点之间通信，从而导致延迟增加。为维持一致性而需要的分布式事务也更加复杂，并且可能比本地事务性能更差。在这种架构下，可以考虑使用应用层数据完整性检查或最终一致性模型作为替代方案。
3. 旧系统和与非关系型数据的集成：
- 场景：将关系型数据库与较旧的遗留系统或非关系型数据存储（例如 NoSQL、平面文件、外部 API）进行集成。
- 解释： 遗留系统或非关系型数据可能无法始终遵循外键所强制执行的引用完整性规则。在这种情况下强加外键可能会导致数据导入问题、数据不一致，并且可能需要复杂的数据转换或改为由应用层管理完整性。你可能需要仔细评估外部数据源的数据质量和一致性，并且可能依赖应用逻辑或 ETL 流程来确保数据完整性，而不是在数据库层面严格强制外键。
你还可以查看 PlanetScale 团队在他们的文章中的一些精彩解释
多态关系
多态关系是一个更高级的概念，它允许单个关系指向不同类型的实体或表。当你有一些具有共同点但类型不同的数据时，它可以帮助你创建更灵活、更适应变化的关系。
想象一下你有一个 activities 日志。一个 activity 可能是 comment、like 或 share。
每一种 activity 类型都有不同的细节。与其为每种 activity 类型及其关联对象分别创建独立的表和关系，不如使用多态方式。
常见场景与示例
- 评论/评价：一个 “Comment” 可能与不同类型的内容相关：文章、产品、视频等。
与其在 Comments 表中分别设置 article_id、product_id、video_id 列，不如使用多态关系。
+---------------------+
| **Comments** |
+---------------------+
| PK comment_id |
| commentable_type | ------> [多态关系]
| commentable_id | -------->
| user_id |
| comment_text |
| ... |
+---------------------+
^
|
+---------------------+ +---------------------+ +---------------------+
| **Articles** | | **Products** | | **Videos** |
+---------------------+ +---------------------+ +---------------------+
| PK article_id | | PK product_id | | PK video_id |
| ... | | ... | | ... |
+---------------------+ +---------------------+ +---------------------+- 通知： 一个通知可能与用户、订单、系统事件等相关。
+----------------------+
| **Notifications** |
+----------------------+
| PK notification_id |
| notifiable_type | ------> [多态关系]
| notifiable_id | -------->
| user_id |
| message |
| ... |
+----------------------+
^
|
+---------------------+ +---------------------+ +-----------------------+
| **Users** | | **Orders** | | **System Events** |
+---------------------+ +---------------------+ +-----------------------+
| PK user_id | | PK order_id | | PK event_id |
| ... | | ... | | ... |
+---------------------+ +---------------------+ +-----------------------+多态关系更复杂，通常在应用层处理，或者使用更高级的数据库特性（取决于具体的数据库系统）。标准 SQL 并不直接内置支持像普通外键那样，去强制执行多态外键约束。