Database normalization is often taught as theory, but in practice it prevents update anomalies. Forum and shop examples below show the problems addressed by 1NF, 2NF and 3NF, followed by the conditions for deliberate denormalization.
Suppose a database stores a member's address in the members, orders and shipping tables. The member moves, but only one copy is updated. The database now holds conflicting addresses for the same person. This is an update anomaly, and normalization is one way to prevent it structurally.
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. This assumes product_name is a copy of the product's current name. If it intentionally records the name shown at order time, it is a different fact and the same partial-dependency conclusion does not follow.
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'
);For this example, assume the business rule says one ZIP code determines one city. Under that assumption, city depends on zip_code rather than on the member id. Real postal systems can map one ZIP or postal code to more than one locality name, so verify that the functional dependency actually holds in the country and address data you use before applying this model.
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?
A JSON array is not automatically a 1NF violation. Modern DBMSs can index JSON paths or array elements, but values such as tags become harder to protect with foreign keys and uniqueness when they participate in relationships. JSON is practical for settings or log payloads read as a whole; use separate rows when search, joins, and referential integrity matter.
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.
Use one value per cell for 1NF, require non-key attributes to depend on the whole composite key for 2NF, and remove dependencies between non-key attributes for 3NF. Before denormalizing, confirm a measured bottleneck, one controlled update path and written documentation.