E-commerce Schema

Interactive diagram

Drag to pan, scroll to zoom. Open it in the full editor to edit, rearrange and export.

About this schema

This models a typical online store: customers place orders made up of line items referencing products, ship to saved addresses, pay via payments, and leave product reviews.

Schema (SQL)

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120),
  created_at TIMESTAMP
);
CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  line1 VARCHAR(255), city VARCHAR(80), country CHAR(2)
);
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  parent_id INT REFERENCES categories(id)
);
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INT REFERENCES categories(id),
  sku VARCHAR(64) UNIQUE, name VARCHAR(200),
  price_cents INT, active BOOLEAN
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  address_id INT REFERENCES addresses(id),
  status VARCHAR(32), total_cents INT, placed_at TIMESTAMP
);
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id),
  product_id INT NOT NULL REFERENCES products(id),
  quantity INT, unit_cents INT
);
CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id),
  amount_cents INT, method VARCHAR(32), paid_at TIMESTAMP
);
CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  product_id INT NOT NULL REFERENCES products(id),
  customer_id INT NOT NULL REFERENCES customers(id),
  rating SMALLINT, body TEXT
);

Open in the editor → to export it as PNG, SVG, Mermaid or DBML.

More examples