Normal forms, denormalization, and when each tradeoff is correct.
Published September 21, 2026
Normalization is a method for deciding which tables a relational schema should have, so that each fact is stored exactly once. When a fact is stored in several places, those copies can disagree, and then the database contains contradictions. The normal forms are a sequence of increasingly strict rules, each removing one specific source of redundancy.
It helps to see the problem first.
Imagine one wide table recording course enrollments:
| student_id | student_name | course_id | course_title | instructor | instructor_office | grade |
|---|---|---|---|---|---|---|
| 1 | Asha | C10 | Databases | Rao | B-204 | A |
| 2 | Vikram | C10 | Databases | Rao | B-204 | B |
| 1 | Asha | C20 | Networks | Iyer | A-110 | A |
Every enrollment repeats the course title, instructor and office. That creates three anomalies:
Normalization removes these by splitting the table so that each fact lives in exactly one row.
A functional dependency X → Y means: if two rows have the same X, they must have the same Y. In the example:
student_id → student_namecourse_id → course_title, instructorinstructor → instructor_office(student_id, course_id) → gradeThe key of the enrollment table is (student_id, course_id). The normal forms are all rules about which dependencies are allowed in a table relative to its keys.
Each column holds a single value, and there are no repeating column groups like course1, course2, course3.
❌ courses = "Databases, Networks" in one cell: you can't index, join or constrain individual courses, and "remove Networks" means string editing.
✅ One row per student–course pair, as in the table above.
The table must be in 1NF, and every non-key column must depend on the whole key, not just part of a composite key.
In the enrollment table, course_title depends only on course_id (part of the key), and student_name depends only on student_id. Those are partial dependencies, so split them out:
students(student_id PK, student_name)
courses(course_id PK, course_title, instructor, instructor_office)
enrollments(student_id FK, course_id FK, grade, PK(student_id, course_id))
A table whose key is a single column is automatically in 2NF, because there's no "part of the key" to depend on.
The table must be in 2NF, and non-key columns must depend on the key directly, not through another non-key column.
courses still has course_id → instructor → instructor_office. The office is a fact about the instructor, not the course. If Rao teaches three courses, the office is stored three times. Split it:
courses(course_id PK, course_title, instructor_id FK)
instructors(instructor_id PK, name, office)
A classic summary of 3NF: every non-key attribute depends on "the key, the whole key, and nothing but the key."
BCNF tightens 3NF: for every dependency X → Y, X must be a candidate key. 3NF allows one exception: Y may be part of some candidate key. That exception only matters in tables with overlapping candidate keys.
Example: each instructor teaches exactly one subject, and a student has one instructor per subject. In (student, subject, instructor), the dependency instructor → subject holds, but instructor isn't a key, so the table is in 3NF but not BCNF. Splitting it into (instructor, subject) and (student, instructor) fixes it.
In practice, most well-designed schemas in 3NF are already in BCNF. 4NF and 5NF deal with multi-valued and join dependencies, and rarely come up outside theory.
Normalization optimizes for correct writes and a single source of truth. Reads may need joins. Sometimes, after measuring, you add controlled redundancy for read performance:
customer_name on orders, or storing the price paid on each order line. That second one isn't really redundancy: the price at the time of purchase is a separate fact from today's catalogue price.order_count on customers, or daily sales summary tables.The cost is that you now own keeping the copies consistent, through triggers, the same transaction, events, or scheduled rebuilds. Denormalize for a specific, measured read problem, and document which copy is the source of truth.
| Form | Rule | Removes |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Lists inside cells |
| 2NF | Non-key columns depend on the whole key | Partial dependencies (composite keys) |
| 3NF | Non-key columns depend only on the key | Transitive dependencies |
| BCNF | Every determinant is a candidate key | Remaining anomalies with overlapping keys |
Q: How do you spot a normalization problem in a schema quickly? A: Look for repeated values that describe something other than the row's key: a city repeated alongside a postcode, a product name repeated in every order line. Then ask "if this value changed, how many rows would I have to update?" If the answer is "more than one", there's likely a partial or transitive dependency to split out.
Q: Is a table with a single-column surrogate key automatically fully normalized?
A: It's automatically in 2NF, but it can still violate 3NF. Transitive dependencies such as zip_code → city have nothing to do with the key being a single column.
Q: When should you denormalize? A: When profiling shows that a specific frequent read is too slow because of joins or aggregation, and an index or query rewrite doesn't fix it. Then add the minimum redundancy needed, and a clear mechanism to keep it consistent.
Q: Does normalization apply to MongoDB? A: The anomalies are the same, but the trade-off shifts. Document databases often embed and duplicate data deliberately, because reads are optimized around whole documents and joins are limited. You still reason about which copy is authoritative and how updates propagate. See Schema Design Patterns.
Q: What's the difference between 3NF and BCNF in one sentence? A: 3NF allows a non-key determinant when the dependent attribute is part of a candidate key. BCNF doesn't allow that: every determinant must be a candidate key.