Skip to content
LLD Learn/Relational Modeling Foundations
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Normalization: 1NF, 2NF, 3NF

5 min read

You'll learn to

  • -Explain 1NF, 2NF and 3NF each as the fix for one concrete bug, not as theory
  • -Name the three anomalies (update, insert, delete) and show each one on a small table
  • -Run the "key, the whole key, and nothing but the key" test on a table

Normalization is a set of rules for structuring tables so each fact is stored once. Storing a fact in several places is called redundancy, and it causes anomalies, meaning bugs that come from the structure itself. An update anomaly is when you change a fact in one place and forget the others. An insert anomaly is when you cannot record one fact without inventing another. A delete anomaly is when deleting one thing destroys a different fact. Each normal form removes a specific, nameable problem, which turns "database theory" into a practical tool.

An Analogy: Moving House

When you pack boxes for a move, you do not write the new address inside every box on a hundred separate sticky notes. You keep it in one address book and refer to it. If the address changes, you correct one page. Normalization is that habit applied to tables: keep each fact in one place and point at it.

First Normal Form (1NF): No Repeating Groups

1NF requires every column to hold a single, atomic value. Atomic means it cannot be usefully split further. A comma-separated list in one column, or repeated columns for the same kind of value, breaks it.

A 1NF violation, and the fix
-- Violates 1NF: a single column holding a variable-length list
CREATE TABLE cargo_holds (
    id INTEGER PRIMARY KEY,
    hold_name TEXT,
    items TEXT   -- "crates, barrels, luggage, mail sacks" - not atomic!
);

-- 1NF-compliant: one row per (hold, item) pair
CREATE TABLE cargo_items (
    id INTEGER PRIMARY KEY,
    hold_id INTEGER NOT NULL,
    item_type TEXT NOT NULL,
    FOREIGN KEY (hold_id) REFERENCES cargo_holds(id)
);

The concrete bug: to find which holds contain barrels in a comma-separated column you need fragile text matching with LIKE '%barrels%', which would also match a hold that lists "no barrels". Adding or removing one item means rewriting the whole string. With one row per item, both are ordinary indexed queries.

Second Normal Form (2NF): No Partial Dependencies

2NF matters for tables whose primary key is made of more than one column, called a composite key. It requires every non-key column to depend on the whole key, not just part of it. In the example below the key is (hold_id, item_type), but mail_room_name depends only on hold_id.

A 2NF violation - mail_room_name only depends on hold_id, not the full composite key
-- Violates 2NF: composite key (hold_id, item_type), but mail_room_name
-- only actually depends on hold_id - a partial dependency
CREATE TABLE mail_sorting (
    hold_id INTEGER,
    item_type TEXT,
    quantity INTEGER,
    mail_room_name TEXT,     -- depends only on hold_id, not on item_type
    PRIMARY KEY (hold_id, item_type)
);

-- 2NF-compliant: split out the partially-dependent column
CREATE TABLE mail_holds (
    hold_id INTEGER PRIMARY KEY,
    mail_room_name TEXT
);
CREATE TABLE mail_sorting (
    hold_id INTEGER,
    item_type TEXT,
    quantity INTEGER,
    PRIMARY KEY (hold_id, item_type),
    FOREIGN KEY (hold_id) REFERENCES mail_holds(hold_id)
);

The concrete bug: suppose hold 4 stores three item types, so three rows. The mail room's name is repeated in all three. If the mail room is renamed, you must update three rows, and missing one leaves the table contradicting itself. After the split, the name is stored once.

Third Normal Form (3NF): No Transitive Dependencies

3NF requires that non-key columns depend only on the primary key, and not on some other non-key column. A transitive dependency is one where a column depends on the key only through another column. Below, lifeboat_capacity depends on lifeboat_id, a non-key column, not on allocation_id.

A 3NF violation - lifeboat_capacity depends on lifeboat_id, not directly on the row's key
-- Violates 3NF: allocation_id is the key, but lifeboat_capacity
-- depends on lifeboat_id (a non-key column), not on allocation_id directly
CREATE TABLE lifeboat_allocations (
    allocation_id INTEGER PRIMARY KEY,
    passenger_id INTEGER NOT NULL,
    lifeboat_id INTEGER NOT NULL,
    lifeboat_capacity INTEGER    -- transitively dependent on lifeboat_id, not allocation_id
);

-- 3NF-compliant: capacity belongs on the lifeboats table itself
CREATE TABLE lifeboats (
    id INTEGER PRIMARY KEY,
    capacity INTEGER NOT NULL
);
CREATE TABLE lifeboat_allocations (
    allocation_id INTEGER PRIMARY KEY,
    passenger_id INTEGER NOT NULL,
    lifeboat_id INTEGER NOT NULL,
    FOREIGN KEY (lifeboat_id) REFERENCES lifeboats(id)
);

Count the damage with numbers. Lifeboat 4 holds 40 people and appears in 25 allocation rows, so its capacity is stored 25 times. If the capacity becomes 50, you must change 25 rows (an update anomaly), and missing one leaves two answers. If lifeboat 9 has no passengers yet, there is no row to hold its capacity at all (an insert anomaly). And deleting the last allocation for lifeboat 4 deletes the only copy of its capacity (a delete anomaly). After the split the capacity lives once in the lifeboats table, and all three problems disappear.

What Each Normal Form Actually Fixes
Eliminates repeating groups in a single column
1NF
Eliminates columns depending on only part of a composite key
2NF
Eliminates columns depending on another non-key column
3NF

The lifeboat scenario returns in the next chapter's Lab level, this time for a different lesson: enforcing capacity limits with CHECK constraints. The schema shape, a separate lifeboats table, is the one 3NF just derived.

A fast test for 3NF: for every non-key column, ask "does this depend on the key, the whole key, and nothing but the key?" That one sentence captures all three normal forms.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Normalize Cargo Hold Cleanup and Mail Room Sorting in the LLD Lab's Titanic act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!