Crowsfoot

E-commerce database schema: ER diagram and SQL

This example models the core of an online shop: who buys, what is sold, and what each order contains. It is a good starting point for a store, a marketplace, or any order-based system.

Open this schema in the editor Jump to the SQL
customers PK id bigint email varchar name varchar created_at timestamp categories PK id bigint name varchar products PK id bigint FK category_id bigint name varchar price numeric stock int orders PK id bigint FK customer_id bigint status varchar total numeric placed_at timestamp order_items PK order_id bigint PK product_id bigint quantity int unit_price numeric

Tables in this schema

customers

People who place orders. Email is the natural lookup key, so it usually gets a unique index.

categories

Groups products so shoppers can browse them.

products

Items for sale. Each belongs to one category and has a price and a stock level.

orders

One purchase by one customer, with a status and a total.

order_items

The line items inside an order. It links orders and products, so one order can hold many products and a product can appear in many orders.

How the tables relate

  • products.category_id references categories.id: one row in categories can have many rows in products.
  • orders.customer_id references customers.id: one row in customers can have many rows in orders.
  • order_items is a junction table between orders and products: many-to-many.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE customers (
  id bigint PRIMARY KEY,
  email varchar(255) NOT NULL,
  name varchar(120) NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE TABLE categories (
  id bigint PRIMARY KEY,
  name varchar(80) NOT NULL
);

CREATE TABLE products (
  id bigint PRIMARY KEY,
  category_id bigint NOT NULL REFERENCES categories (id),
  name varchar(160) NOT NULL,
  price numeric(10,2) NOT NULL,
  stock int NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  status varchar(20) NOT NULL DEFAULT 'pending',
  total numeric(10,2) NOT NULL,
  placed_at timestamptz NOT NULL
);

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders (id),
  product_id bigint NOT NULL REFERENCES products (id),
  quantity int NOT NULL,
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Design notes

  • Order items store their own unit_price, so a later price change on a product doesn’t rewrite past orders.
  • The composite primary key on (order_id, product_id) stops the same product appearing twice in one order. Use quantity instead.
  • Add indexes on orders.customer_id and products.category_id, since you’ll filter and join on them constantly.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.