What is it? #
Goal: design a schema that handles the awkward parts of a real shop, not just the tidy parts.
The previous project had products with one price. Real shops have variants — the same shirt in three sizes and four colours, each with its own stock level and possibly its own price. They have customers with several addresses. They have payments that partially succeed, refunds, and stock that must not go negative when two people check out at once.
Each of those is a design decision with a wrong answer that seems reasonable at first.
This project is about the decisions more than the typing. For each table, be able to say why it exists separately rather than being columns on another table.
Think of it like this #
The difference between drawing a house and drawing a house that can actually be built.
The rough sketch has rooms and doors, and it is fine until someone asks where the plumbing runs, how the stairs fit, and what happens when two doors want the same space.
The awkward details are where the real design happens.
Simple example #
A shop selling clothing. A product is "Cotton T-Shirt". A variant is "Cotton T-Shirt, Medium, Blue" — and that is the thing with a barcode, a stock level and a price.
Confusing those two is the single most common mistake in e-commerce schema design.
Code #
---------- ARCHITECTURE ----------
customers ──┬──▶ addresses (one customer, many addresses)
│
└──▶ orders ──┬──▶ order_items ──▶ product_variants ──▶ products
│ │
├──▶ payments └──▶ inventory_moves
│
└──▶ order_status_history (audit trail)
KEY INSIGHT: products describe, VARIANTS are what you actually sell.
Stock, price and barcode live on the VARIANT, not the product.-- ---------- Products and variants ----------
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
description text,
active boolean NOT NULL DEFAULT true
);
CREATE TABLE product_variants (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id bigint NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
sku text NOT NULL UNIQUE, -- the real-world identifier
size text,
colour text,
price numeric(10,2) NOT NULL CHECK (price >= 0),
stock integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
-- ^^^^^^^^^^^^^^^^
-- This CHECK is the whole oversell defence. The database refuses
-- to go negative no matter what the application believes.
UNIQUE (product_id, size, colour) -- no duplicate combinations
);
CREATE INDEX product_variants_product_idx ON product_variants (product_id);-- ---------- Customers and addresses ----------
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE addresses (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
line1 text NOT NULL,
city text NOT NULL,
postcode text NOT NULL,
country char(2) NOT NULL,
is_default boolean NOT NULL DEFAULT false
);
-- "Only ONE default address per customer" — a partial unique index.
-- A plain UNIQUE would wrongly allow only one NON-default address too.
CREATE UNIQUE INDEX addresses_one_default
ON addresses (customer_id) WHERE is_default;
CREATE INDEX addresses_customer_idx ON addresses (customer_id);-- ---------- Orders: snapshot the address, do not just reference it ----------
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','delivered',
'cancelled','refunded')),
-- The address AS IT WAS when the order was placed.
-- A pointer alone would mean editing your address rewrites where
-- past orders were shipped to — which is wrong, and untraceable.
ship_line1 text NOT NULL,
ship_city text NOT NULL,
ship_postcode text NOT NULL,
ship_country char(2) NOT NULL,
placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
variant_id bigint NOT NULL REFERENCES product_variants(id) ON DELETE RESTRICT,
quantity integer NOT NULL CHECK (quantity > 0),
-- Snapshot again: name and price at the time of sale
product_name text NOT NULL,
unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
line_total numeric(12,2)
GENERATED ALWAYS AS (quantity * unit_price) STORED,
UNIQUE (order_id, variant_id)
);
CREATE INDEX order_items_order_idx ON order_items (order_id);
CREATE INDEX order_items_variant_idx ON order_items (variant_id);
CREATE INDEX orders_customer_idx ON orders (customer_id, placed_at DESC);-- ---------- Payments: an order may have several ----------
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
amount numeric(10,2) NOT NULL, -- NEGATIVE for a refund
method text NOT NULL CHECK (method IN ('card','upi','netbanking','cod')),
status text NOT NULL CHECK (status IN ('pending','succeeded','failed')),
provider_ref text UNIQUE, -- the gateway's id; UNIQUE stops
-- the same webhook being applied twice
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX payments_order_idx ON payments (order_id);
-- One-to-many because reality is: a failed attempt then a successful one,
-- a partial refund, a split payment. A single "paid" boolean on orders
-- cannot represent any of that.-- ---------- Inventory movements: never just overwrite a number ----------
CREATE TABLE inventory_moves (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
variant_id bigint NOT NULL REFERENCES product_variants(id) ON DELETE RESTRICT,
change integer NOT NULL CHECK (change <> 0), -- +restock, -sale
reason text NOT NULL CHECK (reason IN ('sale','restock','return',
'correction','damage')),
order_id bigint REFERENCES orders(id),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX inventory_moves_variant_idx ON inventory_moves (variant_id, created_at DESC);
-- product_variants.stock is the fast current value.
-- inventory_moves is the LEDGER explaining how it got there.
-- When they disagree, the ledger is the truth — and you can find out why.-- ---------- Status history: an audit trail ----------
CREATE TABLE order_status_history (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
from_status text,
to_status text NOT NULL,
changed_by text NOT NULL DEFAULT current_user,
changed_at timestamptz NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION log_order_status()
RETURNS trigger LANGUAGE plpgsql AS <div class="katex-display-wrapper my-6 p-4 sm:p-6 rounded-2xl bg-paper-50 dark:bg-ink-900 border border-paper-200 dark:border-ink-800 shadow-sm overflow-x-auto text-center text-ink-900 dark:text-paper-100"><span class="katex-display"><span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML" display="block"><semantics><mrow><mi>B</mi><mi>E</mi><mi>G</mi><mi>I</mi><mi>N</mi><mi>I</mi><mi>N</mi><mi>S</mi><mi>E</mi><mi>R</mi><mi>T</mi><mi>I</mi><mi>N</mi><mi>T</mi><mi>O</mi><mi>o</mi><mi>r</mi><mi>d</mi><mi>e</mi><msub><mi>r</mi><mi>s</mi></msub><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><msub><mi>s</mi><mi>h</mi></msub><mi>i</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi>y</mi><mo stretchy="false">(</mo><mi>o</mi><mi>r</mi><mi>d</mi><mi>e</mi><msub><mi>r</mi><mi>i</mi></msub><mi>d</mi><mo separator="true">,</mo><mi>f</mi><mi>r</mi><mi>o</mi><msub><mi>m</mi><mi>s</mi></msub><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><mi>s</mi><mo separator="true">,</mo><mi>t</mi><msub><mi>o</mi><mi>s</mi></msub><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><mi>s</mi><mo stretchy="false">)</mo><mi>V</mi><mi>A</mi><mi>L</mi><mi>U</mi><mi>E</mi><mi>S</mi><mo stretchy="false">(</mo><mi>N</mi><mi>E</mi><mi>W</mi><mi mathvariant="normal">.</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>O</mi><mi>L</mi><mi>D</mi><mi mathvariant="normal">.</mi><mi>s</mi><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><mi>s</mi><mo separator="true">,</mo><mi>N</mi><mi>E</mi><mi>W</mi><mi mathvariant="normal">.</mi><mi>s</mi><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><mi>s</mi><mo stretchy="false">)</mo><mo separator="true">;</mo><mi>R</mi><mi>E</mi><mi>T</mi><mi>U</mi><mi>R</mi><mi>N</mi><mi>N</mi><mi>U</mi><mi>L</mi><mi>L</mi><mo separator="true">;</mo><mi>E</mi><mi>N</mi><mi>D</mi><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">BEGIN
INSERT INTO order_status_history (order_id, from_status, to_status)
VALUES (NEW.id, OLD.status, NEW.status);
RETURN NULL;
END;</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal">G</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal">d</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">s</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord"><span class="mord mathnormal">s</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3361em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">h</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">i</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal" style="margin-right:0.0359em;">y</span><span class="mopen">(</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal">d</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">o</span><span class="mord"><span class="mord mathnormal">m</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">s</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">t</span><span class="mord"><span class="mord mathnormal">o</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">s</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mclose">)</span><span class="mord mathnormal" style="margin-right:0.2222em;">V</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.109em;">LU</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mopen">(</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.1389em;">W</span><span class="mord">.</span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord">.</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.1389em;">W</span><span class="mord">.</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mclose">)</span><span class="mpunct">;</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal">LL</span><span class="mpunct">;</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mpunct">;</span></span></span></span></span></div>;
CREATE TRIGGER orders_status_history
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status) -- only on real changes
EXECUTE FUNCTION log_order_status();-- ---------- Checkout, done safely ----------
BEGIN;
INSERT INTO orders (customer_id, ship_line1, ship_city, ship_postcode, ship_country)
SELECT c.id, a.line1, a.city, a.postcode, a.country
FROM customers c
JOIN addresses a ON a.customer_id = c.id AND a.is_default
WHERE c.id = 1
RETURNING id; -- say it returns 501
-- Decrement stock with a GUARD, so no read-then-decide race exists:
UPDATE product_variants
SET stock = stock - 2
WHERE id = 10 AND stock >= 2;
-- Check the affected row count. 0 means insufficient stock -> ROLLBACK.
-- The CHECK (stock >= 0) is the backstop if this guard is ever forgotten.
INSERT INTO order_items (order_id, variant_id, quantity, product_name, unit_price)
SELECT 501, v.id, 2, p.name, v.price
FROM product_variants v JOIN products p ON p.id = v.product_id
WHERE v.id = 10;
INSERT INTO inventory_moves (variant_id, change, reason, order_id)
VALUES (10, -2, 'sale', 501);
COMMIT;-- ---------- Useful reporting queries ----------
-- Order totals with payment status
SELECT o.id, o.status,
sum(i.line_total) AS order_total,
COALESCE(sum(p.amount) FILTER (WHERE p.status='succeeded'),0) AS paid
FROM orders o
JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
GROUP BY o.id
HAVING sum(i.line_total) >
COALESCE(sum(p.amount) FILTER (WHERE p.status='succeeded'),0);
-- ^ orders not fully paid
-- Does the stock column agree with the ledger?
SELECT v.sku, v.stock AS current,
COALESCE(sum(m.change),0) AS from_ledger,
v.stock - COALESCE(sum(m.change),0) AS discrepancy
FROM product_variants v
LEFT JOIN inventory_moves m ON m.variant_id = v.id
GROUP BY v.id, v.sku, v.stock
HAVING v.stock <> COALESCE(sum(m.change),0);
-- Any row returned is a bug worth investigating.
-- Low stock alert
SELECT p.name, v.sku, v.stock
FROM product_variants v JOIN products p ON p.id = v.product_id
WHERE v.stock < 5 AND p.active
ORDER BY v.stock;How it works #
Expected result: a schema handling variants, multiple addresses, partial payments, refunds, an inventory ledger and a status audit trail — with the business rules enforced by the database rather than assumed by the application.
The decisions that matter:
Products versus variants is the central one. Customers browse products; they buy variants. Stock, price and SKU belong to the variant because that is the physical thing on a shelf. Putting stock on products means you cannot tell whether the medium blue shirts have run out, and it is expensive to fix later.
Snapshotting appears twice, and for the same reason both times. The shipping address and the product name and price are copied into the order because they describe what happened, not what is currently true. A customer editing their address should not change where last year's parcel was recorded as going. This is the single most common source of "our historical data changed" bugs.
Payments as a one-to-many relationship reflects reality: attempts fail, refunds are negative amounts, some orders are paid in parts. A boolean on orders cannot express any of it. The UNIQUE constraint on provider_ref is small and important — payment gateways retry webhooks, and it is what stops the same payment being recorded twice.
The inventory ledger follows the accounting principle of never overwriting a balance without recording the movement. stock is the fast current value; inventory_moves explains it. When they disagree — and eventually they will — the ledger tells you when and why. The reconciliation query above is the one to run on a schedule.
Oversell protection has two layers. The guarded UPDATE ... WHERE stock >= 2 removes the read-then-decide race entirely, as the isolation lesson described. The CHECK (stock >= 0) is the backstop that catches any code path where someone forgot the guard. Defence in depth, in a single table.
The partial unique index on default addresses solves a rule that a plain UNIQUE cannot express: one default per customer, any number of non-defaults.
Real-world use #
Schema mistakes are the most expensive kind, because changing a table with millions of rows means a migration, a lock and a maintenance window — while application bugs are a deploy.
The variant mistake is worth singling out. Shops that start with stock on products discover the problem the first time they sell anything in sizes, and by then there is order history referencing the wrong level of the hierarchy.
Snapshotting feels redundant when you write it — the address is right there in another table. It stops feeling redundant the first time someone asks why an old invoice shows a different address than the one on the shipping label, and nobody can answer.
The inventory ledger pays for itself the first time stock is wrong. Without it, you know the number is 3 and should be 5, and that is all you will ever know. With it, you can find the movement that should not be there.
For the status audit trail, a trigger is the right tool specifically because it captures changes made by any writer — the application, an admin fixing something by hand, a migration. That guarantee is exactly what an audit trail needs and what application code cannot provide.
Common mistakes #
- Putting stock and price on products instead of variants, which breaks as soon as sizes exist.
- Referencing the current address instead of snapshotting it, so editing an address rewrites history.
- Modelling payment as a boolean on orders, leaving no way to represent refunds or partial payments.
- Overwriting a stock number with no movement ledger, making discrepancies impossible to investigate.
- Omitting the UNIQUE on the payment provider reference, letting a retried webhook double-charge.
Practice #
Extend the design and justify each choice. Add discount codes that can be percentage or fixed amount, limited by date and usage count — and decide where the applied discount is recorded on an order. Add product categories where a product can belong to several. Add a rule that an order cannot move to shipped unless a succeeded payment covers its total, and decide whether to enforce that with a trigger, a constraint or application code. Finally, write the query that finds any order whose order_status_history shows an impossible transition, such as delivered back to pending.