Database Normalization: 1NF, 2NF and 3NF Explained Simply

A small kirana shop in Malharganj keeps everything in one notebook. Each line is a bill line: date, customer name, customer phone, product, price, quantity. It works fine for a month.

Then a regular customer changes her phone number. Her old number appears on forty lines across three months. The owner corrects the last few lines and gives up. Now the notebook has two phone numbers for the same person, and nobody knows which is right.

Next month, the shop stops stocking one brand of butter, and the owner tears out the only page that mentioned it. The butter's price disappears with it. The notebook mixed several different things on one line: facts about customers, facts about products and facts about sales.

Database normalization is a step-by-step method of organising columns into tables so that every fact is stored in one place, using rules called normal forms (1NF, 2NF, 3NF) to remove redundancy and the anomalies it causes.

The constraints and keys article showed how keys link tables. This article answers the question before that one: which tables should exist at all? You will take Malwa Mart's flat bill sheet and normalize it, one normal form at a time.

What problems does normalization solve?

Redundant data causes three kinds of anomaly, meaning a change that leaves the data wrong or impossible to record.

Redundancy also wastes space, but that is the small problem. The big one is that the data stops agreeing with itself, and every report built on it inherits the confusion.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE bill_sheet (order_id INT, order_date TEXT, customer TEXT, phone TEXT, store_area TEXT,
                         product TEXT, category TEXT, price INT, qty INT);
INSERT INTO bill_sheet VALUES
 (101,'2026-03-01','Asha Verma','9826011234','Vijay Nagar','Basmati Rice 5kg','Grocery',520,1),
 (101,'2026-03-01','Asha Verma','9826011234','Vijay Nagar','Detergent 2kg','Household',340,1),
 (101,'2026-03-01','Asha Verma','9826011234','Vijay Nagar','Sev 500g','Snacks',120,1),
 (101,'2026-03-01','Asha Verma','9826011234','Vijay Nagar','Poha 1kg','Grocery',60,1),
 (103,'2026-03-03','Asha Verma','9826011234','Vijay Nagar','Butter 500g','Dairy',275,1),
 (110,'2026-03-12','Asha Verma','9826011234','Palasia','Toor Dal 1kg','Grocery',165,1),
 (110,'2026-03-12','Asha Verma','9826011234','Palasia','Sev 500g','Snacks',120,1),
 (110,'2026-03-12','Asha Verma','9826011234','Palasia','Poha 1kg','Grocery',60,3);
""")
print("Asha's phone is stored", con.execute("SELECT COUNT(*) FROM bill_sheet WHERE customer = 'Asha Verma'").fetchone()[0], "times")

con.execute("UPDATE bill_sheet SET phone = '9993012345' WHERE order_id = 110")   # update anomaly
print("Phones on file for Asha:", con.execute("SELECT DISTINCT phone FROM bill_sheet").fetchall())

con.execute("DELETE FROM bill_sheet WHERE order_id = 103")                        # delete anomaly
print("Butter's price after deleting order 103:",
      con.execute("SELECT price FROM bill_sheet WHERE product = 'Butter 500g'").fetchall())

One careless UPDATE and Asha has two phone numbers. One DELETE and Butter's price of 275 rupees is gone. Neither statement was wrong in itself; the table design made the damage possible.

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

A table is in 1NF when every cell holds a single value, every row is unique and there are no repeating groups of columns.

The intern's first version of the sheet had one row per bill, with all products crammed into a text column: 'Basmati Rice 5kg x1, Detergent 2kg x1, Sev 500g x1, Poha 1kg x1'. Another version had columns item1, item2, item3. Both break 1NF.

The fix is one row per bill line, which is exactly the bill_sheet above. In 1NF you can finally ask "how many packets of poha sold?" with a simple SUM, instead of parsing text with string functions.

-- Not 1NF: a list inside one cell
-- order_id | items
-- 101      | Basmati Rice 5kg x1, Detergent 2kg x1, Sev 500g x1, Poha 1kg x1

-- 1NF: one row per product on the bill, with a key of (order_id, product)
SELECT product, SUM(qty) FROM bill_sheet GROUP BY product;

Second normal form (2NF): no partial dependencies

A table is in 2NF when it is in 1NF and every non-key column depends on the whole primary key, not just part of it. 2NF only matters when the key has more than one column.

The key of bill_sheet is (order_id, product). Now ask of each column, "what do I need to know to look this up?"

• qty needs both: how many poha packets on bill 110. It depends on the whole key.
• price and category need only the product. That is a partial dependency.
• order_date, customer, phone and store_area need only the order. Another partial dependency.

Arrows show what each column depends on. Partial dependencies break 2NF; the dependency of phone on customer, rather than on the key, breaks 3NF.

To reach 2NF, move each group of columns to a table keyed by what it depends on:

1. products (product_id, product_name, category, price), keyed by product.
2. orders (order_id, order_date, customer, phone, store_area), keyed by order.
3. order_items (order_id, product_id, qty), keyed by both, with only qty left.

Butter's price now lives in the products table, so deleting order 103 no longer erases it, and Ghee can be added before anyone buys it.

Third normal form (3NF): no transitive dependencies

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. Such a chain, key to column A to column B, is a transitive dependency.

The 2NF orders table still has two. phone depends on the customer, not on the order: Asha's phone is the same on every bill. And store_area depends on the store. So customers and stores get their own tables, and orders keep only customer_id and store_id as foreign keys.

A common memory aid sums up all three: every non-key column must depend on the key (1NF), the whole key (2NF) and nothing but the key (3NF).

The flat sheet repeats Asha's phone on every line. After 3NF, each fact appears once and foreign keys link the tables.

Rebuilding the bill with joins

Normalization never loses information. A join puts the pieces back together, and the rebuilt bill totals should match the original sheet. A split that can be joined back exactly is called a lossless decomposition.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, phone TEXT UNIQUE);
CREATE TABLE stores (store_id INTEGER PRIMARY KEY, area TEXT);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price INT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, order_date TEXT);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (1,'Asha Verma','9826011234');
INSERT INTO stores VALUES (1,'Vijay Nagar'), (2,'Palasia');
INSERT INTO products VALUES (1,'Basmati Rice 5kg','Grocery',520), (2,'Toor Dal 1kg','Grocery',165),
 (3,'Poha 1kg','Grocery',60), (4,'Butter 500g','Dairy',275), (7,'Detergent 2kg','Household',340), (8,'Sev 500g','Snacks',120);
INSERT INTO orders VALUES (101,1,1,'2026-03-01'), (103,1,1,'2026-03-03'), (110,1,2,'2026-03-12');
INSERT INTO order_items VALUES (101,1,1), (101,7,1), (101,8,1), (101,3,1), (103,4,1), (110,2,1), (110,8,1), (110,3,3);
""")
bill = """
SELECT o.order_id, o.order_date, c.name, s.area, SUM(p.price * i.qty) AS bill_total
FROM orders o
JOIN customers c   ON c.customer_id = o.customer_id
JOIN stores s      ON s.store_id = o.store_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p    ON p.product_id = i.product_id
GROUP BY o.order_id
"""
for row in con.execute(bill):
    print(row)
con.execute("UPDATE customers SET phone = '9993012345' WHERE customer_id = 1")   # one row, done
print("Phones on file for Asha:", con.execute("SELECT phone FROM customers WHERE customer_id = 1").fetchall())

The bill totals come back as 1,040, 275 and 465 rupees, the same amounts stored in Malwa Mart's orders table throughout this series. The phone change is now one row, with no way to leave a stale copy behind.

Normalization vs denormalization: when to stop

Normalized tables are ideal for the system that records sales, because each sale writes small rows and nothing is duplicated. Reporting is different: a dashboard that joins five tables on every refresh can be slow on millions of rows.

So analysts often read from a normalized source and build denormalized summary tables or views for reports. Beyond 3NF there are stricter forms, such as Boyce-Codd normal form (BCNF), 4NF and 5NF. They matter in unusual designs, but 3NF is the practical target for most business databases and for interviews.

Common mistakes with database normalization

• Comma-separated lists in a column. Tags, items or phone numbers in one cell break 1NF and make filtering painful.
• Stopping at 2NF. Customer details inside the orders table look harmless until a customer moves house.
• Storing derived values carelessly. A stored bill_total can disagree with the bill lines; calculate it, or update it with care.
• Over-normalizing. Splitting city names or genders into their own tables rarely helps and adds joins.
• Normalizing a reporting table. Warehouses are often deliberately denormalized for speed.

Being able to explain 2NF and 3NF with a real example is a standard interview question for analyst roles. A classroom data analytics course in Indore with schema design projects at Debugshala helps you practise that, and normal-form practice questions at Debugshala Test check your understanding. Next, SQL indexes show how to keep joins between normalized tables fast.

Key takeaways

• Normalization stores each fact once, which removes update, insert and delete anomalies.
• 1NF: one value per cell and no repeating groups.
• 2NF: no column depends on only part of a composite key.
• 3NF: no column depends on another non-key column.
• Joins rebuild the full picture; reporting systems may denormalize on purpose for speed.