Normalisation: 1NF, 2NF, 3NF and BCNF

The problem: one big sheet

Many businesses start with one wide sheet, one row per order line, with everything copied in. It is easy to read, so people ask "why not keep it like that?" The answer shows up as soon as the data changes.

order_id | order_date | customer   | customer_phone        | city | product      | category | unit_price | qty
101      | 2025-01-05 | Asha Verma | 9876500001,9876500002 | Pune | Basmati Rice | Grocery  | 650        | 2
101      | 2025-01-05 | Asha Verma | 9876500001,9876500002 | Pune | Toor Dal     | Grocery  | 160        | 3
103      | 2025-02-14 | Asha Verma | 9876500001,9876500002 | Pune | Cotton Kurta | Fashion  | 899        | 2

Three anomalies

An anomaly is a way the data can become wrong or impossible to store because of the table's shape.

• Update anomaly: Asha moves to Mumbai. Her city sits on 3 rows (or 3,000). Miss one and the database now says two different things.
• Insert anomaly: the shop adds a new product, the Power Bank, before anyone orders it. There is no order line to put it on, so the product cannot be recorded at all.
• Delete anomaly: the only order containing Cotton Kurta is deleted, and the fact that Cotton Kurta costs ₹899 and is Fashion disappears with it.

Functional dependency, in words

A functional dependency A → B ("A determines B") means: if you know A, there is exactly one B. Knowing a customer's id tells you her name; knowing a product tells you its category. In the sheet, order_id → order_date and customer, product → category and unit_price, and only the pair (order_id, product) → qty. Normalisation is simply making sure every column depends on the key of its own table.

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

A table is in 1NF when every cell holds a single, atomic value, there are no repeating groups of columns, and rows have a key. The phone cell "9876500001,9876500002" breaks it: you cannot easily search, join or validate one number inside a list. Columns like phone1, phone2, phone3 break it too, in a sneakier way: a customer with four phones does not fit, and "find this phone" needs three ORs.

CREATE TABLE customer_phones (
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  phone       TEXT NOT NULL,
  PRIMARY KEY (customer_id, phone)
);

Second normal form (2NF): the whole key

2NF matters when the key has more than one column. A table is in 2NF when it is in 1NF and no non-key column depends on only part of the key. In the sheet the key is (order_id, product). But order_date depends on order_id alone, and category depends on product alone. These are partial dependencies. The fix is to move them out: order details go to orders, product details to products, and the line table keeps only what needs the whole pair, qty and the price paid.

Third normal form (3NF): nothing but the key

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. After the 2NF split, orders still holds customer, phone and city. city depends on the customer, not on the order: order_id → customer → city. That chain is a transitive dependency. The fix is a customers table, with orders keeping only customer_id.

BCNF, briefly

Boyce-Codd normal form is a slightly stricter 3NF: for every dependency A → B, A must be a candidate key. It only bites in unusual tables with overlapping candidate keys. Classic case: tutoring(student, subject, teacher), where each teacher teaches one subject. The key is (student, subject), but teacher → subject and teacher is not a key. The BCNF fix is teachers(teacher, subject) plus tutoring(student, teacher). There are higher forms (4NF, 5NF), but 3NF or BCNF is the everyday goal.

The result, and getting the report back

The normalised version of the sheet is exactly the SHOP database you have used all course: customers, products, orders and order_items. The flat report is not lost; a JOIN rebuilds it whenever you need it.

SELECT o.id AS order_id, o.order_date, c.name AS customer, c.city, p.name AS product, oi.qty
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.id = 101;
-- 101 | 2025-01-05 | Asha Verma | Pune | Basmati Rice 5kg | 2
-- 101 | 2025-01-05 | Asha Verma | Pune | Toor Dal 1kg     | 3

Common mistakes

• Storing lists in one cell ("1,5,7" or JSON arrays) for things you search or join on. Fix: a child table with one row per item.
• Thinking order_items.price is a 3NF violation because products also has a price. It is not: it is the price paid at that moment, a different fact. Copy it on purpose.
• Splitting tables so far that every screen needs ten joins for no benefit. Fix: aim for 3NF, then denormalise only with a measured reason (next topic).
• Mixing up 2NF and 3NF. Fix: 2NF is about part of a composite key; 3NF is about one non-key column determining another.