Fixing a Flawed Database Table (Normalization and Indexes)

0Problem RestatementProblem

Goldman Sachs asked: here's a table with design flaws. Make it less redundant and faster to query. You're expected to know normalization (organizing tables to remove repeated data) and indexes.

Problems:

• Repeated customer data in every order: if Asha changes her email, many rows must change (an update anomaly), and one missed row means inconsistent data.
• Lists inside a cell ("Pen, Book"): can't query "all orders containing Book" efficiently, and prices are separated from products. This breaks first normal form.
• No keys or constraints: duplicates and bad data are possible.
• No indexes for common lookups, such as orders by customer or by date.

1Normal Forms in Plain Words

• 1NF: one value per cell, no lists. Each row is unique (it has a primary key).
• 2NF: every non-key column depends on the whole key (matters for composite keys).
• 3NF: non-key columns depend only on the key, not on other non-key columns (e.g., customer_city depends on the customer, not the order).

2The Fixed Design

CREATE TABLE customers (
  customer_id BIGINT PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  city        TEXT
);
CREATE TABLE products (
  product_id  BIGINT PRIMARY KEY,
  name        TEXT NOT NULL,
  price_cents INT NOT NULL CHECK (price_cents >= 0)
);
CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  order_date  DATE NOT NULL
);
CREATE TABLE order_items (
  order_id    BIGINT REFERENCES orders(order_id),
  product_id  BIGINT REFERENCES products(product_id),
  quantity    INT NOT NULL CHECK (quantity > 0),
  unit_price_cents INT NOT NULL,          -- price at the time of order (history must not change)
  PRIMARY KEY (order_id, product_id)
);

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
    CU["customers"] -->|"1 to many"| OR["orders"]
    OR -->|"1 to many"| OI["order_items"]
    PR["products"] -->|"1 to many"| OI

3Indexes for Faster Lookups

Add indexes for the queries the business actually runs:

• Orders for a customer, newest first → CREATE INDEX ON orders (customer_id, order_date DESC);
• Orders in a date range → CREATE INDEX ON orders (order_date);
• Which orders contain a product → the PK (order_id, product_id) doesn't help here, so add CREATE INDEX ON order_items (product_id);
• Lookup by email → already indexed by the UNIQUE constraint.

Check each important query with EXPLAIN to confirm it uses the index.

4Deep Dive — How far to normaliseDeep dive

The flawed table repeats customer names and product prices on every row. The instinct is to normalise everything; the instinct after a slow report is to denormalise everything. Both are wrong on their own.

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
  ROW["order_lines: customer_name, address, product_name, price"] --> UPD["Customer changes address"]
  UPD --> MANY["Update thousands of rows - some get missed"]
  MANY --> DIS["The same customer now has two addresses in the table"]
  ROW --> ASK["Which one is correct?"]
  ASK --> NONE["No source of truth - the table cannot answer"]

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
  SRC["Normalised tables - the source of truth"] --> SNAP["order_lines.unit_price_cents - the price at purchase"]
  SNAP --> HIST["Historical, not redundant - a different fact from today's price"]
  SRC --> TOT["orders.total_cents - maintained in the same transaction"]
  SRC --> MV["Materialised view for reports - refreshed on a schedule"]
  MV --> FAST["Report queries hit one table"]
  TOT --> RULE["Rule: every copy is derived, and derived in one place"]

5Migrating Safely (bonus)

1. Create the new tables.
2. Backfill from orders_flat (split the lists, deduplicate customers by email).
3. Dual-write new orders to both old and new tables, then verify counts and totals.
4. Switch reads to the new tables, and remove the old table later.

6Wrap-UpWrap-up

Split the flat table into customers, products, orders and order_items (one value per cell, each fact stored once, linked by primary and foreign keys with constraints), keep the historical unit price on order_items, and add indexes matching the real queries (customer + date, date, product). Denormalize only deliberately for reporting, and migrate with backfill, dual writes and verification.