Skip to content
Tutorials Center
My learning

Topics

Lesson 78 of 125 ยท Designing Databases

Normalization

Learn database normalization in plain language. Spot update, insert and delete anomalies, apply 1NF, 2NF, 3NF and BCNF, and know when to denormalize.

Many databases start life as a spreadsheet: one wide table where every row repeats the customer's name, the product's price and the teacher's phone number. It works fine for a week. Then a phone number changes in one row but not the others, a deleted row takes the last copy of some fact with it, and nobody can say which version of the data is true.

Normalization is the process of organizing tables so that each fact is stored exactly once. You split a wide table into smaller ones linked by keys, guided by a short list of rules called normal forms. The rules sound academic, but the idea behind them is practical: if a fact lives in one place, it can't disagree with itself.

In this lesson you'll see the three kinds of trouble that unnormalized tables cause, learn the normal forms from 1NF to BCNF in plain language, decompose a real table step by step and check that no information was lost, and learn when breaking the rules on purpose (denormalizing) is the right call.

The trouble with one big table

A small language school tracks enrollments in a single table, just like the spreadsheet it replaced. Each row is one student in one course, so the key is the pair (student_email, course_code). Run it and look at the three problems the comments point out:

SQL Update, delete and insert anomalies

Press Run (or Ctrl + Enter) to see the result here.

The run shows all three classic anomalies:

  • Update anomaly. The same fact (Marta's phone number) is stored in three rows. The update touched two of them, so the database now holds two different numbers for one teacher, and nothing tells you which is right.
  • Delete anomaly. Removing a student also removed the only record of the Italian course, its fee and its teacher.
  • Insert anomaly. You can't record a new course without inventing a student, so the last statement stops with Error: NOT NULL constraint failed: signups.student_email.

All three have the same cause: the table mixes facts about several different things (students, courses, teachers, enrollments) in one row.

Functional dependencies

Normal forms are defined using functional dependencies. "A determines B", written A โ†’ B, means: whenever two rows have the same A, they must have the same B. In the school's rules:

  • student_email โ†’ student_name, student_city
  • course_code โ†’ course_title, teacher, fee
  • teacher โ†’ teacher_phone
  • (student_email, course_code) โ†’ enrolled_on

Dependencies come from the real-world rules, not from whatever data happens to be in the table today. Still, you can use data to check a rule you believe in, or to find out that it's broken. If any group of rows with the same left side has more than one distinct right side, the dependency doesn't hold:

SQL Testing a dependency with GROUP BY

Press Run (or Ctrl + Enter) to see the result here.

The query returns Marta Ruiz with 2 phone versions: the leftover from the update anomaly. This GROUP BY ... HAVING COUNT(DISTINCT ...) > 1 pattern is a handy audit when you inherit a messy table.

First normal form (1NF): one value per cell

A table is in first normal form when every column holds a single, indivisible value for each row, and there are no repeating groups such as course1, course2, course3. The most common violation is a list packed into a text column:

SQL A list in a column breaks 1NF

Press Run (or Ctrl + Enter) to see the result here.

It works, but only because every list uses exactly ', ' as the separator. One entry typed as 'SPA101,FRE201' breaks the count silently. You also can't put a foreign key on a list, can't index one course code, and can't record the date of each enrollment. The 1NF fix is one row per student per course, which is what a junction table gives you.

What about JSON columns? Storing a JSON document is a deliberate design choice, not automatically a 1NF violation, as long as you treat the document as one value. If you regularly filter, join or constrain the parts of that value, they probably want to be rows. See Working with JSON.

Second normal form (2NF): depend on the whole key

A table is in second normal form when it's in 1NF and every non-key column depends on the whole primary key, not on part of it. This rule only bites when the key has more than one column.

The signups key is (student_email, course_code), but student_name depends on student_email alone, and course_title and fee depend on course_code alone. Those are partial dependencies, and they're why student and course facts repeat. The fix is to move each group into a table keyed by the part it depends on:

  • students (email, name, city)
  • courses (code, title, teacher, teacher_phone, fee)
  • enrollments (student_email, course_code, enrolled_on): only enrolled_on truly depends on both columns.

Third normal form (3NF): nothing but the key

A table is in third normal form when it's in 2NF and no non-key column depends on another non-key column. Such chains are called transitive dependencies: code โ†’ teacher โ†’ teacher_phone. The phone describes the teacher, not the course, so the 2NF courses table still repeats Marta's number for every course she teaches. Moving teachers into their own table finishes the job.

A classic way to remember 1NF to 3NF: every non-key column must depend on the key (1NF), the whole key (2NF) and nothing but the key (3NF).

Here's the full decomposition. It builds the 3NF tables from the flat data, then joins them back together and uses EXCEPT to prove that no row was lost or invented:

SQL Decomposing to 3NF and checking the result

Press Run (or Ctrl + Enter) to see the result here.

The EXCEPT query returns no rows, so the split is lossless: you can always get the original table back with joins. And each fact now has exactly one home. Marta's phone is one cell in teachers, a course can exist with no students, and deleting an enrollment never deletes a course. Notice that the INSERT ... SELECT DISTINCT steps only worked cleanly because the data had no conflicts. If a teacher had two phone numbers, the UNIQUE constraint on teachers.name would have rejected the second one, forcing you to clean the data first. That's normalization paying off immediately.

Boyce-Codd normal form (BCNF)

3NF handles almost every real table, but there's a stricter version. A table is in Boyce-Codd normal form when every determinant (the left side of any dependency) is a candidate key. The difference from 3NF only appears when a table has overlapping candidate keys, so here's such a case.

An online tutoring service has these rules: a learner has one mentor per language, and each mentor coaches exactly one language. The natural table is mentoring (learner, language, mentor) with the key (learner, language). It's in 3NF, but mentor โ†’ language has a determinant (mentor) that isn't a key. As a result, the fact "Ana coaches Portuguese" repeats for every learner Ana has, and nothing stops a row that contradicts it:

SQL A 3NF table that is not in BCNF

Press Run (or Ctrl + Enter) to see the result here.

The audit query catches Ana listed for two languages. In the BCNF design, a mentor's language is a single cell, so that contradiction can't be stored. The price: the rule "one mentor per language per learner" now spans two tables, and a simple PRIMARY KEY can't enforce it anymore. You'd need a check in application code or a trigger. This trade-off is why many designers stop at 3NF when BCNF would cost them an important constraint.

Beyond BCNF

Higher normal forms exist and occasionally matter:

Normal form Plain-language rule
1NF One value per cell; no repeating column groups
2NF No column depends on only part of a composite key
3NF No column depends on another non-key column
BCNF Every determinant is a candidate key
4NF Don't store two independent lists in one table (for example a person's languages and hobbies, which would force every combination)
5NF A table that can be rebuilt from smaller tables without loss should be split into them

If you design from a sensible ER model (one table per entity, a junction table per many-to-many relationship, as in Entity-Relationship Modeling), you'll usually land in 3NF or BCNF without trying. The normal forms are most useful as a checklist when reviewing a design or cleaning up a legacy table.

When to denormalize

Denormalization means deliberately storing redundant data, usually to make reads faster or simpler. It's a legitimate tool, but you take on the job of keeping the copies in sync. Common, reasonable cases:

  • Cached aggregates. A comment_count on posts, so the homepage doesn't count millions of rows per visit.
  • Historical snapshots. An order line stores the unit_price at the time of purchase. This one isn't really redundancy: the price on the order and today's price are different facts. The shop database's order_items.unit_price works exactly this way.
  • Reporting tables. Data warehouses use wide "star schemas" because analysts read far more than they write.
  • Avoiding expensive joins on very hot paths, after measuring that the join really is the bottleneck.

The danger is drift. Here a summary table caches order totals, an order line changes, and the cache is quietly wrong:

SQL A cached total drifts out of sync ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

If you denormalize, keep the normalized data as the source of truth, update the copy automatically (in the same transaction, with a trigger, or by rebuilding it on a schedule), and have a check query like this one that can detect drift. Often a view or a good index gives you the speed without any redundancy at all, so try those first.

Closures, Armstrong's axioms and finding keys

Every normal form above depends on knowing the table's candidate keys, and so far you've spotted them by eye. That works for five columns. For a legacy table with fifteen columns and a dozen rules, you need a method. Database theory provides one, and it's simpler than its name suggests.

Armstrong's axioms

Given some functional dependencies, other dependencies follow from them automatically. If course_code โ†’ teacher and teacher โ†’ teacher_phone, then clearly course_code โ†’ teacher_phone, even though nobody wrote that rule down. Armstrong's axioms are three rules that produce every dependency that follows from a given set (they're sound: they never produce a false one, and complete: they find all of them):

Axiom Rule Example
Reflexivity If B is a subset of A, then A โ†’ B (student_email, course_code) โ†’ course_code
Augmentation If A โ†’ B, then A, C โ†’ B, C (add the same columns to both sides) course_code โ†’ teacher gives course_code, fee โ†’ teacher, fee
Transitivity If A โ†’ B and B โ†’ C, then A โ†’ C course_code โ†’ teacher โ†’ teacher_phone

Three handy rules follow from those:

  • Union: if A โ†’ B and A โ†’ C, then A โ†’ B, C. That's why you can write course_code โ†’ course_title, teacher, fee as one line.
  • Decomposition: if A โ†’ B, C, then A โ†’ B and A โ†’ C.
  • Pseudotransitivity: if A โ†’ B and B, C โ†’ D, then A, C โ†’ D.

Reflexivity produces trivial dependencies (a set of columns determines its own parts), which say nothing about the data. The interesting results come from transitivity, which is exactly what 3NF's "transitive dependency" refers to.

Attribute closure

Applying the axioms by hand quickly gets messy. The practical tool is the closure of a set of columns X, written Xโบ: every column that X determines. The algorithm is a simple loop:

  1. Start with Xโบ = X.
  2. Look for a dependency A โ†’ B whose left side A is entirely inside Xโบ. Add B to Xโบ.
  3. Repeat until a full pass adds nothing new.

Take the school's rules from earlier and compute โบ:

  • Start: course_code.
  • course_code โ†’ course_title, teacher, fee applies: add course_title, teacher, fee.
  • teacher โ†’ teacher_phone now applies (teacher is inside): add teacher_phone.
  • No other rule's left side fits, so โบ = {course_code, course_title, teacher, fee, teacher_phone}.

The closure answers two questions at once. Does X โ†’ Y hold? Yes, exactly when Y is inside Xโบ. Is X a superkey? Yes, exactly when Xโบ contains every column of the table. โบ lacks the student columns, so course_code alone isn't a key. {student_email, course_code}โบ picks up the student columns through student_email, the course columns as above, and enrolled_on through the composite rule: all nine columns, so it's a superkey.

Finding candidate keys

A candidate key is a minimal superkey: drop any column from it and it stops being a superkey. Three observations make the search fast:

  • A column that never appears on the right side of any dependency can't be derived, so it must be in every candidate key. In the school table, that's student_email and course_code.
  • A column that appears only on right sides is never needed in a key (student_name, teacher_phone, fee, ...).
  • Start from the must-have columns. If their closure already covers the table, they are the only candidate key. Otherwise, try adding the remaining "both sides" columns one at a time, then in pairs, keeping only the minimal sets.

For the school table, {student_email, course_code}โบ is the whole table, so it's the one and only candidate key.

Overlapping keys are where it gets interesting. Recall the mentoring rules from the BCNF section: learner, language โ†’ mentor and mentor โ†’ language. learner never appears on a right side, so it's in every key. Its closure is just , so you try adding a column. Adding language gives {learner, language, mentor}, the whole table. Adding mentor gives mentor โ†’ language, which also reaches the whole table. So there are two candidate keys: (learner, language) and (learner, mentor). That's why the table is in 3NF (language is part of a key) but not in BCNF (mentor determines something without being a key).

Computing closures with SQL

The closure loop is a fixed-point computation, which a recursive CTE can do. The trick is to number the columns as bits (1, 2, 4, ...) so that a set of columns is a single integer: a & b = a tests "a is a subset of b", and a | b is the union. Each dependency becomes one row per right-side column, with its left side as a bitmask:

SQL Closures of every column set

Press Run (or Ctrl + Enter) to see the result here.

The UNION (not UNION ALL) is what stops the recursion: once a step adds nothing, it produces a row that already exists, and the CTE ends. A closure only ever grows, so MAX(m) per starting set is the final closure. The result lists all seven column sets. Three are superkeys, and two of those, learner, language and learner, mentor, are minimal, matching the reasoning above. mentor's closure is language, mentor: the rule that breaks BCNF.

The same query scales to the school table's nine columns (511 subsets) and can pick out the minimal superkeys directly:

SQL Finding the candidate keys

Press Run (or Ctrl + Enter) to see the result here.

Out of 128 superkeys (every set that contains both student_email and course_code), exactly one is minimal: student_email, course_code. Try adding (32, 8) to fds, a made-up rule "each teacher teaches one course", and run it again: student_email, teacher appears as a second candidate key.

You won't run this query at work every day, but the method behind it is worth knowing: when you review a table, list its dependencies, compute closures to find every candidate key, and then check each dependency's left side against those keys. Any left side that isn't a superkey is a normalization problem.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table order_lines (order_id, product_id, quantity, product_name) has the key (order_id, product_id). Which normal form does product_name violate?

    product_name depends on product_id alone, which is only part of the composite key. That's a partial dependency, so the table breaks 2NF.
  2. 2. In employees (id, name, department_id, department_name), which dependency breaks 3NF?

    department_name depends on department_id, a non-key column, which depends on the key. That transitive chain is what 3NF forbids; move departments into their own table.
  3. 3. Deleting the last enrollment for a course also erases the course's fee. What is this called?

    A delete anomaly happens when removing one fact also removes another, unrelated fact that was stored in the same row.
  4. 4. What does this query return if teacher โ†’ teacher_phone holds in the data? SELECT teacher FROM signups GROUP BY teacher HAVING COUNT(DISTINCT teacher_phone) > 1;

    If the dependency holds, every teacher has exactly one distinct phone, so no group passes the HAVING filter and the result is empty.
  5. 5. Which is the strongest reason to store unit_price on each order line even though products have a price?

    Prices change. The order line's price is a historical fact about that sale, so storing it is correct modeling, not accidental redundancy.

Summary

  • Unnormalized tables cause update, insert and delete anomalies because one row mixes facts about several things.
  • A functional dependency A โ†’ B means equal A values always come with equal B values. GROUP BY A HAVING COUNT(DISTINCT B) > 1 finds violations in real data.
  • 1NF: one value per cell. 2NF: no partial dependencies on a composite key. 3NF: no transitive dependencies. BCNF: every determinant is a candidate key.
  • Decompose by moving dependent columns into their own tables, and verify the split is lossless by joining back and comparing with EXCEPT.
  • Denormalize only for a measured reason, keep a single source of truth, and automate and audit the copies.

Next: One-to-Many and Many-to-Many, where you'll model real systems with foreign keys and junction tables.