Many people remember database normalization as an exam topic. At work, normalization isn't an exam — it's incident prevention. This guide walks through what each of the first, second and third normal forms actually prevents, using forum and shop examples, and ends with the part most tutorials skip: when to break the rules on purpose.
Start with the incident. A database stores a member's address in three places — the members table, the orders table, the shipping table. The member moves, and only one place gets updated. The same member now has two different addresses across three tables. Which one is true? That unanswerable question is an update anomaly, and normalization is the structural way to prevent it.
At a glance: what 1NF, 2NF and 3NF each prevent
Seeing the three forms side by side first makes the examples below easier to follow.
| Normal form | Structure it catches | Typical symptom |
|---|---|---|
| 1NF | multiple values in one cell | comma-joined tags that can't be searched or joined |
| 2NF | columns tied to part of a composite key | a product name copied into every order line |
| 3NF | columns tied to a non-key column | fixing a ZIP code means fixing the city too |
Now one at a time, with the violating DDL and the fix side by side.
First normal form (1NF): the comma-column example
The most common violation first. Someone wants tags on posts but doesn't want another table:
-- 1NF violation: multiple values in one column
CREATE TABLE posts (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
tags VARCHAR(255) NULL COMMENT 'tags as: mysql,erd,normalization'
);It works at first. The trouble starts the day you need "all posts tagged erd". A LIKE '%erd%' search also matches 'erd-tool', indexes don't help, and renaming a tag means parsing and rewriting strings across the table. The moment one cell holds several values, the database stops being able to treat those values as data.
1NF is the rule "one value per cell," and the fix is to give each value its own row:
-- 1NF satisfied: tags as rows
CREATE TABLE tags (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE COMMENT 'Tag name'
);
CREATE TABLE post_tags (
post_id BIGINT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (post_id, tag_id),
CONSTRAINT fk_pt_post FOREIGN KEY (post_id) REFERENCES posts (id),
CONSTRAINT fk_pt_tag FOREIGN KEY (tag_id) REFERENCES tags (id)
) COMMENT='Post tags';The check is a single question: is there a column where values are joined with commas, slashes or spaces? If so, splitting it now costs far less than fixing it after the searches start.
Second normal form (2NF): columns hanging off half the key
2NF only bites on tables with composite primary keys. Let's look at the shop's order items:
-- 2NF violation: product_name depends on part of the key (product_id only)
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
product_name VARCHAR(200) NOT NULL COMMENT 'the problem column',
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id)
);The primary key is the composite (order_id, product_id). But product_name is determined by product_id alone — the order has nothing to do with it. The result: the same product name copied into as many rows as it has order lines. Rename a product and you're updating thousands of rows, or updating some and leaving one product living under two names.
What is a partial dependency?
That situation has a name: partial functional dependency. The term sounds heavy, but it means exactly what happened — a column determined by only part of the composite key is sitting in the table. product_name depends on half the key (product_id), so the dependency is partial, and 2NF says such columns belong in the table where their own key lives:
-- 2NF satisfied: product data moves to products
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL COMMENT 'Product name'
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_oi_product FOREIGN KEY (product_id) REFERENCES products (id)
);The check: on any composite-key table, ask each column "could I determine this from part of the key alone?" If yes for any column, its home is another table.
Third normal form (3NF): columns hanging off a non-key column
3NF applies even without composite keys. It's the shape that appears when addresses get added to a members table:
-- 3NF violation: city depends on zip_code, not on the key (id)
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
zip_code CHAR(5) NULL COMMENT 'ZIP code',
city VARCHAR(50) NULL COMMENT 'the problem column'
);city isn't determined by the member (id) — it's determined by zip_code. Know the ZIP code and the city follows automatically. In this state, the same ZIP code's city name is copied across as many rows as there are members, and when a city name changes in an administrative reshuffle, you're updating the members table by the thousands. Editing member data and corrupting the address system: the incident from the introduction.
A transitive dependency, by example
This structure's name is transitive dependency. id determines zip_code, and zip_code determines city, so the dependency runs one step removed: id → zip_code → city. 3NF cuts that chain by moving the column that hangs off a non-key column into its own table:
-- 3NF satisfied: ZIP data moves to zip_codes
CREATE TABLE zip_codes (
zip_code CHAR(5) PRIMARY KEY COMMENT 'ZIP code',
city VARCHAR(50) NOT NULL COMMENT 'City'
);
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
zip_code CHAR(5) NULL,
CONSTRAINT fk_members_zip FOREIGN KEY (zip_code) REFERENCES zip_codes (zip_code)
);The check: "could I determine this column from some other non-key column?" If yes, the table whose key is that other column is where this column lives.
Why nobody talks much about BCNF and beyond
Textbooks continue to BCNF, 4NF and 5NF, but most working schemas stop at 3NF. The higher forms only produce different results in special structures — overlapping candidate keys, multi-valued dependencies — and ordinary business schemas that satisfy 3NF usually satisfy them for free. Make 3NF solid as a fundamental, and look up the rest the day you actually run into one of them.
When to denormalize: three tests
If you stop reading here with "so I should always split," you've learned half the lesson. Practice includes deliberately breaking normal forms — denormalization. The problem is never the breaking; it's breaking without grounds. In design reviews, approving a denormalization comes down to three tests:
- Is the bottleneck measured? "Joins will probably be slow" is not grounds. This conversation starts after a real query's execution plan and response time have been measured as a problem. Duplication introduced on a guess tends to keep the same performance and add only the risk.
- Is there a single update path? Duplicating a value hands the application the job of keeping two copies in sync. There has to be an answer — a trigger, a batch job, one code path — for how that happens in exactly one place.
- Is the break documented? The next maintainer who sees the duplicate and "cleans it up" causes the incident. Recording which column is duplicated and why in the table specification is part of the denormalization, not an extra.
Two common cases are worth separating. Aggregate columns (a post's comment count) store a computable value for performance — classic denormalization, and they should pass all three tests. Snapshot columns (the unit price on an order item) are not denormalization at all: the price at order time and the current price are different facts, so copying it is recording history, not duplicating data. With that distinction in hand, you can judge any "looks duplicated" column quickly.
Seeing the normalized result
Normalization splits tables, so when it's done you have more tables and more relationship lines. That's the moment to check the structure visually. Paste the corrected DDL from above into WorksCove ERD and it draws like this:
The comma column has become the post_tags junction table, and the city inside members has become a zip_codes reference — visible as relationship lines. Keep the ERD notation guide nearby if the line symbols are unfamiliar. To verify FKs sit on the N side and the split tables connect as intended, the checklist in How to Draw an ERD applies as is, and the extract-and-paste flow itself is covered in SQL to ERD.
FAQ
How far should I normalize — which normal form is enough?
The working answer is 3NF. First through third normal form prevent most real-world incidents, and the forms above it (BCNF, 4NF) only diverge from 3NF in special structures like overlapping candidate keys or multi-valued dependencies. Treat 3NF as the baseline and manage deliberate performance-driven exceptions (denormalization) separately.
Is copying the price into the order at purchase time a normalization violation?
No — it's correct design. The price at order time and the product's current price are different facts: when the product price changes later, past order totals must stay as they were. You're not duplicating a value, you're recording a fact as of a moment, so snapshot columns don't conflict with normalization.
Do JSON columns with multiple values violate 1NF?
If you need to search or join on those values, you inherit exactly the problems the violation causes. Tags stored as a JSON array have the same indexing and integrity issues as a comma-separated column. If the data is stored whole and read whole — settings blobs, log payloads — a JSON column can be a practical choice. The test is whether the values participate in relationships.
Does normalization still matter in the NoSQL era?
As long as you use a relational database, yes. NoSQL allowing duplication doesn't mean normalization was wrong — it's a different trade: the application takes responsibility for update anomalies in exchange for read performance. To understand that trade you need to know what incidents normalization was preventing, so the starting point is the same either way.
The summary fits in three lines. One value per cell (1NF). Depend on the whole key (2NF). Depend on nothing but the key (3NF). Keep those as the baseline, and when you break them, check measurement, update path and documentation first. Seen this way, normalization stops being exam material and becomes the standard you use in tomorrow's design review.