ErdDocs

Example schemas / E-commerce Store

E-commerce Store ER diagram

An e-commerce database centers on four entities: customers place orders, orders contain products through an order_items junction, and payments settle orders. Categories nest through a self-reference, and prices are frozen into order lines at purchase time.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • customers — One row per account; email is the login identity.
  • categories — Self-referencing tree — parent_id points back at the same table.
  • products — The catalog; stock here is a running total, not history.
  • orders — A header row: who ordered and where the order is in its lifecycle.
  • order_items — The many-to-many junction between orders and products, with payload columns (qty, unit_price).
  • payments — Separate from orders: an order can be paid in parts or refunded.

Design decisions worth copying

  • order_items freezes unit_price. If lines referenced the live product price, editing the catalog would silently rewrite every past invoice.
  • The composite primary key (order_id, product_id) makes "the same product twice in one order" impossible by construction instead of by application code.
  • payments is its own table rather than columns on orders — partial payments and refunds then become rows, not schema changes.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(120),
    created_at  timestamptz  NOT NULL DEFAULT now()
);

CREATE TABLE categories (
    id         bigserial   PRIMARY KEY,
    parent_id  bigint      REFERENCES categories(id),
    name       varchar(80) NOT NULL
);
COMMENT ON COLUMN categories.parent_id IS 'NULL for top-level categories';

CREATE TABLE products (
    id           bigserial     PRIMARY KEY,
    category_id  bigint        NOT NULL REFERENCES categories(id),
    sku          varchar(40)   NOT NULL UNIQUE,
    title        varchar(200)  NOT NULL,
    price        numeric(10,2) NOT NULL,
    stock        int           NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id           bigserial     PRIMARY KEY,
    customer_id  bigint        NOT NULL REFERENCES customers(id),
    status       varchar(20)   NOT NULL DEFAULT 'pending',
    placed_at    timestamptz   NOT NULL DEFAULT now()
);
COMMENT ON COLUMN orders.status IS 'pending -> paid -> shipped -> done, or cancelled';

CREATE TABLE order_items (
    order_id    bigint        NOT NULL REFERENCES orders(id),
    product_id  bigint        NOT NULL REFERENCES products(id),
    qty         int           NOT NULL,
    unit_price  numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
COMMENT ON COLUMN order_items.unit_price IS 'Frozen at purchase; product price may change later';

CREATE TABLE payments (
    id         bigserial     PRIMARY KEY,
    order_id   bigint        NOT NULL REFERENCES orders(id),
    amount     numeric(10,2) NOT NULL,
    method     varchar(20)   NOT NULL,
    paid_at    timestamptz   NOT NULL DEFAULT now()
);

FAQ

Why is there no cart table?

A cart is an order in the "pending" state — most real systems reuse the orders table for it. A separate cart table doubles the schema for no new information.

Where would product variants (size, color) go?

A product_variants table between products and order_items: products keeps the shared description, each variant carries its own SKU, price and stock, and order lines reference the variant.

More example schemas