E-commerce Product Catalog

Design the schema for a catalog that supports products with variants (size, color), nested categories, multi-currency pricing, and per-warehouse inventory. Read-heavy: product detail pages and category listings dominate traffic.

Clarifying questions

Is the variant axis fixed (size, color) or arbitrary per product (size, color, material, voltage)?
Do we need historical pricing for audit, or just current price per currency?
Single warehouse or multi-warehouse with regional fulfillment?
Are categories a strict tree or a DAG (Directed Acyclic Graph; a product belongs to multiple)?
How tolerant are we of inventory race conditions — strict count or eventually consistent?

Core requirements

Model products with arbitrary variant axes without schema changes per product type.
Render a product detail page in one round trip (product, variants, prices, stock).
Browse by category with filters on price, availability, and attributes.
Track inventory per (variant, warehouse) with safe decrement semantics.
Support multiple currencies with explicit conversion timing.
Allow a single SKU to live in multiple categories.

Canonical schema (PostgreSQL)

CREATE TABLE categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id UUID REFERENCES categories(id),
  slug TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  path LTREE NOT NULL
);

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  brand TEXT,
  attributes JSONB NOT NULL DEFAULT '{}'::jsonb,
  status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE product_categories (
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);

CREATE TABLE variants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sku TEXT UNIQUE NOT NULL,
  options JSONB NOT NULL,
  weight_grams INTEGER,
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE prices (
  variant_id UUID NOT NULL REFERENCES variants(id) ON DELETE CASCADE,
  currency CHAR(3) NOT NULL,
  amount_minor BIGINT NOT NULL CHECK (amount_minor >= 0),
  effective_from TIMESTAMPTZ NOT NULL DEFAULT now(),
  effective_to TIMESTAMPTZ,
  PRIMARY KEY (variant_id, currency, effective_from)
);

CREATE TABLE inventory (
  variant_id UUID NOT NULL REFERENCES variants(id) ON DELETE CASCADE,
  warehouse_id UUID NOT NULL,
  on_hand INTEGER NOT NULL CHECK (on_hand >= 0),
  reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (variant_id, warehouse_id)
);

Key indexes

CREATE INDEX idx_categories_path ON categories USING GIST (path)

LTREE GIST index makes 'all descendants of category X' an O(log n) subtree query for nested navigation.

CREATE INDEX idx_products_attrs ON products USING GIN (attributes jsonb_path_ops)

Faceted filters ('material=cotton' AND 'fit=slim') hit JSONB without per-attribute columns.

CREATE INDEX idx_variants_product ON variants(product_id) WHERE active

Product detail page loads only active variants; partial index keeps it tight.

CREATE INDEX idx_prices_current ON prices(variant_id, currency) WHERE effective_to IS NULL

Current-price lookup is the hottest query; partial index ignores historical rows.

CREATE INDEX idx_inventory_available ON inventory(variant_id) WHERE on_hand > reserved

'In stock somewhere' check becomes a partial-index hit instead of a sum across warehouses.

Key decisions to defend

Variant options live in JSONB rather than a fixed (size, color) shape — t-shirts and lightbulbs share one schema without polymorphism.
Prices are temporal (effective_from / effective_to) instead of overwritten — audit, scheduled sales, and rollback all fall out for free.
Categories are a tree via LTREE rather than recursive CTE (Common Table Expression) only — subtree queries are 100x faster and worth the extension dependency.
Inventory is split into on_hand and reserved so cart holds do not require a separate ledger; a SELECT FOR UPDATE on the row gates the decrement.
Many-to-many product_categories table — a strict tree forces awkward duplication when products legitimately belong to multiple browse paths.

Likely follow-ups

· Add a global search ranked by popularity — where does the score live?
· How would you represent a bundle (3 products sold as one SKU)?
· Black Friday: one variant gets 10k concurrent decrement attempts — what changes?
· Add per-region pricing rules without writing every (variant, country) row.

Dive deeper

Catalog questions probe whether you reach for JSONB intelligently (variant options, attributes) versus reflexively (everything in a blob). They also test whether you model time correctly — prices and inventory are state that changes, and a senior candidate models the history, not just the current value. The inventory question quietly tests concurrency awareness.

All database design prompts