Normalization is the process of organizing columns and tables so that each fact lives in exactly one place. Do it well and whole classes of data corruption simply cannot happen.
When a single table tries to hold too many kinds of information at once, it starts to fight you. The same customer name gets typed three slightly different ways, updating a price means touching a hundred rows, and deleting the last order for a product accidentally erases the product itself. Normalization is the disciplined cure. It is a set of rules, called normal forms, that you apply step by step to split a wide, repetitive table into smaller, focused tables joined by keys. This guide walks one messy table through 1NF, 2NF, 3NF, and BCNF, showing the CREATE TABLE statements and sample rows at every stage so you can see exactly what each form fixes.
What this guide covers
The theory goes back to Edgar F. Codd, the researcher who invented the relational model. If you want the formal definitions and history, the Wikipedia article on database normalization is a solid reference. For a gentler, applied introduction with practice exercises, start with our database normalization lesson, then come back here for the full walkthrough.
1. Why normalize: the three anomalies
Normalization is not about tidiness for its own sake. It exists to prevent three concrete problems, known as update anomalies. Each one is a way that a poorly structured table lets your data drift out of sync with reality.
- Insert anomaly: you cannot record one fact without also inventing another. If course details only exist inside enrollment rows, you cannot list a brand new course until at least one student signs up for it.
- Update anomaly: one real world change forces many row edits. If an instructor moves office and their name appears in fifty rows, you must update all fifty. Miss one and the table now disagrees with itself.
- Delete anomaly: removing one fact destroys an unrelated one. Delete the last student in a course and the course itself, its title and instructor, vanishes with that row.
Every normal form removes a specific structural cause of these anomalies. By the time a schema reaches 3NF, most day to day corruption is designed out. Related reading: many of these traps also appear in our roundup of common database design mistakes.
2. The messy starting table
Here is a single flat table that a beginner might build for a small college. It tries to hold students, courses, instructors, and grades all at once. Everything below flows from fixing this one table.
-- One table trying to do everything
CREATE TABLE enrollments (
student_id INT,
student_name VARCHAR(100),
courses VARCHAR(255), -- 'Math101, Hist210'
instructor VARCHAR(100),
instructor_office VARCHAR(20),
grade VARCHAR(2)
);With sample data, the problems jump out. Notice the comma packed courses column, the repeated student name, and the instructor office copied on every row.
| student_id | student_name | courses | instructor | instructor_office | grade |
|---|---|---|---|---|---|
| 1 | Ana Ruiz | Math101, Hist210 | Dr Lee, Dr Kaur | B12, C04 | A, B |
| 2 | Ben Osei | Math101 | Dr Lee | B12 | C |
| 3 | Ana Ruiz | Chem110 | Dr Adeyemi | A09 | B |
Ana appears twice with her name retyped, several values are crammed into one cell, and if Dr Lee changes office you must hunt down every row that mentions B12. This table is unnormalized. Let us fix it one form at a time.
3. First normal form (1NF)
The rule: every cell holds a single atomic value, and there are no repeating groups. No comma separated lists, no arrays hidden inside a column, and one row per fact. To reach 1NF we split the multi value cells so each course, instructor, and grade sits in its own row, and we give the table a proper key.
CREATE TABLE enrollments (
student_id INT NOT NULL,
student_name VARCHAR(100) NOT NULL,
course_id VARCHAR(10) NOT NULL,
instructor VARCHAR(100) NOT NULL,
instructor_office VARCHAR(20) NOT NULL,
grade VARCHAR(2),
PRIMARY KEY (student_id, course_id)
);Each cell is now atomic, and the pair (student_id, course_id) uniquely identifies a row. The sample data expands so every enrollment is its own line:
| student_id | course_id | student_name | instructor | instructor_office | grade |
|---|---|---|---|---|---|
| 1 | Math101 | Ana Ruiz | Dr Lee | B12 | A |
| 1 | Hist210 | Ana Ruiz | Dr Kaur | C04 | B |
| 2 | Math101 | Ben Osei | Dr Lee | B12 | C |
| 3 | Chem110 | Ana Ruiz | Dr Adeyemi | A09 | B |
Atomic means indivisible for your queries. A comma list in one column forces you to parse strings instead of joining rows, and it makes indexes, foreign keys, and aggregate functions useless on that data. One value per cell is what lets SQL do its job.
4. Second normal form (2NF)
The rule: the table is in 1NF and no non key column depends on only part of a composite key. This is called removing partial dependencies. Our key is (student_id, course_id), so we ask: does any column depend on just one half of that key?
student_namedepends only onstudent_id. It has nothing to do with the course.instructorandinstructor_officedepend only oncourse_id. The same course always has the same instructor regardless of who enrolls.gradegenuinely depends on the whole key: it is this student in this course.
Each partial dependency becomes its own table. We keep only the truly key dependent facts in the enrollment table and point at the parents with foreign keys.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id VARCHAR(10) PRIMARY KEY,
instructor VARCHAR(100) NOT NULL,
instructor_office VARCHAR(20) NOT NULL
);
CREATE TABLE enrollments (
student_id INT NOT NULL REFERENCES students(student_id),
course_id VARCHAR(10) NOT NULL REFERENCES courses(course_id),
grade VARCHAR(2),
PRIMARY KEY (student_id, course_id)
);Ana Ruiz now exists once in students. Rename her and every enrollment sees the change instantly, because the name is stored in exactly one place. The insert anomaly is easing too: you can add a course to courses before anyone enrolls. If foreign keys are new to you, our guide on primary key vs foreign key explains how these links are enforced.
5. Third normal form (3NF)
The rule: the table is in 2NF and no non key column depends on another non key column. This is removing transitive dependencies, where the key determines column A, and column A determines column B, so the key only reaches B by going through A.
Look hard at the courses table. The key is course_id. But instructor_office does not really depend on the course; it depends on the instructor. The chain is course_id → instructor → instructor_office. That middle step is the transitive dependency. If Dr Lee teaches two courses, their office is stored twice, and moving offices means editing multiple rows again.
CREATE TABLE instructors (
instructor_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
office VARCHAR(20) NOT NULL
);
CREATE TABLE courses (
course_id VARCHAR(10) PRIMARY KEY,
title VARCHAR(120) NOT NULL,
instructor_id INT NOT NULL REFERENCES instructors(instructor_id)
);Now the instructor office lives once per instructor. The courses table stores only what depends directly on the course, plus a foreign key to the instructor. Sample data shows the payoff clearly:
| Table | Row | What it stores |
|---|---|---|
| instructors | (7, Dr Lee, B12) | Office recorded exactly once |
| courses | (Math101, Calculus I, 7) | Course points at instructor 7 |
| courses | (Stat205, Statistics, 7) | Same instructor, no repeated office |
A schema in good 3NF is the sweet spot for most applications: little redundancy, clear ownership of each fact, and joins that are still easy to write. This is the practical target when you follow our walkthrough on how to design a relational database.
6. Boyce Codd normal form (BCNF)
The rule: for every functional dependency X → Y in the table, X must be a candidate key. Put simply, every determinant, anything that decides another column, has to be something that could serve as a key. BCNF is a stricter version of 3NF, and most 3NF tables already satisfy it. The interesting cases involve overlapping candidate keys.
Suppose each course section is taught by one instructor, a student takes a course only once, and crucially each instructor teaches exactly one course. Consider this table of who teaches whom:
CREATE TABLE teaching (
student_id INT NOT NULL,
course_id VARCHAR(10) NOT NULL,
instructor VARCHAR(100) NOT NULL,
PRIMARY KEY (student_id, course_id)
);Two dependencies hold here. First (student_id, course_id) → instructor, the chosen key. But also instructor → course_id, because each instructor teaches only one course. That second determinant, instructor, is not a candidate key on its own, yet it determines course_id. The table is in 3NF but it violates BCNF, and the redundancy shows: every student of Dr Lee re records that Dr Lee teaches Math101.
The fix is to decompose so every determinant becomes a key. Split the fact that an instructor teaches a course away from the fact that a student has an instructor.
-- Each instructor determines their course, so make instructor the key
CREATE TABLE instructor_course (
instructor VARCHAR(100) PRIMARY KEY,
course_id VARCHAR(10) NOT NULL
);
CREATE TABLE student_instructor (
student_id INT NOT NULL,
instructor VARCHAR(100) NOT NULL REFERENCES instructor_course(instructor),
PRIMARY KEY (student_id, instructor)
);BCNF can cost you a dependency. Decomposing to BCNF occasionally makes it harder to enforce a rule that spanned the original table. This is a known trade off, and it is why 3NF, not BCNF, is the everyday target. Push to BCNF when the leftover redundancy genuinely bites. We go deeper on 4NF, 5NF, and these trade offs in advanced normalization.
7. Summary of the normal forms
Here is the whole ladder in one view. Read it top to bottom: each form assumes the previous one is already satisfied.
| Normal form | Rule | What it fixes |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Comma lists and arrays hidden in a single column |
| 2NF | No partial dependency on part of a composite key | Columns that belong to only one part of the key |
| 3NF | No transitive dependency between non key columns | Facts stored via a middle column, like office via instructor |
| BCNF | Every determinant is a candidate key | Redundancy from overlapping candidate keys |
The chart below shows how redundancy in our worked example shrinks as we climb the ladder. The values are a representative illustration of the design, not a formal measurement.
Notice the steep drop between 1NF and 3NF. That is where the biggest wins live, which is why most teams design straight to 3NF and only consider stricter forms when a real problem appears. To practise these decompositions with guided exercises, work through the SQL intermediate course.
8. When to denormalize for performance
Normalization optimizes for correctness and clean writes. It does not always optimize for read speed. A fully normalized schema can require many joins to answer one question, and on very large, read heavy systems those joins can become a bottleneck. Denormalization is the deliberate choice to add controlled redundancy back in, trading some write complexity for faster reads.
Common, legitimate denormalization tactics include storing a derived total on an order so you do not sum line items on every page load, keeping a copy of a frequently displayed name next to a foreign key, or building summary tables and materialized views. In PostgreSQL, for example, a materialized view caches the result of an expensive join and refreshes on demand; see the PostgreSQL materialized views documentation for the syntax and refresh options.
Normalize first, denormalize on evidence. Start from a clean 3NF design, then denormalize only where a measured, real query is too slow and no index or query rewrite fixes it. Denormalized copies must be kept in sync with triggers, application code, or scheduled refreshes, and that maintenance is the price you pay for the speed.
The order matters. A normalized schema that you selectively denormalize is easy to reason about, because you know exactly which columns are the trusted source and which are fast copies. A schema that grew up denormalized by accident is the one that produces the insert, update, and delete anomalies we started with. Get the structure right first, measure second, and add redundancy last, only where the data proves you need it.