Understanding Relational Database Normalization: From 1NF to 4NF with Practical MySQL Examples

Relational table design is a core factor affecting the performance of relational databases.

Normalization theory, including the six normal forms (1NF‑5NF), guides how to structure tables to minimize redundancy and improve readability. Typically achieving 3NF or BCNF is sufficient for most applications.

1NF – Atomicity

An example employee table shows a JSON salary column that stores both base and extra salary, violating 1NF. By splitting this column into base_salary and extra_salary , the table satisfies 1NF, though redundancy remains in department fields.

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | YES  |     | NULL    |       |
| employee_name   | varchar(64) | YES  |     | NULL    |       |
| salary          | json        | YES  |     | NULL    |       |
| dept            | varchar(64) | YES  |     | NULL    |       |
| dept_desc       | text        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | YES  |     | NULL    |       |
| employee_name   | varchar(64) | YES  |     | NULL    |       |
| base_salary     | varchar(30) | YES  |     | NULL    |       |
| extra_salary    | varchar(30) | YES  |     | NULL    |       |
| dept            | varchar(64) | YES  |     | NULL    |       |
| dept_desc       | text        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

2NF – Eliminate Partial Dependencies

The student table initially uses a composite primary key (student_id, course) but stores attributes that depend only on part of the key, violating 2NF. Splitting it into three tables—student, course, and enrollment—removes the partial dependencies.

(debian-ytt1:3500)|(ytt)>desc `学生表`;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| 学号            | varchar(64)      | NO   | PRI | NULL    |       |
| 姓名            | varchar(64)      | YES  |     | NULL    |       |
| 年龄            | tinyint unsigned | YES  |     | NULL    |       |
| 性别            | char(1)          | YES  |     | NULL    |       |
| 所属系名称      | varchar(64)      | YES  |     | NULL    |       |
| 系地址          | varchar(100)     | YES  |     | NULL    |       |
| 系电话          | varchar(20)      | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

3NF – Remove Transitive Dependencies

Further analysis shows that department fields depend on the department name, which in turn depends on the student key, creating a transitive dependency. By extracting a separate department table, the student table satisfies 3NF.

(debian-ytt1:3500)|(ytt)>desc `院系表`;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| 系名称   | varchar(64)  | NO   | PRI | NULL    |       |
| 地址   | varchar(100) | YES  |     | NULL    |       |
| 电话   | varchar(20)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

BCNF – Eliminate All Dependencies in Composite Keys

When a student can belong to multiple departments, the composite key (student_id, department_name) creates a dependency that violates BCNF. Introducing a relationship table 学生_系_关系表 resolves the issue.

(debian-ytt1:3500)|(ytt)>desc `学生_系_关系表`;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int unsigned | NO   | PRI | NULL    | auto_increment |
| 学号            | varchar(64)  | YES  |     | NULL    |                |
| 所属系名称      | varchar(64)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

4NF – Remove Multi‑Valued Dependencies

In a multinational company, employees may have both Chinese and English names, leading to a composite primary key (employee_no, employee_name) and duplicated salary data. Splitting the data into three tables—employee, employee_zh, and employee_en—eliminates the redundancy.

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | NO   | PRI | NULL    |       |
| base_salary     | varchar(30) | YES  |     | NULL    |       |
| extra_salary    | varchar(30) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

In practice, strict adherence to the highest normal forms is not always necessary; designers may denormalize for performance reasons, balancing query cost against data redundancy.