Normalisation

// Part 01

The Problem That Normalisation Solves

// Part 02

The Normal Forms — A Progressive Hierarchy

// Part 03

First Normal Form (1NF) — Atomic Values

-- Violation 1: Multiple values in one cell (comma-separated list)
-- products table with a 'tags' column
product_id | product_name   | tags
1          | Amul Butter    | 'dairy, refrigerated, fat'
-- PROBLEM: 'dairy, refrigerated, fat' is three values in one cell
-- Cannot query: WHERE tags = 'dairy' -- this won't work

-- Violation 2: Repeating column groups
-- orders table with multiple product columns
order_id | product_1   | qty_1 | product_2  | qty_2 | product_3 | qty_3
1001     | Amul Butter | 2     | Tata Salt  | 1     | NULL      | NULL
-- PROBLEM: How do you handle an order with 10 products?
-- Must add more columns. Queries are complex. NULLs everywhere.

-- Violation 3: Non-atomic composite value
customer_id | full_address
1           | '204 MG Road, Koramangala, Seattle 560001'
-- PROBLEM: Cannot query by city alone without string parsing

-- Fix 1: Move multi-valued attribute to a separate table
-- Instead of tags column in products:
CREATE TABLE product_tags (
  product_id  INTEGER REFERENCES products(product_id),
  tag         VARCHAR(50) NOT NULL,
  PRIMARY KEY (product_id, tag)
);
-- One row per tag per product — fully atomic

-- Fix 2: Move repeating groups to a separate table
-- Instead of product_1, qty_1, product_2, qty_2 columns in orders:
CREATE TABLE order_items (
  order_id    INTEGER REFERENCES orders(order_id),
  product_id  INTEGER REFERENCES products(product_id),
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
-- One row per product per order — handles any number of products

-- Fix 3: Split composite address into atomic columns
CREATE TABLE customers (
  customer_id     INTEGER PRIMARY KEY,
  street_address  VARCHAR(200),
  locality        VARCHAR(100),
  city            VARCHAR(100),
  state           VARCHAR(100),
  zip_code         VARCHAR(10)
);
-- Each address component queryable independently

// Part 04

Second Normal Form (2NF) — No Partial Dependencies

-- Imagine order_items stored product details alongside order details:
CREATE TABLE order_items_bad (
  order_id      INTEGER,
  product_id    INTEGER,
  quantity      INTEGER,
  unit_price    DECIMAL(10,2),
  -- These columns violate 2NF:
  product_name  VARCHAR(200),  -- depends only on product_id, not on order_id
  category      VARCHAR(100),  -- depends only on product_id
  brand         VARCHAR(100),  -- depends only on product_id
  PRIMARY KEY (order_id, product_id)
);

-- Composite PK: (order_id, product_id)
-- Dependency analysis:
--   quantity    → depends on (order_id, product_id) BOTH ✓
--   unit_price  → depends on (order_id, product_id) BOTH ✓ (price at time of order)
--   product_name → depends only on product_id ✗ PARTIAL DEPENDENCY
--   category    → depends only on product_id ✗ PARTIAL DEPENDENCY
--   brand       → depends only on product_id ✗ PARTIAL DEPENDENCY

-- Problems this causes:
-- If Amul Butter's name changes: must update EVERY order_item row
-- If a product is ordered 50,000 times: product_name stored 50,000 times

-- BEFORE (2NF violation):
order_items(order_id, product_id, quantity, unit_price, product_name, category, brand)

-- AFTER (2NF compliant — split into two tables):
-- Table 1: Facts about the order line item (depend on full PK)
CREATE TABLE order_items (
  order_id    INTEGER       NOT NULL REFERENCES orders(order_id),
  product_id  INTEGER       NOT NULL REFERENCES products(product_id),
  quantity    INTEGER       NOT NULL,
  unit_price  DECIMAL(10,2) NOT NULL,  -- price AT TIME of order (valid here)
  line_total  DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- Table 2: Facts about the product (depend only on product_id)
CREATE TABLE products (
  product_id    INTEGER       PRIMARY KEY,
  product_name  VARCHAR(200)  NOT NULL,
  category      VARCHAR(100)  NOT NULL,
  brand         VARCHAR(100),
  unit_price    DECIMAL(10,2) NOT NULL  -- current price (may differ from order price)
);

-- Now product_name is stored ONCE in products
-- order_items joins to products to get the name when needed

// Part 05

Third Normal Form (3NF) — No Transitive Dependencies

-- Imagine customers stored with city and state together:
CREATE TABLE customers_bad (
  customer_id  INTEGER  PRIMARY KEY,
  first_name   VARCHAR(100),
  email        VARCHAR(255),
  zip_code      VARCHAR(10),
  city         VARCHAR(100),   -- determined by zip_code (transitive!)
  state        VARCHAR(100)    -- determined by zip_code (transitive!)
);

-- Dependency chain:
-- customer_id → zip_code    (direct: each customer has one zip_code)
-- zip_code → city           (a zip_code determines a city)
-- zip_code → state          (a zip_code determines a state)
-- Therefore: customer_id → zip_code → city  (TRANSITIVE)

-- Problems:
-- If Seattle's zip_code 560001 is reassigned to another city:
--   Must update EVERY customer row with that zip_code
-- Storage: city/state repeated for every customer with the same zip_code
-- Inconsistency risk: two customers with same zip_code can have different cities

-- Another classic 3NF violation:
CREATE TABLE employees_bad (
  employee_id  INTEGER  PRIMARY KEY,
  first_name   VARCHAR(100),
  department   VARCHAR(100),
  dept_head    VARCHAR(100)   -- determined by department, not employee_id!
);
-- employee_id → department → dept_head  (TRANSITIVE)

-- BEFORE (3NF violation):
customers(customer_id, first_name, email, zip_code, city, state)

-- AFTER (3NF compliant):
-- Table 1: Customer facts that depend directly on customer_id
CREATE TABLE customers (
  customer_id  INTEGER      PRIMARY KEY,
  first_name   VARCHAR(100) NOT NULL,
  email        VARCHAR(255) NOT NULL UNIQUE,
  zip_code      VARCHAR(10)
  -- city and state removed — they belong in a zip_codes table
);

-- Table 2: Zip Code facts (city and state depend on zip_code, not customer)
CREATE TABLE zip_codes (
  zip_code  VARCHAR(10)   PRIMARY KEY,
  city     VARCHAR(100)  NOT NULL,
  state    VARCHAR(100)  NOT NULL
);

-- employees fix:
CREATE TABLE departments (
  department  VARCHAR(100) PRIMARY KEY,
  dept_head   VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  employee_id  INTEGER       PRIMARY KEY,
  first_name   VARCHAR(100)  NOT NULL,
  department   VARCHAR(100)  REFERENCES departments(department)
  -- dept_head removed — it depends on department, not employee_id
);

// Part 06

FreshCart — Full Normalisation Walkthrough

-- Single flat table with everything mixed together
orders_flat(
  order_id,
  customer_name,    -- should be in customers
  customer_email,   -- should be in customers
  customer_city,    -- should be in customers
  product_name,     -- should be in products
  category,         -- should be in products (depends on product)
  brand,            -- should be in products
  quantity,         -- belongs here (depends on order + product)
  unit_price,       -- belongs here (price at time of order)
  store_city,       -- should be in stores
  store_manager     -- should be in stores
)

orders(order_id, customer_name, customer_email, customer_city,
       store_city, store_manager, order_date)
-- One row per order ✓

order_items(order_id, product_name, category, brand, quantity, unit_price)
-- One row per product per order ✓
-- PK: (order_id, product_name) — composite

-- order_items partial dependencies:
-- quantity    → depends on (order_id, product_name) ✓ stays
-- unit_price  → depends on (order_id, product_name) ✓ stays (price at time of order)
-- product_name → it IS part of the PK ✓
-- category    → depends only on product_name ✗ move to products table
-- brand       → depends only on product_name ✗ move to products table

-- After 2NF:
order_items(order_id, product_id, quantity, unit_price)
-- product_name, category, brand moved to their own table:
products(product_id, product_name, category, brand)

-- In orders table:
-- order_id → customer_name (direct) — but customer info should be its own entity
-- order_id → customer_email (depends on customer, not on order)
-- order_id → customer_city (depends on customer)
-- order_id → store_city (depends on store, not on order)
-- order_id → store_manager (depends on store)

-- Transitive chains:
-- order_id → customer_id → customer_email (3NF violation)
-- order_id → store_id → store_manager (3NF violation)

-- After 3NF:
customers(customer_id, customer_name, email, city)
stores(store_id, city, manager_name, monthly_target)
orders(order_id, customer_id, store_id, order_date, total_amount)
products(product_id, product_name, category, brand, unit_price)
order_items(order_id, product_id, quantity, unit_price, line_total)

-- This is exactly FreshCart's schema!

// Part 07

Denormalisation — When to Break the Rules

-- orders.total_amount is denormalised
-- It could always be computed as: SELECT SUM(line_total) FROM order_items WHERE order_id = X
-- But storing it directly in orders saves that JOIN+SUM for every order lookup

-- This is a valid denormalisation because:
-- 1. total_amount is queried far more often than it is computed
-- 2. The application maintains consistency: it always updates total_amount when order_items change
-- 3. The performance benefit is significant (no JOIN for the most common query)

-- The trade-off: if an order_items line_total is corrected,
-- total_amount in orders must also be manually updated
-- Application code must maintain this invariant

-- order_items.unit_price is also denormalised:
-- The current product price is in products.unit_price
-- The price AT TIME OF ORDER is stored in order_items.unit_price
-- This is correct — it is a slowly changing dimension (not a 3NF violation)

🎯 Pro Tip

The rule of thumb for denormalisation: normalise first, then selectively denormalise based on measured performance data. Never denormalise speculatively — "this might be slow" is not a reason. Measure the query, confirm the JOIN is the bottleneck, then denormalise the specific data that eliminates the bottleneck. Document every denormalisation decision and how the application maintains data consistency.

// Part 08

Functional Dependencies — The Theory Behind Normalisation

-- These functional dependencies exist in FreshCart's data:

customer_id → first_name          -- one customer_id has one first_name
customer_id → email               -- one customer has one email
customer_id → city                -- one customer has one city
product_id  → product_name        -- one product has one name
product_id  → category            -- one product belongs to one category
product_id  → unit_price          -- one product has one current price
store_id    → city                -- one store is in one city
store_id    → manager_name        -- one store has one manager
order_id    → customer_id         -- one order belongs to one customer
order_id    → order_date          -- one order has one date
order_id    → total_amount        -- one order has one total

-- Composite key dependency:
(order_id, product_id) → quantity     -- one line item has one quantity
(order_id, product_id) → unit_price   -- one line item has one price at order time

-- Transitive dependencies (these would violate 3NF if in the wrong table):
customer_id → city    (would violate 3NF if city were stored in orders)
store_id → manager_name (would violate 3NF if stored in orders)

-- For any table, to check its normal form:

-- 1NF test: Is every column atomic?
--   "tags VARCHAR containing 'dairy,fresh,organic'" → FAIL 1NF

-- 2NF test (only for composite PKs): Does every non-key column
--   depend on the FULL primary key?
--   In order_items(order_id, product_id, quantity, product_name):
--   product_name depends only on product_id (partial) → FAIL 2NF

-- 3NF test: Does any non-key column determine another non-key column?
--   In customers(customer_id, email, city, state):
--   If city → state (a city is always in one state) → CHECK 3NF
--   Actually: in India, a city can span state borders (rare)
--   And a customer might be in a different state than their city suggests
--   So city does NOT reliably determine state → PASS 3NF (in this context)

-- Context matters: functional dependencies are facts about your DOMAIN
-- not about SQL syntax. You must understand the business rules.

// Part 09

Practical Normalisation — Step-by-Step Design

-- Suppliers table: attributes that depend on supplier_id
CREATE TABLE suppliers (
  supplier_id    INTEGER        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  supplier_name  VARCHAR(200)   NOT NULL,
  contact_person VARCHAR(200)   NOT NULL,
  phone          VARCHAR(20)    NOT NULL,
  email          VARCHAR(255)   NOT NULL UNIQUE,
  city           VARCHAR(100),
  state          VARCHAR(100),
  created_at     TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- Supply contracts: attributes that depend on (supplier_id, product_id)
CREATE TABLE supply_contracts (
  supplier_id    INTEGER        NOT NULL REFERENCES suppliers(supplier_id),
  product_id     INTEGER        NOT NULL REFERENCES products(product_id),
  contract_price DECIMAL(10,2)  NOT NULL CHECK (contract_price > 0),
  start_date     DATE           NOT NULL,
  end_date       DATE,
  is_preferred   BOOLEAN        NOT NULL DEFAULT false,
  PRIMARY KEY (supplier_id, product_id),
  -- A product can only have one preferred supplier
  CONSTRAINT uq_preferred_supplier
    UNIQUE (product_id, is_preferred)
    -- Note: in practice this constraint is complex to implement
    -- as UNIQUE allows multiple FALSE values but only one TRUE
);

-- This is 3NF because:
-- All supplier attributes depend directly on supplier_id
-- All contract attributes depend on (supplier_id, product_id)
-- No transitive dependencies
-- supplier city/state depend on supplier_id (the PK) directly

// Part 10

What This Looks Like at Work

CREATE TABLE appointments_flat (
  appointment_id   INTEGER PRIMARY KEY,
  patient_name     VARCHAR(200),
  patient_email    VARCHAR(255),
  patient_phone    VARCHAR(20),
  patient_city     VARCHAR(100),
  doctor_name      VARCHAR(200),
  doctor_phone     VARCHAR(20),
  specialisation   VARCHAR(200),
  consultation_fee DECIMAL(8,2),
  clinic_name      VARCHAR(200),
  clinic_address   VARCHAR(500),
  clinic_city      VARCHAR(100),
  appointment_date DATE,
  appointment_time TIME,
  status           VARCHAR(20),
  notes            TEXT
);

-- Entity: Patient (attributes depend on patient_id)
CREATE TABLE patients (
  patient_id   INTEGER      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  full_name    VARCHAR(200) NOT NULL,
  email        VARCHAR(255) NOT NULL UNIQUE,
  phone        VARCHAR(20)  NOT NULL,
  city         VARCHAR(100)
);

-- Entity: Doctor (attributes depend on doctor_id)
CREATE TABLE doctors (
  doctor_id        INTEGER        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  full_name        VARCHAR(200)   NOT NULL,
  phone            VARCHAR(20)    NOT NULL,
  email            VARCHAR(255)   NOT NULL UNIQUE,
  specialisation   VARCHAR(200)   NOT NULL,
  consultation_fee DECIMAL(8, 2)  NOT NULL CHECK (consultation_fee > 0)
);

-- Entity: Clinic (attributes depend on clinic_id)
-- (Removed from appointment — transitive dependency)
CREATE TABLE clinics (
  clinic_id    INTEGER      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  clinic_name  VARCHAR(200) NOT NULL,
  address      VARCHAR(500) NOT NULL,
  city         VARCHAR(100) NOT NULL
);

-- Association: which doctors work at which clinics
CREATE TABLE doctor_clinics (
  doctor_id  INTEGER REFERENCES doctors(doctor_id),
  clinic_id  INTEGER REFERENCES clinics(clinic_id),
  PRIMARY KEY (doctor_id, clinic_id)
);

-- Entity: Appointment (depends on appointment_id)
CREATE TABLE appointments (
  appointment_id   INTEGER      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  patient_id       INTEGER      NOT NULL REFERENCES patients(patient_id),
  doctor_id        INTEGER      NOT NULL REFERENCES doctors(doctor_id),
  clinic_id        INTEGER      NOT NULL REFERENCES clinics(clinic_id),
  appointment_date DATE         NOT NULL,
  appointment_time TIME         NOT NULL,
  -- consultation_fee stored here: price at booking time (may differ from doctor's current fee)
  consultation_fee DECIMAL(8,2) NOT NULL,
  status           VARCHAR(20)  NOT NULL DEFAULT 'Scheduled'
                   CHECK (status IN ('Scheduled','Completed','Cancelled','No-show')),
  notes            TEXT
);

🎯 Pro Tip

The question "why is this stored here if it's already in the other table?" is one of the most common and most useful questions in schema review. Sometimes the answer is "it should not be here — move it" (a normalisation fix). Sometimes the answer is "it needs to capture the value at a specific point in time, which may differ from the current value" (a valid denormalisation). Learning to distinguish between these two cases is a core database design skill.

// Part 11

Interview Prep — 5 Questions With Complete Answers

// Part 12

Design Mistakes — And How to Recognise Them

What comes next

In Module 27, you learn aggregate functions — COUNT, SUM, AVG, MIN, MAX — the tools that turn raw rows into business metrics. This is where SQL becomes the language of analytics.