DiagramDB / Database Normalization

Database Normalization

Normalization means storing each fact once, so it can’t contradict itself. Follow one messy orders table through 1NF, 2NF and 3NF and watch the repeated data disappear.

Start: one spreadsheet-style table

Each row is an order with everything typed in. The Items cell holds a list, and the customer’s city and the product prices are repeated on every order. Change Ana’s city and you must find every row she appears in.

orders_raw
order_idkey	customer	city	items
1001	Ana Ruiz	Porto	Kettle ×1 @ 30, Mug ×2 @ 8
1002	Ben Ode	Lagos	Mug ×4 @ 8
1003	Ana Ruiz	Porto	Teapot ×1 @ 25

Highlighted columns are the ones the next step will move.

The normal forms, briefly

First normal form (1NF)

Every column holds a single value of one type, there are no repeating groups like phone1, phone2, phone3, and each row is uniquely identifiable. Lists stored in a cell (comma-separated tags) break 1NF; move them to a child table.

Second normal form (2NF)

Only relevant when the key has more than one column. Every non-key column must depend on the whole key. If a column depends on just part of it (product price depends on product, not on order + product), move it out.

Third normal form (3NF)

No non-key column depends on another non-key column. City depends on customer, so it belongs in customers, not orders. After 3NF, each fact has one home.

Beyond 3NF

BCNF tightens 3NF for tables with overlapping candidate keys. 4NF removes independent multi-valued facts stored together (a person’s languages and hobbies in one table), and 5NF deals with join dependencies. Most production schemas stop at 3NF/BCNF.

When to denormalize

Denormalization, meaning deliberately storing a derived or repeated value, trades write complexity for read speed: a cached order_total, a reporting table, a star schema in a warehouse. Do it knowingly, keep the normalized source of truth, and document how the copy is kept in sync (trigger, job or application code).

Normalization checklist

Does any cell contain a list? → child table (1NF).
Composite key? Does every column need all of it? → split (2NF).
Could a column be looked up from another non-key column? → its own table (3NF).
Would changing one real-world fact require updating more than one row? → it isn’t normalized yet.

Questions

What is database normalization?

Normalization is the process of organising tables so each fact is stored in exactly one place. It was introduced by Edgar F. Codd in his relational model work (1970–71) and proceeds through “normal forms”, each removing a kind of redundancy that causes update, insert and delete anomalies.

What are 1NF, 2NF and 3NF in simple terms?

1NF: every cell holds one value and there are no repeating groups. 2NF: 1NF, and every non-key column depends on the whole primary key, not part of it. 3NF: 2NF, and non-key columns depend only on the key, not on other non-key columns. The classic summary is “the key, the whole key, and nothing but the key”.

What is BCNF?

Boyce–Codd normal form is a slightly stricter 3NF: for every functional dependency X → Y, X must be a candidate key. Tables in 3NF violate BCNF only in unusual cases with overlapping candidate keys, so 3NF and BCNF are usually treated as the practical target.

Should I always normalize to 3NF?

For transactional (OLTP) databases, 3NF is the sensible default because it prevents inconsistent data. Analytics warehouses often denormalize on purpose (star schemas) to make reads simpler and faster. Denormalize deliberately, after measuring, not by accident.

What are update, insert and delete anomalies?

Update anomaly: the same fact is stored in many rows and one copy gets changed but not the others. Insert anomaly: you cannot record a fact (a new course) without an unrelated one (a student enrolled in it). Delete anomaly: deleting one fact (the last enrolment) destroys another (that the course exists).