Design this schema: an online store

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

"Design the database for an online store." No data, no right answer, 30 minutes. The interviewer is not waiting for a perfect diagram. They want to see how you get from a vague brief to tables you can defend.

You'll learn to

• Run a design round in order: requirements, ER, tables, normalise, indexes, queries
• Turn an online store into tables with the right keys and constraints
• Explain why some copies of data (the price paid) are correct, not redundant
• Pick indexes from the queries the app runs most

Learn

How a design round runs

Talk through six steps, in this order, and say each one out loud:

1. Requirements: ask what the app must do and which questions it must answer fast.
2. ER: name the entities (nouns) and relationships (verbs), with cardinality.
3. Tables: turn the ER into CREATE TABLEs: keys, types, NOT NULL, CHECK.
4. Normalise: one fact in one place. Then justify any copy you keep on purpose.
5. Indexes: one for each hot query, beyond the primary keys.
6. Queries: write two or three of the key queries against your own schema to prove it works.

Start small and grow it. A clear 7-table design beats a 20-table one you cannot explain.

Step 1: the requirements

Ask, then write down what you agreed:

• Customers sign up with a unique email.
• Products sit in categories, and a category can have a parent (Electronics, then Phones).
• An order has many products, each with a quantity. Prices change over time, but an order must keep the price paid.
• A payment can fail and be retried, so an order can have several payments.
• A customer can review a product once.
• Hot queries: a customer's order history, best sellers, revenue by category, orders not fully paid.

Step 2: the store as an ER diagram

An ER diagram of an online store: a customer places many orders, an order contains many products and a product is in many orders (with quantity and unit price), each order has many payments, each product belongs to one category, and customers review products (many to many, with a rating).

Two M:N relationships, two junction tables: order_items (with qty and unit_price) and reviews (with rating). The parent category is a 1:N from Category to itself, so it is just a parent_id column; the diagram leaves it out to stay readable.

Step 3: the tables

CREATE TABLE customers (
  customer_id  INT PRIMARY KEY,
  email        VARCHAR(255) NOT NULL UNIQUE,
  name         VARCHAR(100) NOT NULL,
  country      VARCHAR(40) NOT NULL,
  created_at   TIMESTAMP NOT NULL
);
CREATE TABLE categories (
  category_id  INT PRIMARY KEY,
  name         VARCHAR(50) NOT NULL UNIQUE,
  parent_id    INT REFERENCES categories(category_id)
);
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  category_id  INT NOT NULL REFERENCES categories(category_id),
  name         VARCHAR(100) NOT NULL,
  price        NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock        INT NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT NOT NULL REFERENCES customers(customer_id),
  status       VARCHAR(10) NOT NULL CHECK (status IN ('placed', 'paid', 'shipped', 'delivered', 'cancelled')),
  placed_at    TIMESTAMP NOT NULL,
  ship_city    VARCHAR(40) NOT NULL
);
CREATE TABLE order_items (
  order_id     INT REFERENCES orders(order_id),
  product_id   INT REFERENCES products(product_id),
  qty          INT NOT NULL CHECK (qty > 0),
  unit_price   NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
  payment_id   INT PRIMARY KEY,
  order_id     INT NOT NULL REFERENCES orders(order_id),
  amount       NUMERIC(10,2) NOT NULL CHECK (amount > 0),
  status       VARCHAR(10) NOT NULL CHECK (status IN ('success', 'failed')),
  paid_at      TIMESTAMP NOT NULL
);
CREATE TABLE reviews (
  customer_id  INT REFERENCES customers(customer_id),
  product_id   INT REFERENCES products(product_id),
  rating       INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  PRIMARY KEY (customer_id, product_id)
);

SELECT table_name, COUNT(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;

Seven tables. Money is NUMERIC(10,2), never FLOAT. CHECKs keep bad values out (a negative price, a rating of 7, an unknown status). reviews has the primary key (customer_id, product_id), so a second review of the same product by the same person is rejected by the database itself.

Step 4: normalise, and the copies you keep

Check each table for a fact stored twice. The category name lives only in categories; products point to it by id, so renaming a category is one UPDATE.

Two copies look redundant but are correct:

• order_items.unit_price is the price at the moment of sale. products.price is today's price. They are different facts, so storing both is not a 3NF problem. Without it, raising a price would rewrite every old invoice.
• orders.ship_city is where this order went. A customer who moves must not change history.

The order total is derived (the sum of its lines), so leave it out, or keep it only with a clear rule for updating it.

Design decisions you should be able to defend

Step 5: indexes for the hot queries

-- a customer's order history, newest first
CREATE INDEX idx_orders_customer ON orders (customer_id, placed_at DESC);
-- best sellers and "who bought this product"
CREATE INDEX idx_order_items_product ON order_items (product_id);
-- browsing a category
CREATE INDEX idx_products_category ON products (category_id);
-- payments of one order
CREATE INDEX idx_payments_order ON payments (order_id);

SELECT tablename, indexname
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Primary keys and UNIQUE columns already have an index (the _pkey and _key rows). order_items (order_id, …) is covered by its primary key, because order_id is its first column. Each extra index speeds up reads but slows every INSERT a little, so add one per real query, not one per column.

MySQL (InnoDB) creates an index on every foreign key column for you. Postgres does not: orders.customer_id has no index until you create one. In a Postgres design round, say "and I index the foreign keys I join or filter on".

Step 6: prove it with a key query

-- Alex's order history with totals
SELECT o.order_id,
       o.placed_at::DATE AS placed_on,
       o.status,
       SUM(oi.qty * oi.unit_price) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.customer_id = 1
GROUP BY o.order_id, o.placed_at, o.status
ORDER BY o.placed_at DESC;

Order 101 bought the Phone X at $649. Its price is $699 today, but the total stays $687 because it is built from unit_price. The practice tasks run on this same schema and data.

The classic mistake is one wide orders table with product1, product2, qty1, qty2 columns, or a comma-separated list of product ids. It breaks 1NF, caps the number of lines, and makes "best sellers" impossible to write. Many-to-many always means a junction table.

Interviewers look for: questions before tables, a junction table for every M:N, money as NUMERIC, constraints in the database (UNIQUE email, CHECK on status), the price-at-purchase decision, and indexes tied to named queries. Finish by writing one query on your design.

In one line

Design round: requirements, ER, tables, normalise, indexes, queries. M:N needs a junction table; keep the price paid on the order line; index the foreign keys you query by.

Interview corner

Design the tables for an online store.

customers (unique email), categories (with parent_id), products (category_id, price NUMERIC), orders (customer_id, status, placed_at), order_items (PK order_id + product_id, qty, unit_price), payments (order_id, amount, status), reviews (PK customer_id + product_id, rating). Then name the hot queries and index orders(customer_id, placed_at) and order_items(product_id).

Is storing unit_price in order_items denormalisation?

No. It is a different fact: the price agreed at the time of sale. products.price is the current price. If you derived old orders from the current price, a price change would rewrite history.

Should you store the order total on the orders table?

It is derived, so the normalised answer is no: compute it from the lines. Many real systems do store it for speed and for the invoice record, but then it must be written in the same transaction as the lines and never edited on its own. Say which you choose and why.