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.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

Hover a table to light up its relations, and click to pin them.

What each table does

  • customersOne row per account; email is the login identity.
  • categoriesA self-referencing tree: parent_id points back at the same table.
  • productsThe catalog; stock here is a running total, not history.
  • ordersA header row: who ordered and where the order is in its lifecycle.
  • order_itemsThe many-to-many junction between orders and products, with payload columns (qty, unit_price).
  • paymentsSeparate 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. You are free to copy it.

E-commerce Store ER diagram: 6 tables and 6 relations, drawn by ErdDocs from the SQL below.E-commerce Store ER diagram: 6 tables and 6 relations, drawn by ErdDocs from the SQL below.
The diagram ErdDocs generates from this SQL.
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, and 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?

In 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