Database Normalization (1NF, 2NF, 3NF)

In relational database design, the quality of data organization directly determines system performance, maintenance costs, and data consistency. To eliminate redundant information and prevent update anomalies, the academic and industrial communities introduced the concept of "Normal Forms." Normal form design is not a single standard but a strict hierarchical rule set; from the First Normal Form (1NF) to the Third Normal Form (3NF), each level aims to resolve data issues left over from the previous stage. Understanding these norms is the cornerstone of building efficient and reliable database architectures.
The First Normal Form (1NF) is the most fundamental condition that must be met, with its core requirement being that "every cell in a table must contain an indivisible single value." In early relational models, it was permissible to store multiple values within a single field (e.g., comma-separated strings), which logically contradicted the definition of a relation. Violating 1NF leads to difficult querying, data that is hard to sort, and complex aggregation operations that cannot be executed.

The key step to implementing 1NF is to decompose all repeating groups into independent rows and columns. Suppose we have an Orders table where the "Contact Numbers" field stores a string list of multiple phone numbers; this violates 1NF. The correct approach is to establish a new associative table (e.g., Customer_Phone) and store each phone number in a single cell of a specific row.

-- Example violating 1NF: Repeating groups exist
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    ContactNumbers TEXT -- Incorrect: May contain multiple values, e.g., "13800138000, 13900139000"
);

-- Corrected solution adhering to 1NF: Atomicity guaranteed
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    ContactPhone VARCHAR(20) -- Correct: Only one value per row
);

CREATE TABLE PhoneDetails (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    PhoneNumber VARCHAR(20),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

Second Normal Form: Elimination of Partial Dependency and Full Key Constraints

After a table satisfies the First Normal Form, we move on to consider the Second Normal Form (2NF). 2NF applies to all non-prime attribute relations in a relation schema, with its core requirement being that "there are no partial functional dependencies of non-prime attributes on candidate keys." This means if a composite primary key consists of two or more fields, all other columns must depend fully on the entire combination and cannot depend on only one part of it.

For example, in a table containing StudentID, ClassID, and Grade, if (StudentID, ClassID) is a compound primary key and Grade depends only on ClassID (because students in the same class have the same grade), this constitutes a partial dependency on the candidate key. This design leads to the need to update a large number of records when class information changes,极易引发数据不一致或“删除异常” (easily triggering data inconsistency or "delete anomalies").

The method to resolve partial dependency is table splitting: extract attributes that fully depend on only one part of the primary key into new tables.

-- Example violating 2NF: Partial dependency exists (Grade depends only on ClassID)
CREATE TABLE Student_Class_Grade (
    StudentID INT,
    ClassID VARCHAR(10),
    Grade CHAR(3), -- Incorrect: Depends only on ClassID, not the full composite key
    PRIMARY KEY (StudentID, ClassID)
);

-- Corrected solution adhering to 2NF: Attribute splitting
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE Classes_Grade ( -- New table containing only attributes fully dependent on ClassID
    ClassID VARCHAR(10),
    Grade CHAR(3),
    PRIMARY KEY (ClassID)
);

-- Establish association to restore logical relationship
ALTER TABLE Students ADD FOREIGN KEY (StudentID, ClassID) 
REFERENCES Classes_Grade(ClassID); -- Note: In actual scenarios, typically a foreign key directly references the main table; this is for illustrative purposes of dependency elimination

Third Normal Form: Elimination of Transitive Dependency and Independent Attributes

On top of a database satisfying the Second Normal Form, we need to further check for "transitive functional dependencies," which is the focus of the Third Normal Form (3NF). If a non-prime attribute depends on another non-prime attribute rather than directly on the primary key, a transitive dependency exists, leading to data redundancy and update anomalies.

Taking an employee salary table as an example: EmployeeID is the primary key, DepartmentName is a non-prime attribute, and the formula for calculating Salary is "Base Salary + Department Allowance." If the base salary is the same for different departments (e.g., the base salary for all technical department employees is 5000), then BaseSalary actually depends only on DepartmentName. At this point, EmployeeID -> DepartmentName -> BaseSalary constitutes a transitive dependency relationship.

The method to eliminate transitive dependency is to ensure that every non-prime attribute depends directly on the primary key. The usual practice is to separate entities with common attributes into new tables or move calculation logic to the application layer or view layer.

-- Example violating 3NF: Transitive dependency exists (BaseSalary indirectly determined via DepartmentName)
CREATE TABLE Employees_Salary (
    EmployeeID INT PRIMARY KEY,
    DeptCode VARCHAR(10), -- Department code, non-prime attribute but affects salary calculation
    BaseSalary DECIMAL(10,2), 
    Bonus DECIMAL(10,2),
    TotalSalary DECIMAL(10,2)
);

-- Corrected solution adhering to 3NF: Dependency separation
CREATE TABLE Departments ( -- New table storing department and base salary
    DeptCode VARCHAR(10) PRIMARY KEY,
    BaseSalary DECIMAL(10,2), 
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employee_Bonus ( -- Contains only bonus information directly dependent on EmployeeID
    EmployeeID INT PRIMARY KEY,
    Bonus DECIMAL(10,2)
);

-- Association table showing final structure to ensure no transitive dependencies
ALTER TABLE Employees_Salary 
ADD FOREIGN KEY (DeptCode) REFERENCES Departments(DeptCode),
ADD FOREIGN KEY (EmployeeID) REFERENCES Employee_Bonus(EmployeeID);

Practical Significance and Trade-offs of Normal Form Design

Although following strict Third Normal Form can maximize the reduction of data redundancy and ensure consistency, in actual engineering development, we also need to consider performance optimization and business flexibility. Over-splitting tables can lead to frequent JOIN operations, increasing query complexity and response time. Therefore, modern database design often adopts a strategy combining "normalization" and "denormalization": core transactional data strictly follows 3NF, while report analysis or high-frequency read scenarios may intentionally introduce redundancy (i.e., partially violating normal forms) to improve read performance.

Mastering normal form design not only helps write clear SQL statements but also cultivates a deep understanding of the essence of data structures for developers, enabling them to design robust and efficient system architectures when facing complex business logic.