Why Database Normalization Matters: A Practical Guide to 1NF, 2NF, and 3NF

Background

A junior relative asked whether learning databases has a future and where to start. The author admits forgetting the details of the three normal forms and decides to look them up, turning the conversation into a practical tutorial.

What is a database normal form?

Normalization provides a set of guidelines for designing relational tables to eliminate redundancy and improve data consistency. The most common forms are the first (1NF), second (2NF), and third (3NF) normal forms.

First Normal Form (1NF)

1NF requires that each column contain atomic, indivisible values. In the example user table, the address column stores both province and city, violating 1NF.

Original table:
| id | name | gender | address |
|----|------|--------|-------------------|
|0001|张三|男|广东省，深圳市|
|0002|李四|女|海南省，海口市|

After 1NF split:
| id | name | gender | province | city |
|----|------|--------|----------|------|
|0001|张三|男|广东省|深圳市|
|0002|李四|女|海南省|海口市|

Second Normal Form (2NF)

2NF demands a single primary key and that every non‑key attribute be fully dependent on that key. An order table that stores product details together with order information violates 2NF because product attributes depend only on the product ID.

Violating table (order number, product number, product name, price):
... (multiple rows for the same order)

To satisfy 2NF, use a composite primary key (order_id, product_id) and move product‑specific columns to a separate product table.

Third Normal Form (3NF)

3NF requires that every non‑key column be directly dependent on the primary key, not through another non‑key column. Adding city population and characteristics to the user table creates an indirect dependency on the city, not the user.

Violating table:
| id | name | gender | city | city_feature | city_population |
|----|------|--------|------|--------------|-----------------|
|0001|张三|男|深圳市|科技、创新|1300W|
|0002|李四|女|海口市|旅游、观光|230W|

After 3NF, move city_feature and city_population to a separate city table linked by city.

Why normalization?

Eliminates redundant data, improving consistency.

Reduces storage cost by avoiding duplicate information.

Denormalization (anti‑normalization)

Denormalization deliberately adds redundancy or aggregates data to boost query performance, at the cost of consistency and added synchronization effort. For example, a JSON document can embed order details, product list, and address in a single record.

{
  "oid": "0001",
  "price": { "total": 380, "benefit": 40 },
  "goods": [
    { "gid": "SN001", "name": "蓝月亮洗衣液", "price": 41, "amount": 2 },
    { "gid": "SN003", "name": "电动剃须刀", "price": 99, "amount": 1 }
  ],
  "address": { "contact": "张三", "phone": "150899000" }
}

This flexible structure is typical of NoSQL document stores such as MongoDB. MySQL added JSON column support in version 5.7, and PostgreSQL has supported it since 9.4.

Beyond the three normal forms, there are higher forms such as BCNF, 4NF, and 5NF.

While denormalization can improve performance, it should be balanced against the need for data integrity; understanding normal forms remains a fundamental skill for good database design.