Skip to content

Normalization

Reduce redundancy and anomalies 使用 1NF, 2NF, 3NF, and BCNF normal 表单.

你将学到

  • 什么 normalization 是 and problems 它解决
  • 首先 Normal 表单 (1NF) — atomic 值 and 否 repeating 分组
  • 其次 Normal 表单 (2NF) — 否部分 dependencies
  • 第三 Normal 表单 (3NF) — 否 transitive dependencies
  • 当到 denormalize for 性能

概念讲解

为什么要规范化?

Normalization is the process of organizing tables and columns to reduce data redundancy and eliminate anomalies — insertion, update, and deletion anomalies. An update anomaly occurs when the same fact is stored in multiple places and you update one but forget the others. Normalization ensures each fact is stored in exactly one place.

First Normal Form (1NF)

A table is in 1NF if every column contains atomic (indivisible) values and there are no repeating groups or arrays. A 用户 table with a phone_numbers column containing "555-1234, 555-5678" violates 1NF because the value is not atomic. The fix is to create a separate user_phones table with one row per phone number.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and every non-key column depends on the entire primary key, not just part of it. This only matters for tables with composite primary keys. Example: an order_items table with key (order_id, product_id) should not store product_name — that depends only on product_id, not the full key. Move product_name to a products table.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column (no transitive dependencies). Example: an employees table with department_id and department_name — department_name depends on department_id, not on the employee key. Move department_name to a departments table.

When to Denormalize

Fully normalized schemas are correct but can require many joins for common queries. In read-heavy systems (analytics, reporting, caching), it is sometimes worth denormalizing — duplicating data to avoid joins. This is a conscious trade-off: faster reads at the cost of more complex writes and the risk of inconsistency. Denormalize only when you have measured a real performance problem.

示例代码

              -- BEFORE: unnormalized table (violates 1NF, 2NF, 3NF)
-- order_id | customer_name | customer_city | items (CSV)

-- AFTER: normalized into 3NF
CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  city  VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date  DATE NOT NULL
);

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);
            

unnormalized 单个表 是拆分到 four 3NF 表: customers, 顺序, products, and junction 表 order_items.

动手试一试

常见错误

错误写法

Over-normalizing 到点 的需要 10 连接 for 简单查询

正确做法

3NF 是 practical sweet spot. Higher normal 表单 (BCNF, 4NF, 5NF) 是很少需要. 如果查询需要也 许多连接, consider selective denormalization.

错误写法

存储 derived 数据 in normalized 表

正确做法

做不 存储 order_total in 顺序表 如果它 能是 computed 从 order_items. 存储它 仅如果你 denormalize for 读取性能 and keep 它 in sync.

练习 Normalization

3 个练习

练习 Normalization

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误