Database Normalization Levels

Apply 1NF, 2NF, and 3NF to refactor tables into an efficient, low-redundancy structure.

In this lesson, you will learn how to organize your data into smaller, related tables to eliminate redundant information and keep your database consistent.

From Chaos to Order: The Normalization Levels

Normalization is the process of structuring a database to reduce data duplication. We move through levels—First (1NF), Second (2NF), and Third (3NF)—to ensure each piece of information is stored in exactly the right place.

First Normal Form (1NF)

A table is in 1NF if every column contains only atomic values (no lists or comma-separated values) and each record is unique. If you have a column like Skills containing "SQL, Python, Java," you are not in 1NF. Break those out into individual rows.

Second Normal Form (2NF)

To reach 2NF, your table must first be in 1NF, and all non-key columns must depend on the entire primary key. Imagine a table with (StudentID, CourseID, CourseName). Here, CourseName depends only on CourseID, not the StudentID. This is a "partial dependency." To fix this, you would move CourseName into a separate Courses table.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and has no transitive dependencies. This means non-key columns shouldn't depend on other non-key columns.

Consider this structure:

-- Before 3NF: 'InstructorOffice' depends on 'InstructorName', not the ID
CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    InstructorName VARCHAR(50),
    InstructorOffice VARCHAR(50) 
);

If we change the instructor's office, we shouldn't have to hunt through every class they teach to update it. Instead, we refactor:

-- After 3NF: Data is separated by what it actually describes
CREATE TABLE Instructors (
    InstructorID INT PRIMARY KEY,
    InstructorName VARCHAR(50),
    InstructorOffice VARCHAR(50)
);

CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    InstructorID INT, -- Reference to the instructor
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

By separating entities, you ensure that updates only happen in one place, preventing the data integrity issues we discussed in previous lessons.

Key Takeaway: Normalization is the art of ensuring each column in a table relates directly to the primary key, minimizing redundancy by splitting data into logically distinct tables.