Normalization: 1NF to 3NF

Analogy categoryAnalogies

Introduction

Normalization is the process of organizing columns and tables to minimize data redundancy and avoid update, insert, and delete anomalies. It proceeds through a series of normal forms, each building on the last: First Normal Form (1NF) requires atomic values and no repeating groups; Second Normal Form (2NF) requires 1NF plus no partial dependency of non-key attributes on part of a composite primary key; Third Normal Form (3NF) requires 2NF plus no transitive dependency, meaning non-key attributes must depend only on the primary key, not on other non-key attributes. This lesson is one of the most frequently asked topics in SQL interviews, so understanding the precise definitions and being able to walk through an example is essential.

🏏

Cricket analogy: Normalization is like separating a scorecard into batting, bowling, and fielding tables instead of one messy sheet, so updating a bowler's economy rate doesn't require editing every batting entry too.

Syntax

sql

-- Unnormalized table: repeating groups and comma-separated values
CREATE TABLE orders_unf (
    order_id     INT PRIMARY KEY,
    customer_name VARCHAR(100),
    products      VARCHAR(255) -- e.g. 'Laptop, Mouse, Keyboard'
);

-- 1NF fix: one product per row, atomic values, no repeating groups
CREATE TABLE orders_1nf (
    order_id      INT,
    customer_name VARCHAR(100),
    product        VARCHAR(100),
    PRIMARY KEY (order_id, product)
);

Explanation

1NF: Each column must hold a single atomic value (not a list), and there should be no repeating groups of columns like product1, product2, product3. The unnormalized table above stores multiple products as one comma-separated string, violating 1NF; splitting it into one row per (order_id, product) fixes this. 2NF: applies only when the primary key is composite. It requires that every non-key column depend on the WHOLE key, not just part of it. In orders_1nf, imagine we add a product_price column — price depends only on product, not on order_id, so it's a partial dependency and violates 2NF; it must be moved to a separate products table keyed by product alone. 3NF: requires that non-key columns depend only on the primary key, not on other non-key columns (no transitive dependency). If an orders table has customer_id -> customer_city (i.e., city depends on customer, not directly on order_id), city should be moved to a customers table.

🏏

Cricket analogy: 1NF means a scorecard can't cram 'Kohli, Rohit, Gill' into one 'batsmen' column; 2NF means if runs depend only on the batsman not the match_id, they move to a separate table; 3NF moves a player's team to a teams table, not repeated per innings.

Example

sql

-- BEFORE (2NF violation): composite key (order_id, product), but
-- product_price depends only on product, not on order_id
CREATE TABLE order_details_bad (
    order_id      INT,
    product        VARCHAR(100),
    quantity       INT,
    product_price  DECIMAL(10,2), -- partial dependency on 'product' only
    customer_id    INT,
    customer_city  VARCHAR(100),  -- transitive: depends on customer_id, not order_id
    PRIMARY KEY (order_id, product)
);

-- AFTER (3NF): dependencies split into separate tables
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_city VARCHAR(100)
);

CREATE TABLE products (
    product       VARCHAR(100) PRIMARY KEY,
    product_price DECIMAL(10,2)
);

CREATE TABLE order_details_good (
    order_id    INT,
    product     VARCHAR(100),
    quantity    INT,
    customer_id INT,
    PRIMARY KEY (order_id, product),
    FOREIGN KEY (product) REFERENCES products(product),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Analysis

In order_details_bad, updating a product's price requires updating every order row containing that product (update anomaly), and if a product has never been ordered it cannot be recorded at all (insertion anomaly). Deleting the last order for a customer would also lose that customer's city (deletion anomaly). After splitting into customers, products, and order_details_good, each fact is stored exactly once: price lives only in products, city lives only in customers, and order_details_good only stores facts that truly depend on the full (order_id, product) key, such as quantity. This eliminates all three anomaly types while preserving the ability to reconstruct the original data via JOINs.

🏏

Cricket analogy: Storing a player's team name on every innings row means renaming a sponsor requires editing thousands of rows (update anomaly); splitting into a teams table means the sponsor name lives in exactly one place.

Key Takeaways

1NF: atomic column values, no repeating groups, each row uniquely identifiable.
2NF: 1NF plus no partial dependency — non-key columns must depend on the entire composite key.
3NF: 2NF plus no transitive dependency — non-key columns must depend only on the primary key.
Normalization reduces update, insert, and delete anomalies at the cost of requiring JOINs to reassemble data.

Practice what you learned

1. Which rule defines First Normal Form (1NF)?

2. A table has composite key (order_id, product) and a column product_price that depends only on product. Which normal form does this violate?

3. A table has primary key order_id and a column customer_city that depends on customer_id (also in the table), not directly on order_id. What does this violate?

4. What is the primary benefit of normalizing a database to 3NF?

Was this page helpful?

Primary and Foreign Keys

Denormalization