System Design
1. System Prerequisites

Normalization — 1NF to BCNF

⏱ 0s active on this topic

In simple words

If the same fact is saved in many places — say Alice's city copied into every one of her orders — then when she moves you have to fix every copy, and if you miss one the data contradicts itself. Normalization means arranging your tables so each fact is saved in exactly one place, and other tables just point to it. It is done in steps (called normal forms: 1NF, 2NF, 3NF and so on), each step removing one particular kind of repetition.

What is Normalization?

Normalization is the process of organizing a database to reduce data redundancy and eliminate data anomalies by applying a series of rules called Normal Forms. Data Redundancy means storing same data in more than one physical and logical locations. Data Anomaly means unexpected pattern in data like storing all data in one place.

Un-normalized DB:
  Same data stored in multiple places → update one → others go stale → data inconsistency

Normalized DB:
  Each piece of data stored exactly once → update one place → consistent everywhere

The Problem — Data Anomalies

Before learning normal forms, understand WHY normalization exists.

Start with this poorly designed table that stores everything in one place:

Table: orders_raw (everything crammed in)

┌──────────┬─────────┬───────────┬──────────────────┬─────────┬──────────┬──────────────┬────────────┐
│ order_id │ user_id │ user_name │ user_email       │ city    │ prod_id  │ product_name │ prod_price │
├──────────┼─────────┼───────────┼──────────────────┼─────────┼──────────┼──────────────┼────────────┤
│ 1001     │ 1       │ Alice     │ alice@email.com  │ Mumbai  │ P01      │ Shoes        │ 500        │
│ 1002     │ 1       │ Alice     │ alice@email.com  │ Mumbai  │ P02      │ T-shirt      │ 200        │
│ 1003     │ 2       │ Bob       │ bob@email.com    │ Delhi   │ P01      │ Shoes        │ 500        │
│ 1004     │ 2       │ Bob       │ bob@email.com    │ Delhi   │ P03      │ Watch        │ 1500       │
└──────────┴─────────┴───────────┴──────────────────┴─────────┴──────────┴──────────────┴────────────┘

This has three types of problems:

1. Update Anomaly

Alice moves from Mumbai to Delhi. You must update EVERY row where user_id = 1. Miss one → Alice lives in two cities simultaneously.

Update bob@email.com to bob@gmail.com
→ Must change 2 rows
→ If one row is missed → same person has two different emails

2. Insertion Anomaly

You want to add a new product (Bag, ₹800) to the catalog. But there is no order for it yet — you CANNOT insert it because order_id cannot be NULL (it's part of the key).

Cannot add product without an order → data about products is trapped inside orders

3. Deletion Anomaly

Carol (user_id = 3) cancels her only order. You delete that row → Carol's user information is GONE from the database entirely.

Deleting an order deletes user data too → unintended data loss

Functional Dependency — The Core Concept

A functional dependency X → Y means: "knowing X, you can always determine Y."

user_id → user_name        (knowing user_id tells you the user's name)
user_id → email            (knowing user_id tells you the email)
product_id → product_name  (knowing product_id tells you the product name)
order_id → order_date      (knowing order_id tells you the date)

order_id → user_id         (knowing order_id tells you which user placed it)

But:
user_id does NOT → product_name   (knowing a user doesn't tell you a product)

Types of Functional Dependencies

Full dependency:        X → Y where Y depends on ALL of X
                        (order_id, product_id) → quantity
                        quantity needs BOTH order_id AND product_id

Partial dependency:     X → Y where Y depends on PART of X
                        (order_id, product_id) → product_name
                        product_name only needs product_id, not order_id
                        This is a partial dependency — violation of 2NF

Transitive dependency:  X → Z where X → Y and Y → Z
                        user_id → city_id → city_name
                        city_name depends on city_id, not directly on user_id
                        This is a transitive dependency — violation of 3NF

1NF — First Normal Form

Rule

  1. Every column must hold atomic (indivisible) values — no lists, no sets, no arrays
  2. Every row must be unique (have a primary key)
  3. No repeating groups of columns

❌ Violates 1NF

Table: orders_bad

┌──────────┬─────────┬──────────────────────────────────┐
│ order_id │ user_id │ products                         │
├──────────┼─────────┼──────────────────────────────────┤
│ 1001     │ 1       │ Shoes, T-shirt                   │ ← multiple values in one cell
│ 1002     │ 2       │ Watch                            │
└──────────┴─────────┴──────────────────────────────────┘

Also bad — repeating columns:
┌──────────┬───────────┬──────────────┬──────────────┐
│ order_id │ product_1 │ product_2    │ product_3    │
├──────────┼───────────┼──────────────┼──────────────┤
│ 1001     │ Shoes     │ T-shirt      │ NULL         │
└──────────┴───────────┴──────────────┴──────────────┘

✅ Converted to 1NF

Split multi-valued cells into separate rows:

┌──────────┬─────────┬──────────┐
│ order_id │ user_id │ prod_id  │
├──────────┼─────────┼──────────┤
│ 1001     │ 1       │ P01      │
│ 1001     │ 1       │ P02      │
│ 1002     │ 2       │ P03      │
└──────────┴─────────┴──────────┘
PK = (order_id, prod_id)

1NF achieved: each cell holds one atomic value, each row is unique.

2NF — Second Normal Form

Rule

  1. Must be in 1NF
  2. No partial dependencies — every non-key column must depend on the ENTIRE primary key, not just part of it

2NF only applies when the primary key is composite (multiple columns).

Current table after 1NF (with more columns):

Table: order_products (PK = order_id + prod_id)

┌──────────┬─────────┬──────────┬──────────────┬──────────┬─────────────────┐
│ order_id │ prod_id │ quantity │ product_name │ price    │ order_date      │
├──────────┼─────────┼──────────┼──────────────┼──────────┼─────────────────┤
│ 1001     │ P01     │ 1        │ Shoes        │ 500      │ 2024-01-10      │
│ 1001     │ P02     │ 2        │ T-shirt      │ 200      │ 2024-01-10      │
│ 1002     │ P01     │ 1        │ Shoes        │ 500      │ 2024-01-11      │
└──────────┴─────────┴──────────┴──────────────┴──────────┴─────────────────┘

Functional dependencies:
(order_id, prod_id) → quantity       ✅ full dependency — needs both
prod_id             → product_name   ❌ partial — only needs prod_id
prod_id             → price          ❌ partial — only needs prod_id
order_id            → order_date     ❌ partial — only needs order_id

❌ Partial Dependencies (violates 2NF)

product_name and price depend only on prod_id — not on the full PK
order_date depends only on order_id — not on the full PK

Result:
- "Shoes" stored in EVERY row that includes P01 → update anomaly
- Change Shoes price → update 100 rows → miss one → inconsistent data

✅ Converted to 2NF

Split tables so each column depends on its ENTIRE key:

Table: order_items (PK = order_id + prod_id)       Table: orders (PK = order_id)
┌──────────┬─────────┬──────────┐                  ┌──────────┬────────────┐
│ order_id │ prod_id │ quantity │                  │ order_id │ order_date │
├──────────┼─────────┼──────────┤                  ├──────────┼────────────┤
│ 1001     │ P01     │ 1        │                  │ 1001     │ 2024-01-10 │
│ 1001     │ P02     │ 2        │                  │ 1002     │ 2024-01-11 │
│ 1002     │ P01     │ 1        │                  └──────────┴────────────┘
└──────────┴─────────┴──────────┘

Table: products (PK = prod_id)
┌─────────┬──────────────┬───────┐
│ prod_id │ product_name │ price │
├─────────┼──────────────┼───────┤
│ P01     │ Shoes        │ 500   │
│ P02     │ T-shirt      │ 200   │
└─────────┴──────────────┴───────┘

2NF achieved: every non-key column depends on the ENTIRE primary key.

3NF — Third Normal Form

Rule

  1. Must be in 2NF
  2. No transitive dependencies — non-key columns must NOT depend on other non-key columns

Current orders table (extended with user data):

Table: orders (PK = order_id)

┌──────────┬─────────┬────────────┬───────────┬────────────────┬─────────┐
│ order_id │ user_id │ order_date │ user_name │ user_email     │ city    │
├──────────┼─────────┼────────────┼───────────┼────────────────┼─────────┤
│ 1001     │ 1       │ 2024-01-10 │ Alice     │ alice@email.com│ Mumbai  │
│ 1002     │ 1       │ 2024-01-11 │ Alice     │ alice@email.com│ Mumbai  │
│ 1003     │ 2       │ 2024-01-12 │ Bob       │ bob@email.com  │ Delhi   │
└──────────┴─────────┴────────────┴───────────┴────────────────┴─────────┘

Functional dependencies:
order_id → user_id       ✅ direct
order_id → order_date    ✅ direct
order_id → user_name     ❌ TRANSITIVE: order_id → user_id → user_name
order_id → user_email    ❌ TRANSITIVE: order_id → user_id → user_email
order_id → city          ❌ TRANSITIVE: order_id → user_id → city

❌ Transitive Dependencies (violates 3NF)

user_name, user_email, city all depend on user_id — not directly on order_id

Result:
- Alice appears in every row for her orders → update anomaly
- Change Alice's city → update multiple rows in orders table
- Delete Alice's last order → lose Alice's contact info

✅ Converted to 3NF

Table: orders (PK = order_id)        Table: users (PK = user_id)
┌──────────┬─────────┬────────────┐  ┌─────────┬───────────┬────────────────┬─────────┐
│ order_id │ user_id │ order_date │  │ user_id │ user_name │ user_email     │ city    │
├──────────┼─────────┼────────────┤  ├─────────┼───────────┼────────────────┼─────────┤
│ 1001     │ 1       │ 2024-01-10 │  │ 1       │ Alice     │ alice@email.com│ Mumbai  │
│ 1002     │ 1       │ 2024-01-11 │  │ 2       │ Bob       │ bob@email.com  │ Delhi   │
│ 1003     │ 2       │ 2024-01-12 │  └─────────┴───────────┴────────────────┴─────────┘
└──────────┴─────────┴────────────┘

3NF achieved: non-key columns depend ONLY on the primary key — nothing else.

BCNF — Boyce-Codd Normal Form

Rule

  1. Must be in 3NF
  2. For every functional dependency X → Y, X must be a superkey (X must uniquely identify rows — it must be a candidate key)

BCNF is a stricter version of 3NF. Most tables in 3NF are also in BCNF. Violations only occur with overlapping composite candidate keys.

BCNF Violation Example

Table: course_teacher (a student can take a course from only one teacher,
                        a teacher teaches only one course,
                        but a course can be taught by multiple teachers)

┌──────────┬─────────┬──────────────┐
│ student  │ course  │ teacher      │
├──────────┼─────────┼──────────────┤
│ Alice    │ Math    │ Prof. Kumar  │
│ Alice    │ Physics │ Prof. Sharma │
│ Bob      │ Math    │ Prof. Patel  │
│ Carol    │ Math    │ Prof. Kumar  │
└──────────┴─────────┴──────────────┘

Candidate keys:
  (student, course) → uniquely identifies a row ✅
  (student, teacher) → uniquely identifies a row ✅

Functional dependency:
  teacher → course   (each teacher teaches only one course)

But: teacher is NOT a superkey (teacher alone doesn't identify a row)
→ BCNF violated

✅ Converted to BCNF

Table: student_teacher           Table: teacher_course
┌──────────┬──────────────┐      ┌──────────────┬─────────┐
│ student  │ teacher      │      │ teacher      │ course  │
├──────────┼──────────────┤      ├──────────────┼─────────┤
│ Alice    │ Prof. Kumar  │      │ Prof. Kumar  │ Math    │
│ Alice    │ Prof. Sharma │      │ Prof. Sharma │ Physics │
│ Bob      │ Prof. Patel  │      │ Prof. Patel  │ Math    │
│ Carol    │ Prof. Kumar  │      └──────────────┴─────────┘
└──────────┴──────────────┘

Normal Forms — Visual Summary

Raw Table (Un-normalized)
        ↓
┌─────────────────────────────────────────────────────────────┐
│ 1NF — Atomic values, no repeating groups, has primary key   │
└─────────────────────────────────────────────────────────────┘
        ↓
┌─────────────────────────────────────────────────────────────┐
│ 2NF — 1NF + no partial dependencies                         │
│       (non-key columns depend on FULL composite PK)         │
│       Only relevant when PK is composite                    │
└─────────────────────────────────────────────────────────────┘
        ↓
┌─────────────────────────────────────────────────────────────┐
│ 3NF — 2NF + no transitive dependencies                      │
│       (non-key columns depend ONLY on PK, not on other      │
│        non-key columns)                                     │
└─────────────────────────────────────────────────────────────┘
        ↓
┌─────────────────────────────────────────────────────────────┐
│ BCNF — 3NF + every determinant is a superkey               │
│        Stricter than 3NF, eliminates subtle redundancies    │
└─────────────────────────────────────────────────────────────┘

Quick Check — Violation Identifier

Is any cell storing multiple values?          → violates 1NF
Does a non-key column depend on PART of a
  composite PK?                               → violates 2NF
Does a non-key column depend on another
  non-key column?                             → violates 3NF
Does a functional dependency exist where
  the left side is not a superkey?            → violates BCNF

The Forms Nest — Both Directions

Each form is literally "the previous form + one more rule", so:

SATISFYING a higher form guarantees the lower ones:
  BCNF ✅  →  3NF ✅  →  2NF ✅  →  1NF ✅

SATISFYING a lower form guarantees NOTHING above it:
  1NF ✅   →  says nothing about 2NF / 3NF / BCNF

VIOLATING a lower form breaks every form above it:
  1NF ❌   →  2NF ❌, 3NF ❌, BCNF ❌
  3NF ❌   →  BCNF ❌   (but it could still be 2NF ✅ while failing 3NF)

So when you spot a multi-valued cell, you don't need to check anything else — the table fails 1NF and therefore every form. Work bottom-up: fix 1NF, then 2NF, then 3NF.

Denormalization — Going Backwards (Important for System Design)

Normalization is ideal for data integrity. But at scale, normalized tables require many JOINs → JOINs across millions of rows → slow queries.

Denormalization intentionally introduces redundancy to improve read performance.

When to Denormalize

Normalized (3NF):
  To get a user's order with product names:
  users JOIN orders JOIN order_items JOIN products
  4-table JOIN → expensive at 100M rows

Denormalized:
  orders_summary table stores:
  order_id, user_name, user_email, product_names (JSON array), total_amount
  → single table read, no joins → fast
  → but user_name is duplicated → update Alice's name → must update orders_summary too

Strategies

1. Store computed columns
   Add total_amount to orders table (sum of order_items)
   Update it when items are added/removed
   → fast reads, but write complexity increases

2. Duplicate frequently joined columns
   Add user_name and user_email to orders table
   → fast reads, but must keep in sync with users table

3. Pre-aggregated tables (summary tables)
   daily_revenue table: (date, total_revenue, total_orders)
   Updated by a background job daily
   → dashboard reads from this instead of aggregating orders in real time

4. Read replicas + materialized views
   Not denormalization per se, but achieves the same goal
   → normalize the source, denormalize the read model

The Tradeoff

Normalization  →  write-optimized, consistent, storage-efficient, slow complex reads
Denormalization →  read-optimized, redundant, inconsistent risk, fast reads

System Design Context

Real-World Normalization Decisions

Flipkart product catalog:

Products table (normalized):
  products → categories (FK)
  products → brands (FK)
  products → product_attributes (1:N for flexible attributes)

For search/display: denormalized Elasticsearch index stores everything flat
  product_id, name, brand_name, category_name, price, attributes...
  → fast search, no joins at search time
  → synced from normalized DB via CDC (Change Data Capture)

Twitter/X feed:

Normalized: tweets table, users table (JOIN to get username + avatar)
Denormalized: tweet_feed stores user_handle + avatar_url inline
  → reading 100 tweets doesn't need 100 JOINs to users table
  → downside: if user changes username, all their cached tweets show old name
  → Twitter accepts eventual consistency for username display

Interview Decision Framework

Use normalized (3NF) when:
  - Data integrity is critical (financial, medical)
  - Write-heavy workload
  - Small-medium scale (< 10M rows)
  - Complex queries that vary (reporting, analytics)

Denormalize when:
  - Read-heavy, read patterns are known and fixed
  - Scale requires removing JOINs (100M+ rows)
  - Latency requirements are strict (< 10ms per query)
  - The data is derived/computed (can always be recalculated)

How This Connects

  • Relational Model - Tables, Keys, Relationships — normalization decides which tables the relational model should have; candidate keys and functional dependencies are the raw material.
  • SQL - Joins, Aggregations, Subqueries — every table you split is a JOIN you now have to write; normalization is why JOINs exist.
  • Indexing - B-Tree & Hash Indexes — more tables means more foreign-key columns that need indexes to keep those JOINs fast.
  • ACID Properties — the anomalies normalization prevents are consistency (C) problems; denormalization trades some of that guarantee for speed.
  • Module 4 — Data Architecture / Storage Engines — denormalized read models, materialized views, search indexes synced by CDC (Change Data Capture) are normalization run in reverse for scale.
  • NoSQL Types — document stores deliberately denormalize (embed related data) so a read is one lookup, not a join.

In the Interview

When to say it. Any "design the schema" prompt, any "what's wrong with this table?", and whenever you propose denormalization you should be able to say what normal form you're breaking and why it's worth it.

The ~20-second version:

"Normalization removes redundancy so each fact is stored once, which prevents update, insertion, and deletion anomalies. 1NF means atomic cell values and a primary key — no comma-separated lists. 2NF applies when the key is composite: every non-key column must depend on the whole key, not part of it — product_name in an (order_id, product_id) table depends only on product_id, so it moves to a products table. 3NF means no non-key column depends on another non-key column — user_city in orders depends on user_id, not order_id, so it moves to users. I normalize transactional data to 3NF, then denormalize specific read-heavy paths — a summary table or a flat search index — accepting the sync cost for the latency win."

Follow-ups to expect:

  1. "What's a functional dependency?" — X → Y means knowing X always determines Y. Partial dependency (on part of a composite key) breaks 2NF; transitive (non-key → non-key) breaks 3NF.
  2. "3NF vs BCNF?" — BCNF is stricter: every determinant must be a candidate key. They differ only when you have overlapping composite candidate keys — rare in practice.
  3. "Why not always denormalize for speed?" — Every duplicated value can drift out of sync; you take on triggers/jobs/app logic to keep copies consistent, and writes get more expensive.
  4. "How do you keep a denormalized copy consistent?" — Update it in the same transaction, or via CDC / an async job, and accept eventual consistency for non-critical fields (e.g. a cached display name).

Mistake to avoid: reciting "2NF removes partial dependencies" without being able to point at the offending column and name the dependency. Interviewers probe with a concrete table — practice spotting the FD, not the definition.

Quick Revision — 6 Lines

  1. Normalization removes redundancy and prevents update / insert / delete anomalies
  2. 1NF — atomic values, no repeating groups, must have a primary key
  3. 2NF — no partial dependencies (non-key must depend on FULL composite PK)
  4. 3NF — no transitive dependencies (non-key must depend ONLY on PK, not other non-key cols)
  5. BCNF — stricter 3NF: every determinant in any FD must be a superkey
  6. Denormalize deliberately for read-heavy systems — trade consistency risk for speed

Quick-revision quiz

Question 1 of 18
Name the three data anomalies normalization prevents, with a one-line example of each.