The problem normalization solves
Imagine a single table that records every order a small shop receives, with the customer’s name, email address and city typed out on each row. It is easy to start this way, and it works at first. Then problems appear.
| order_id | customer_name | customer_email | city | product |
|---|---|---|---|---|
| 101 | Ana | ana@example.com | Leeds | Lamp |
| 102 | Ana | ana@example.com | Leeds | Desk |
| 103 | Ben | ben@example.com | Bristol | Chair |
Ana’s details are stored twice. If she changes her email, you must find and fix every row, and if one is missed, the table now disagrees with itself. These problems have names:
- Update anomaly: a change must be made in many places and may be made in only some.
- Insert anomaly: you cannot record a new customer until they place an order, because there is no row to hold them.
- Delete anomaly: deleting a customer’s only order also erases everything you knew about the customer.
Normalization is the process of structuring tables so that each fact is stored in one place. The aim is to reduce duplication and the errors that come with it. Data is stored once, and the relationships between tables, using keys, bring it back together when needed.
First normal form (1NF)
A table is in first normal form when each cell holds a single value and there are no repeating groups. A column that holds “Lamp, Desk” in one cell breaks the rule, as do columns named product1, product2 and product3. The fix is to give each value its own row, or to move the repeated items to their own table.
Each row should also be identifiable by a key, so that no two rows are the same thing.
Second normal form (2NF)
Second normal form matters when a table’s key is made of more than one column. Suppose an order-lines table is keyed by order and product together, but it also stores the product’s description. The description depends only on the product, not on the whole key. That is a partial dependency, and 2NF asks you to move the description to a products table.
If your table has a single-column key, it satisfies 2NF automatically once it satisfies 1NF.
Third normal form (3NF)
Third normal form removes dependencies between ordinary columns. In the original table, the city depends on the customer, not on the order. A column that depends on another non-key column, rather than directly on the key, belongs somewhere else.
A common informal summary is that every non-key column should describe “the key, the whole key, and nothing but the key.”
The result
Splitting the original table gives two tables.
customers
| customer_id | name | city | |
|---|---|---|---|
| 1 | Ana | ana@example.com | Leeds |
| 2 | Ben | ben@example.com | Bristol |
orders
| order_id | customer_id | product |
|---|---|---|
| 101 | 1 | Lamp |
| 102 | 1 | Desk |
| 103 | 2 | Chair |
Now Ana’s email lives in exactly one row. The customer_id in orders is a foreign key pointing back to customers, as described in relational databases. A customer can exist before ordering anything, and deleting an order leaves the customer intact. All three anomalies are gone.
In a fuller design, the product would likely move to its own table too, with a link between orders and products. Because the data is now split, queries that need both pieces use SQL joins to reassemble it.
Beyond third normal form
Further normal forms exist, such as Boyce-Codd normal form and fourth and fifth normal forms. They address more unusual situations, and many practical designs stop at 3NF. Treat the forms as a checklist of useful questions rather than a ritual: Is this fact stored more than once? Does this column describe the thing the row is about?
When denormalization makes sense
Normalization is not always the goal. Denormalization means deliberately keeping some duplicated data to make reading faster or simpler. Reasons include:
- Reports that would otherwise join many tables on every request.
- Analysis systems, where read speed matters more than update safety. Many data warehouses use wide, partly duplicated tables for this reason.
- Historical records, where you want to keep what was true at the time. An invoice should usually keep the price and address as they were when it was issued, even if the product or customer record changes later.
Document stores, covered in the comparison with NoSQL databases, often embed related information inside one record by design. That trades some of the update safety of normalization for convenience in reading.
Denormalizing is a choice made with open eyes, and it normally means accepting extra work to keep copies consistent.
Practical advice
- Start by listing the “things” in your data, such as customers, orders and products, and give each its own table.
- Give every table a stable primary key.
- Ask of each column: does this belong to the thing this table describes?
- Avoid putting lists inside a single cell.
- Do not over-split. A table for every tiny value makes queries clumsy without adding much safety.
- Keep a record of the design, in a diagram or short notes, so the next person understands the intent.
A well-structured design also makes later work easier. Cleaner structure reduces the effort of data cleaning, and it makes rules such as data minimization simpler to apply, since personal details sit in one known place rather than being scattered across many tables.
A note on terminology
You may see “normalization” with an s and “normalization” with a z. They mean the same thing, and the meaning is separate from the statistical sense of rescaling numbers to a common range, which is an unrelated use of the word.