DiagramDB
DiagramDB / Online Shopping System ERD

ER Diagram for Online Shopping System

An e-commerce store: customers with saved addresses, products in categories, carts, orders with line items, payments and shipments. The order line item is where the price is frozen at the moment of purchase.

All examples
Drawing 8 tables…

Why each relationship is drawn this way

  • orders ↔ productsorder_items resolves the many-to-many and stores quantity and the unit price paid, so later price changes never rewrite old orders.
  • category → categoryparent_id points back at categories: a self-reference that builds a tree (Clothing → Shoes → Trainers).
  • customer → addresses → ordersCustomers keep several addresses; each order ships to one of them.
  • order → shipmentorder_id is UNIQUE on shipments, so this schema allows one shipment per order (drop UNIQUE for split deliveries).
  • order → paymentsSeveral payment attempts per order are normal: a declined card followed by a successful one.

The schema

CREATE TABLE customers (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL
);
CREATE TABLE addresses (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  line1 VARCHAR(200) NOT NULL,
  city VARCHAR(80) NOT NULL,
  postcode VARCHAR(20) NOT NULL,
  country CHAR(2) NOT NULL
);
CREATE TABLE categories (
  id INT PRIMARY KEY,
  parent_id INT REFERENCES categories(id),
  name VARCHAR(80) NOT NULL
);
CREATE TABLE products (
  id INT PRIMARY KEY,
  category_id INT NOT NULL REFERENCES categories(id),
  sku VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  price_cents INT NOT NULL,
  stock INT NOT NULL DEFAULT 0
);
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  shipping_address_id INT NOT NULL REFERENCES addresses(id),
  status VARCHAR(20) NOT NULL,
  placed_at TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price_cents INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE payments (
  id INT PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id),
  provider_ref VARCHAR(80) NOT NULL,
  amount_cents INT NOT NULL,
  status VARCHAR(20) NOT NULL
);
CREATE TABLE shipments (
  id INT PRIMARY KEY,
  order_id INT NOT NULL UNIQUE REFERENCES orders(id),
  carrier VARCHAR(40),
  tracking_number VARCHAR(60),
  shipped_at TIMESTAMP
);

Design notes

  • Keep a cart in its own carts/cart_items tables or in the browser; do not create pending orders for every cart.
  • Copy the address onto the order if customers can edit or delete saved addresses.

Tables at a glance

  • customers: id, email, full_name, created_at
  • addresses: id, customer_id, line1, city, postcode, country
  • categories: id, parent_id, name
  • products: id, category_id, sku, name, price_cents, stock
  • orders: id, customer_id, shipping_address_id, status, placed_at
  • order_items: order_id, product_id, quantity, unit_price_cents
  • payments: id, order_id, provider_ref, amount_cents, status
  • shipments: id, order_id, carrier, tracking_number, shipped_at

Questions

What are the entities in a online shopping system ER diagram?

This design uses 8 tables: customers, addresses, categories, products, orders, order_items, payments, shipments. order_items resolves the many-to-many and stores quantity and the unit price paid, so later price changes never rewrite old orders.

How many relationships does the online shopping system ER diagram have?

9 foreign-key relationships. The many-to-many ones are resolved with junction tables, which is how they must be stored in a relational database.

Can I edit this ER diagram and export it?

Yes. “Edit in the ER diagram generator” opens the same SQL in the editor, where you can add tables, drag boxes and export SVG, PNG, Mermaid, DBML or SQL for PostgreSQL, MySQL, SQL Server or SQLite.

Which notation is used?

Crow’s foot: a fork means many, a bar means exactly one and a circle means optional (zero). A nullable foreign key gets the circle on the parent end.

More ER diagram examples