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.
动手试一试
- SQL Formatter
格式 normalized Schema 定义.
- JSON 到 SQL
Convert flat JSON 到 normalized SQL 表.
常见错误
错误写法
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 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。