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

Normalization is the process of organising data into tables so that each fact is stored exactly once. It's why ShopKart has separate customers, orders, order_items and products tables instead of one giant spreadsheet. Understanding it explains why you need joins, and it's a standard interview topic.

The problem: one big table

Imagine ShopKart started with a single sheet:

CREATE TABLE order_sheet (
  order_id      INT,
  order_date    DATE,
  customer_name VARCHAR(100),
  customer_city VARCHAR(50),
  products      TEXT,          -- comma-separated list
  category      VARCHAR(50),
  total_amount  NUMERIC(10,2)
);

INSERT INTO order_sheet VALUES
(1001, '2025-01-05', 'Aarav Sharma', 'Bengaluru', 'Redmi Note 13 Pro, boAt Airdopes 141', 'Electronics', 24197.10),
(1011, '2025-06-30', 'Aarav Sharma', 'Bengaluru', 'Running Shoes, Steel Water Bottle 1L', 'Fashion, Home & Kitchen', 3097.20),
(1003, '2025-02-02', 'Rohan Gupta',  'Delhi',     'Samsung 43" Smart TV, Steel Water Bottle 1L', 'Electronics, Home & Kitchen', 28838.50);

SELECT * FROM order_sheet;

Result:

(3 rows)

It looks simple, but it causes anomalies:

-- Counting a product means pattern-matching text: fragile and slow
SELECT COUNT(*) AS orders_with_bottle
FROM order_sheet
WHERE products LIKE '%Water Bottle%';

Result:

(1 row)

First Normal Form (1NF): one value per cell

Rule: every column holds a single, atomic value, and there are no repeating groups.

The products and category columns break 1NF. Fix it with one row per product in the order:

order_id | order_date | customer_name | customer_city | product_name          | category
1001     | 2025-01-05 | Aarav Sharma  | Bengaluru     | Redmi Note 13 Pro     | Electronics
1001     | 2025-01-05 | Aarav Sharma  | Bengaluru     | boAt Airdopes 141     | Electronics
1011     | 2025-06-30 | Aarav Sharma  | Bengaluru     | Running Shoes         | Fashion
...

Now each cell has one value, but order and customer details repeat on every line.

Second Normal Form (2NF): no partial dependencies

Rule: be in 1NF, and every non-key column must depend on the whole key.

The 1NF table's key is (order_id, product_name). But order_date and customer_name depend only on order_id, and category depends only on product_name. Split them out:

• orders (order_id, order_date, customer_name, customer_city)
• products (product_name, category)
• order_items (order_id, product_name, quantity)

Third Normal Form (3NF): no transitive dependencies

Rule: be in 2NF, and non-key columns must depend only on the key, not on other non-key columns.

In orders, customer_city depends on customer_name, not on order_id. Move customer details to their own table and keep only a key in orders. Likewise, give categories their own table. The result is exactly the ShopKart design:

customers  (customer_id PK, customer_name, city, ...)
orders     (order_id PK, customer_id FK, order_date, status, ...)
order_items(order_item_id PK, order_id FK, product_id FK, quantity, unit_price, ...)
products   (product_id PK, product_name, category_id FK, price, ...)
categories (category_id PK, category_name)

A common way to remember it: every non-key column depends on the key, the whole key, and nothing but the key.

The normalized design answers questions cleanly

The water-bottle question becomes an exact join instead of text matching:

SELECT p.product_name, SUM(oi.quantity) AS units_sold, COUNT(DISTINCT oi.order_id) AS orders
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE p.product_name = 'Steel Water Bottle 1L'
GROUP BY p.product_name;

Result:

(1 row)

And changing a customer's city is a single-row update.

Denormalization: when analysts flatten on purpose

Normalized tables are ideal for writing data (apps, transactions). For reading at scale, data warehouses often denormalize:

• Star schema: a central fact table (one row per order line, with amounts) surrounded by dimension tables (customer, product, date). Fewer joins, simpler BI queries.
• Wide tables / views: a pre-joined table like the order_lines view from the views lesson, so dashboards don't repeat five-table joins.

-- A denormalized analysis table built from the normalized source
CREATE TABLE fact_order_lines AS
SELECT oi.order_item_id, o.order_date, c.city, cat.category_name, p.product_name,
       oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100) AS net_amount
FROM order_items oi
JOIN orders o       ON o.order_id = oi.order_id
JOIN customers c    ON c.customer_id = o.customer_id
JOIN products p     ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
WHERE o.status = 'Delivered';

SELECT category_name, ROUND(SUM(net_amount), 0) AS revenue
FROM fact_order_lines
GROUP BY category_name
ORDER BY revenue DESC
LIMIT 3;

Result:

(3 rows)

The trade-off: faster, simpler reads, but the copy must be rebuilt when the source changes.

Common mistakes

• Storing lists in one column ('Red, Blue, Green'). That breaks 1NF and makes filtering painful.
• Storing derived values that can go stale, such as a customer's total_orders count, without a process to keep it updated.
• Over-normalizing, for example splitting first and last name into separate tables. Normalize until anomalies disappear, not further.

Practice question

A table employee_projects(employee_id, employee_name, department, project_code, project_name, hours) has the key (employee_id, project_code). Which columns break 2NF, and how would you split the table?

Key takeaways

• Normalization stores each fact once, preventing update, insert and delete anomalies.
• 1NF: atomic values. 2NF: no partial dependency on a composite key. 3NF: no dependency between non-key columns.
• Normalized schemas need joins to answer questions.
• Analytics often denormalizes on purpose (star schemas, wide tables) for faster reads.