Fixing a Flawed Database Table (Normalization and Indexes)

0) Problem Restatement

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.

1) Normal 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).

2) The 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)
);

flowchart LR
    CU["customers"] -->|"1 to many"| OR["orders"]
    OR -->|"1 to many"| OI["order_items"]
    PR["products"] -->|"1 to many"| OI

3) Indexes 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.

4) When to Denormalize

Normalization removes redundancy, but joins cost time. For read-heavy reports, it's fine to add controlled redundancy: e.g., orders.total_cents (kept in sync in the same transaction), or a reporting table/materialized view refreshed periodically. The rule is: one source of truth, and derived copies built from it.

5) Migrating 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.

6) Wrap-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.