Database normalization for system design

Normal forms in practical terms: why normalize, first, second and third normal form with examples, anomalies that normalization prevents, when and how to denormalize deliberately, and how to present a schema in a system design interview.

Reading is half of it. See this used in a real interview: walk through Design Airbnb →

Interviewers often ask for a schema, and a good schema starts normalized: each fact stored once, in one place. Then, where performance demands it, you denormalize on purpose. Knowing the normal forms (in plain terms) and the anomalies they prevent lets you justify both moves. This guide covers just enough theory to be precise without turning the interview into a database class.

Why normalize

When the same fact is stored in several places, updates can get out of sync:

• Update anomaly: a host changes their name; it is stored on every listing row, and one row is missed.
• Insert anomaly: you cannot record a new host until they have a listing, because host data only lives in the listings table.
• Delete anomaly: deleting a host's last listing also deletes the only record of the host.

Normalization removes these by giving each fact a single home and linking rows by keys.

The normal forms, practically

First normal form (1NF): each column holds a single value; no repeating groups.

• Bad: listings(id, amenities = "wifi,pool,parking").
• Good: a separate listing_amenities(listing_id, amenity) table (or, pragmatically, an array or JSON column when you never query by individual values).

Second normal form (2NF): in a table with a composite key, every non-key column depends on the whole key.

• Bad: bookings_nights(booking_id, night, guest_name) where guest_name depends only on booking_id.
• Good: move guest_name to bookings.

Third normal form (3NF): non-key columns depend only on the key, not on other non-key columns.

• Bad: listings(id, city_id, city_name); city_name depends on city_id.
• Good: cities(id, name) referenced by listings.city_id.

A common summary: every column depends on "the key, the whole key, and nothing but the key". Boyce-Codd normal form and higher forms tighten edge cases; you rarely need to name them in a design interview.

An example schema

For a booking product:

booking_nights with a unique key on (listing_id, night) also prevents double booking. See bookings and reservations and Design Airbnb.

Notice total_amount on bookings: it duplicates what could be computed from prices, but it records what was charged at the time, a fact that must not change when prices change. Snapshots of historical values are not denormalization mistakes; they are different facts.

When to denormalize

Normalized schemas need joins, and joins get expensive at scale or across shards. Denormalize when:

• A hot read path needs data from several tables on every request (a feed item needs the author's name and avatar).
• Counts and aggregates are read far more than they change (likes per post).
• Data is partitioned so joins would cross shards.
• You use a store without joins (key-value, wide-column). See data modelling for Cassandra and DynamoDB.

Ways to denormalize safely:

• Keep the normalized source of truth, and derive denormalized copies from it (via events or change data capture), so they can be rebuilt. See change data capture.
• Store ids plus a cached copy of display fields, and accept brief staleness.
• Maintain counters asynchronously. See counting at scale.
• Use materialized views or read models for complex queries. See event sourcing and CQRS.

Always say what keeps the copies in sync and what happens if they drift. See data modelling and denormalisation.

Transactions and integrity

Normalized relational schemas pair naturally with constraints: primary keys, foreign keys, unique constraints and checks enforce invariants in the database rather than in every code path. For money, inventory and bookings, those constraints are part of correctness. See transactions and isolation.

Indexes follow queries

Normalization decides where facts live; indexes decide how fast you can find them. Index foreign keys used in joins and the columns your main queries filter and sort by. See database indexing.

In the interview

1. Write the core tables, normalized, with keys and the important constraints.
2. Walk through the main queries and add indexes.
3. Identify the hot paths where joins hurt, and denormalize them deliberately, naming the sync mechanism.

For Design Instagram: normalized users, posts, follows and likes; denormalized like counts and author display fields in the feed cache, updated asynchronously. For Design a Payment System: normalized and constrained, with snapshots of amounts and rates at transaction time.

Checklist

• Each fact stored once; anomalies explained if asked.
• 1NF, 2NF and 3NF in plain terms.
• Historical snapshots stored as their own facts.
• Constraints enforce invariants.
• Denormalize hot paths deliberately, derived from the source of truth.
• State the sync mechanism and tolerated staleness.