Database Normalization (1NF, 2NF, 3NF)

Normalization is the process of organising data into tables so that each fact is stored exactly once. It prevents the classic bugs of spreadsheet-style tables: a customer’s city spelt two ways, a price change that updates some rows but not others, a product that disappears when its last order is deleted. This lesson normalizes one messy table into 3NF, step by step, and builds it in MySQL.

The starting point: one flat table

A shop starts by recording orders in one wide table, exactly as it did in a spreadsheet:

CREATE TABLE flat_orders (  order_id      INT,  order_date    DATE,  customer_name VARCHAR(50),  customer_city VARCHAR(30),  products      VARCHAR(200),  total         INT);INSERT INTO flat_orders VALUES(1, '2025-01-05', 'Ananya Sharma', 'Mumbai', 'Mechanical Keyboard x1, Notebook Pack x5', 4744),(2, '2025-01-17', 'Rohit Kumar',   'Delhi',  'Office Chair x1', 8999),(3, '2025-02-02', 'Ananya Sharma', 'Mumbai', 'Mechanical Keyboard x1, Wireless Mouse x1', 4298);SELECT * FROM flat_orders;

The problems: anomalies

Normalization removes these problems by splitting the table.

First Normal Form (1NF)

Rule: every column holds one atomic value, and there are no repeating groups. Each row is identified by a key.

products breaks 1NF: one cell holds a list. Give each product of an order its own row:

The key is now (order_id, product_name). The stored total is dropped: it can be calculated.

Second Normal Form (2NF)

Rule: 1NF, and every non-key column depends on the whole key, not just part of it. (Only matters when the key has several columns.)

With the key (order_id, product_name):

• quantity depends on both: how many of this product in this order. Fine.
• order_date, customer_name, customer_city depend on order_id alone. Partial dependency.
• price depends on product_name alone. Partial dependency.

Move each partial dependency into its own table: orders (order_id → date, customer), products (product → price) and order_items (order_id + product → quantity).

Third Normal Form (3NF)

Rule: 2NF, and no non-key column depends on another non-key column (no transitive dependency).

In the new orders table, customer_city depends on customer_name, which depends on order_id. City is a fact about the customer, not about the order. Move customers to their own table and keep only a customer_id in orders.

The normalized design in MySQL

CREATE TABLE n_customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL, city VARCHAR(30));CREATE TABLE n_products  (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL UNIQUE, price INT NOT NULL);CREATE TABLE n_orders    (order_id INT PRIMARY KEY, order_date DATE NOT NULL,                          customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES n_customers (customer_id));CREATE TABLE n_order_items (order_id INT, product_id INT, quantity INT NOT NULL,                          PRIMARY KEY (order_id, product_id),                          FOREIGN KEY (order_id) REFERENCES n_orders (order_id),                          FOREIGN KEY (product_id) REFERENCES n_products (product_id));INSERT INTO n_customers VALUES (1, 'Ananya Sharma', 'Mumbai'), (2, 'Rohit Kumar', 'Delhi');INSERT INTO n_products VALUES (1, 'Wireless Mouse', 799), (2, 'Mechanical Keyboard', 3499), (4, 'Office Chair', 8999), (6, 'Notebook Pack', 249);INSERT INTO n_orders VALUES (1, '2025-01-05', 1), (2, '2025-01-17', 2), (3, '2025-02-02', 1);INSERT INTO n_order_items VALUES (1, 2, 1), (1, 6, 5), (2, 4, 1), (3, 2, 1), (3, 1, 1); SELECT o.order_id, o.order_date, c.customer_name, c.city, SUM(oi.quantity * p.price) AS totalFROM n_orders oJOIN n_customers c    ON c.customer_id = o.customer_idJOIN n_order_items oi ON oi.order_id = o.order_idJOIN n_products p     ON p.product_id = oi.product_idGROUP BY o.order_id, o.order_date, c.customer_name, c.cityORDER BY o.order_id;

The anomalies are gone: moving Ananya to Pune is one UPDATE of one row; a new product can be added before anyone orders it; deleting an order no longer deletes the customer. This is exactly the structure of the tutorial’s sample database.

BCNF and beyond

Boyce-Codd Normal Form (BCNF) tightens 3NF: every determinant (a column set that decides another column) must be a candidate key. Most 3NF designs are already in BCNF. 4NF and 5NF deal with multi-valued and join dependencies and are rarely needed in everyday applications.

When to denormalize

Normalized designs are best for data that changes (OLTP systems). Reporting and analytics sometimes denormalize on purpose, storing a calculated order_total or a copy of the customer’s city, to avoid joins on huge tables. Do it deliberately, document it, and keep the copies in sync with triggers or batch jobs.

Normalization FAQ