Database normalization: 1NF, 2NF and 3NF

To normalize is to split data into tables so that each piece of data is stored only once. It’s the step between the entity-relationship model and CREATE TABLE: it decides which tables you need and which columns each one gets.

You do it by applying a set of rules called normal forms. There are several, but in practice the first three are enough for most databases.

The problem: one table that stores everything

Imagine the shop keeps its orders in a single sheet, just as they come in:

It works while it’s small, but it has three problems, called anomalies:

• Update anomaly. If Mavin moves, the address has to be fixed on every order. Miss one and the database holds two addresses for the same person.
• Insertion anomaly. You can’t add a customer who hasn’t bought anything yet, because each row is an order.
• Deletion anomaly. If you delete Peter’s only order, you also lose everything you knew about Peter.

On top of that, with several products in one cell there’s no simple way to answer “how many Xboxes have we sold?”.

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

A table is in 1NF if:

• Each cell holds a single value (no comma-separated lists).
• There are no repeating groups of columns (product1, product2, product3…).
• Each row can be identified by a key.

We split the products into rows. The key becomes the combination of order and product:

Now you can count and sum per product, but Mavin and her address are repeated.

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

A table is in 2NF if it’s in 1NF and every column depends on the whole key, not just part of it. It only affects tables whose key has several columns.

Our key is (orderId, product). But:

• customer and customerAddress depend only on orderId.
• description and price depend only on product.

Each group moves to its own table, and the original keeps only what depends on both columns together (in a real shop, the quantity of each product in each order):

Orders

Products

OrderLines

Each product’s price now lives in one place.

Third normal form (3NF): nothing depends on a non-key column

A table is in 3NF if it’s in 2NF and no column depends on another column that isn’t the key (a so-called transitive dependency).

In Orders, the address doesn’t depend on the order but on the customer: if Mavin places ten orders, her address is repeated ten times. The fix is the same as before: move the customer to its own table and keep only a reference to it in Orders.

Customers

Orders

customerId in Orders is a Foreign Key: the link to the Customers table. All three anomalies are gone. The address is changed in one place, you can add a customer with no orders, and deleting an order doesn’t delete the customer.

The rule to remember

A classic way to sum up the three normal forms: every column must depend on the key, the whole key and nothing but the key.

• “On the key”: 1NF, each row is identified by a key and each cell holds one value.
• “The whole key”: 2NF.
• “And nothing but the key”: 3NF.

Should you always normalize?

For an application database, where data is written constantly (orders, users, invoices), yes: normalizing to 3NF prevents inconsistencies and is the standard starting point.

There are cases where you denormalize on purpose, such as data warehouses and reporting tables, where almost everything is reads and repeating data saves a lot of JOINs. But that’s a decision you make on top of a normalized design, not an excuse to skip it.

Practice

1. A Students (studentId, name, subjects) table stores values like 'Maths, Physics' in subjects. Which normal form does it break and how would you fix it?
2. In an Enrollments (studentId, subjectId, subjectName, grade) table with key (studentId, subjectId), which column breaks 2NF?
3. In Employees (employeeId, name, departmentId, departmentName), which column breaks 3NF?