How to Design a Database Schema for an E-Commerce Marketplace

Introduction

An e-commerce marketplace connects buyers and sellers on a single platform — think Amazon, Etsy, or eBay. On the surface, users browse products, add them to a cart, and check out. Under the hood, the data model has to handle multi-vendor storefronts, hierarchical product categories, variant-level inventory tracking (sizes, colours), transactional order records, payment states, and a review system that enforces one rating per buyer per product.

Get the schema wrong early — conflating variants with products, storing prices in the wrong place, or skipping transactional safeguards — and you'll face painful migrations once real money flows through it.

In this guide we'll build a complete, production-grade PostgreSQL schema for a multi-vendor marketplace from the ground up. We'll cover:

Why product variants deserve their own table (and their own SKU)
How to model hierarchical categories with a self-referencing parent_id
Where prices live — and why COALESCE(price_override, base_price) is the right pattern
How to enforce one review per buyer per product at the database level
Five real-world SQL queries including window functions and a cart-to-order conversion

Step 1: Identify the Entities

Before writing any DDL, map out what the system needs to track:

Who is using the platform? → users (buyers, sellers, admins)
Who sells things? → stores owned by seller users
What do they sell? → products grouped by categories
How do products vary? → product_variants (size, colour, etc.)
What's in a buyer's basket? → cart_items referencing variants
What did they buy? → orders containing order_items
How did they pay? → payments linked to orders
What do they think? → reviews on products
Where do things ship? → addresses stored per user

Table	Description
`users`	All platform participants — buyers, sellers, and admins in a single table with a role column
`stores`	Seller storefronts with a unique slug for public URLs
`categories`	Self-referencing hierarchy (Electronics → Laptops → Gaming Laptops)
`products`	Items listed by a store, linked to a category, with a base price
`product_variants`	Size/colour/style variations of a product — each with its own SKU and optional price override
`cart_items`	Items a user has added to their cart but not yet checked out
`orders`	Confirmed purchases with a status lifecycle and a shipping address
`order_items`	Individual line items within an order — variant, quantity, and price snapshot
`payments`	Payment method and status for each order (one payment per order)
`reviews`	Buyer ratings and feedback on products (one review per buyer per product)
`addresses`	Shipping and billing addresses stored per user

Step 2: Define the Relationships

A user with role = 'seller' owns one or more stores; the same user can also place orders as a buyer
A store lists many products, each belonging to one category
A category can have a parent category, forming a tree of arbitrary depth via parent_id
A product has many product_variants — each variant has a unique SKU and an optional price override
A user has many cart_items, each referencing a specific product_variant
An order links a buyer to a shipping address and contains many order_items
Each order_item references a product_variant and captures the price at purchase time
Each order has exactly one payment record
A buyer can leave one review per product (enforced by a unique constraint)
A user can store many addresses; one can be flagged as default

DBA Tip: Always snapshot unit_price and subtotal on order_items at checkout time. Never recalculate from the current product price — prices change, but what a customer paid must be immutable.

Step 3: Build the Schema

A few deliberate design choices throughout:

UUID primary keys — opaque, safe to expose in APIs, no sequential-ID enumeration risk
VARCHAR + CHECK instead of ENUM — adding a new order status or payment method never requires an ALTER TYPE migration
NUMERIC for all money columns — exact decimal arithmetic; never FLOAT or REAL for currency
TIMESTAMPTZ everywhere — stores timestamps with time zone, essential for a multi-region service
ON DELETE CASCADE on user-owned data — cart items, reviews, and addresses clean up automatically when an account is removed

-- Enable UUID generation (required once per database)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE users (
  id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  email         VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name     VARCHAR(100) NOT NULL,
  role          VARCHAR(20)  NOT NULL DEFAULT 'buyer'
    CHECK (role IN ('buyer', 'seller', 'admin')),
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ============================================================
-- STORES  (seller storefronts)
-- ============================================================
CREATE TABLE stores (
  id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        VARCHAR(150) NOT NULL,
  slug        VARCHAR(150) UNIQUE NOT NULL,
  description TEXT,
  logo_url    VARCHAR(500),
  is_active   BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ============================================================
-- CATEGORIES  (self-referencing hierarchy)
-- ============================================================
CREATE TABLE categories (
  id        UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  name      VARCHAR(100) NOT NULL,
  slug      VARCHAR(100) UNIQUE NOT NULL,
  parent_id UUID         REFERENCES categories(id) ON DELETE SET NULL
);

-- ============================================================
-- PRODUCTS
-- ============================================================
CREATE TABLE products (
  id          UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id    UUID           NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  category_id UUID           REFERENCES categories(id) ON DELETE SET NULL,
  name        VARCHAR(200)   NOT NULL,
  slug        VARCHAR(200)   NOT NULL,
  description TEXT,
  base_price  NUMERIC(12, 2) NOT NULL,
  is_active   BOOLEAN        NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  UNIQUE (store_id, slug)
);

-- ============================================================
-- PRODUCT VARIANTS  (size, colour, style, etc.)
-- ============================================================
CREATE TABLE product_variants (
  id             UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id     UUID           NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sku            VARCHAR(100)   UNIQUE NOT NULL,
  name           VARCHAR(150)   NOT NULL,   -- e.g. "Large / Red"
  price_override NUMERIC(12, 2),            -- NULL → use products.base_price
  stock_quantity INT            NOT NULL DEFAULT 0
    CHECK (stock_quantity >= 0),
  created_at     TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- ============================================================
-- ADDRESSES
-- ============================================================
CREATE TABLE addresses (
  id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  label       VARCHAR(50),                  -- e.g. "Home", "Office"
  line_1      VARCHAR(255) NOT NULL,
  line_2      VARCHAR(255),
  city        VARCHAR(100) NOT NULL,
  state       VARCHAR(100),
  postal_code VARCHAR(20)  NOT NULL,
  country     VARCHAR(100) NOT NULL,
  is_default  BOOLEAN      NOT NULL DEFAULT FALSE
);

-- ============================================================
-- CART ITEMS
-- ============================================================
CREATE TABLE cart_items (
  id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  variant_id UUID        NOT NULL REFERENCES product_variants(id) ON DELETE CASCADE,
  quantity   INT         NOT NULL DEFAULT 1
    CHECK (quantity > 0),
  added_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, variant_id)   -- one row per variant per user; update quantity instead
);

-- ============================================================
-- ORDERS
-- ============================================================
CREATE TABLE orders (
  id                  UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             UUID           NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  shipping_address_id UUID           NOT NULL REFERENCES addresses(id),
  status              VARCHAR(20)    NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
  total_amount        NUMERIC(12, 2) NOT NULL,
  placed_at           TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- ============================================================
-- ORDER ITEMS  (price snapshot at checkout)
-- ============================================================
CREATE TABLE order_items (
  id         UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id   UUID           NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  variant_id UUID           NOT NULL REFERENCES product_variants(id),
  quantity   INT            NOT NULL
    CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL,   -- price at time of purchase
  subtotal   NUMERIC(12, 2) NOT NULL    -- quantity × unit_price, stored for speed
);

-- ============================================================
-- PAYMENTS  (one per order)
-- ============================================================
CREATE TABLE payments (
  id       UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID           NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
  method   VARCHAR(30)    NOT NULL
    CHECK (method IN ('credit_card', 'paypal', 'bank_transfer', 'crypto')),
  status   VARCHAR(20)    NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
  amount   NUMERIC(12, 2) NOT NULL,
  paid_at  TIMESTAMPTZ
);

-- ============================================================
-- REVIEWS  (one per buyer per product)
-- ============================================================
CREATE TABLE reviews (
  id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  product_id UUID        NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  rating     SMALLINT    NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment    TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, product_id)   -- one review per buyer per product
);

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX idx_stores_owner          ON stores(owner_id);
CREATE INDEX idx_products_store        ON products(store_id);
CREATE INDEX idx_products_category     ON products(category_id);
CREATE INDEX idx_products_active       ON products(store_id, is_active);
CREATE INDEX idx_variants_product      ON product_variants(product_id);
CREATE INDEX idx_cart_items_user       ON cart_items(user_id);
CREATE INDEX idx_orders_user           ON orders(user_id);
CREATE INDEX idx_orders_status         ON orders(status);
CREATE INDEX idx_orders_placed         ON orders(placed_at DESC);
CREATE INDEX idx_order_items_order     ON order_items(order_id);
CREATE INDEX idx_payments_order        ON payments(order_id);
CREATE INDEX idx_reviews_product       ON reviews(product_id);
CREATE INDEX idx_addresses_user        ON addresses(user_id);
CREATE INDEX idx_categories_parent     ON categories(parent_id);

Step 4: ER Diagram

Scroll or pinch to zoom · drag to pan · double-click to zoom in

Entity-relationship diagram for the e-commerce marketplace platform

Entity-relationship diagram — e-commerce marketplace platform

Step 5: Example Queries

Query 1 — Active Products in a Store with Variant Pricing

Return every active product in a store with all its variants and their effective selling price. COALESCE picks the variant's price override if set, otherwise falls back to the product's base price.

SELECT
  p.name                                              AS product_name,
  p.base_price,
  pv.name                                             AS variant,
  pv.sku,
  COALESCE(pv.price_override, p.base_price)           AS final_price,
  pv.stock_quantity
FROM products p
JOIN product_variants pv ON pv.product_id = p.id
WHERE p.store_id  = 'your-store-uuid-here'
  AND p.is_active = TRUE
ORDER BY p.name, pv.name;

DBA Tip: Storing price_override as nullable (rather than duplicating base_price into every variant row) keeps price updates simple — change base_price on the product and all variants without an override reflect it immediately.

Query 2 — User Order History with Item Count

Summarise a buyer's order history. COUNT(oi.id) gives the number of distinct line items per order.

SELECT
  o.id                           AS order_id,
  o.status,
  o.total_amount,
  o.placed_at,
  COUNT(oi.id)                   AS item_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 'your-user-uuid-here'
GROUP BY o.id, o.status, o.total_amount, o.placed_at
ORDER BY o.placed_at DESC;

Query 3 — Top-Rated Products Across the Marketplace

Find the highest-rated products that have enough reviews to be statistically meaningful. HAVING COUNT(...) >= 5 filters out products with only one or two ratings that could skew the leaderboard.

SELECT
  p.id                       AS product_id,
  p.name                     AS product_name,
  s.name                     AS store_name,
  ROUND(AVG(r.rating), 2)    AS avg_rating,
  COUNT(r.id)                AS review_count
FROM products p
JOIN stores  s ON s.id = p.store_id
JOIN reviews r ON r.product_id = p.id
WHERE p.is_active = TRUE
GROUP BY p.id, p.name, s.name
HAVING COUNT(r.id) >= 5
ORDER BY avg_rating DESC, review_count DESC
LIMIT 20;

Query 4 — Low Stock Alerts Per Store

Identify variants that are running low or out of stock so sellers can restock before losing sales.

SELECT
  s.name                AS store_name,
  p.name                AS product_name,
  pv.sku,
  pv.name               AS variant_name,
  pv.stock_quantity,
  COALESCE(pv.price_override, p.base_price) AS selling_price
FROM product_variants pv
JOIN products p ON pv.product_id = p.id
JOIN stores   s ON p.store_id    = s.id
WHERE pv.stock_quantity <= 5
  AND p.is_active = TRUE
  AND s.is_active = TRUE
ORDER BY s.name, pv.stock_quantity ASC;

Query 5 — Monthly Revenue Per Store with Rank

Use RANK() to compare each store's monthly revenue and identify top performers. DATE_TRUNC('month', ...) clips the timestamp to the first of the current month.

SELECT
  s.name                                            AS store_name,
  COUNT(DISTINCT o.id)                              AS orders_completed,
  SUM(oi.subtotal)                                  AS gross_revenue,
  RANK() OVER (ORDER BY SUM(oi.subtotal) DESC)      AS revenue_rank
FROM order_items oi
JOIN orders   o ON oi.order_id   = o.id
JOIN product_variants pv ON oi.variant_id = pv.id
JOIN products p  ON pv.product_id = p.id
JOIN stores   s  ON p.store_id    = s.id
WHERE o.status    = 'delivered'
  AND o.placed_at >= DATE_TRUNC('month', NOW())
GROUP BY s.id, s.name
ORDER BY revenue_rank;

DBA Tip: Window functions like RANK() are evaluated after GROUP BY and HAVING, so you can rank aggregated values directly without a subquery. If you need only the top N stores, wrap this in a CTE and filter on revenue_rank <= 10.

Extensibility

This schema is a solid foundation. Here's how to extend it cleanly without breaking existing functionality:

Feature	Extension
Wishlists	Add a `wishlists` table (`user_id`, `product_id`, `added_at`) with a `UNIQUE (user_id, product_id)` constraint — identical pattern to `reviews`
Coupons & Discounts	Add a `coupons` table (code, discount type, value, expiry, usage limit) and an `order_coupons` junction; store the discount amount on `orders` at checkout
Seller Payouts	Add a `payouts` table tracking commission splits per store per settlement period; reference completed `order_items` for the calculation base
Product Images	Add a `product_images` table (`product_id`, `url`, `alt_text`, `sort_order`) — query `WHERE sort_order = 0` for the hero image
Shipment Tracking	Add a `shipments` table (`order_id`, `carrier`, `tracking_number`, `status`, `estimated_delivery`) with a `UNIQUE` constraint on `order_id` for single-shipment orders
Full-Text Search	Add a `tsvector` generated column on `products(name, description)` and a GIN index; enables `WHERE search_vector @@ to_tsquery(...)` without an external search engine
Product Tags / Facets	Add `tags` and `product_tags` tables for faceted filtering (brand, material, style); index `product_tags(tag_id)` for fast facet queries

DBA Tip: For the categories self-referencing hierarchy, if you need to query full ancestry paths (e.g. "Electronics > Laptops > Gaming Laptops"), consider using PostgreSQL's WITH RECURSIVE CTE or the ltree extension. ltree stores paths as dot-delimited labels and supports indexed ancestor/descendant lookups with a single operator.

Summary

We've built a complete multi-vendor e-commerce schema in PostgreSQL covering:

A single users table with a role column for buyers, sellers, and admins — no table-per-role complexity
A self-referencing categories tree that supports unlimited nesting without schema changes
A products + product_variants split that keeps SKU-level inventory, pricing, and stock in one place while sharing name, description, and base price across variants
A cart_items table with a unique constraint on (user_id, variant_id) — upsert quantity rather than inserting duplicates
Price snapshotting in order_items — unit_price and subtotal are captured at checkout and never recalculated from live product data
A one-payment-per-order rule enforced by a UNIQUE constraint on payments.order_id
A one-review-per-product-per-buyer rule enforced by UNIQUE (user_id, product_id) on reviews
Production-ready details — UUID keys, VARCHAR + CHECK constraints, NUMERIC for money, TIMESTAMPTZ, cascading deletes, and a targeted index strategy

Try running these queries on your own PostgreSQL instance or explore the schema interactively at SQLExplain.ai.

How to Design a Database Schema for an E-Commerce Marketplace in PostgreSQL