E-commerce Database Schema ER Diagram Example

A useful ER diagram is not just a picture of tables. It should explain how business events move through the database. This e-commerce example shows a compact schema for customers, product catalog, orders, payments, and inventory.

Core Entities

Entity	Purpose	Important relationships
users	Stores customer accounts and contact information.	One user can place many orders.
categories	Groups products and supports parent-child category trees.	One category can contain many products and many child categories.
products	Stores product names, pricing, and catalog metadata.	One product can appear in many order items and inventory movements.
orders	Represents a checkout event.	One order belongs to one user and has many order items and payments.
order_items	Stores line-item snapshots for quantity and price at purchase time.	Each row joins one order to one product.
payments	Tracks payment attempts and settlement status.	One order may have multiple payments if retries or split payments are supported.

Example SQL DDL

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE categories (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT NULL,
  name VARCHAR(120) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  category_id BIGINT NOT NULL,
  sku VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  list_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(30) NOT NULL,
  ordered_at TIMESTAMP NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE payments (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  provider VARCHAR(50) NOT NULL,
  status VARCHAR(30) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  paid_at TIMESTAMP NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

How To Read The ER Diagram

The most important relationship is orders to order_items. Keep order item prices as a snapshot instead of reading the current product price later. This preserves historical invoices even when catalog prices change.

The products to categories relationship is many-to-one. If a product can appear in multiple categories, replace the direct category_id with a product_categories join table. That design changes the ER diagram from a simple lookup to a many-to-many relationship.

The payments to orders relationship should usually be one-to-many. Even if your first version only allows a single payment, payment retries, partial captures, refunds, and asynchronous provider callbacks are easier to model when payments are separate rows.

Common Review Checks

Every child table should make its parent key explicit with a foreign key or a documented relationship.
Line-item tables should store historical values that must not change after purchase.
Status fields should be documented with allowed states or moved into lookup tables for large systems.
Self-referencing relationships, such as category trees, should be labeled clearly in the diagram.

Paste the DDL above into Free ER Diagram to generate Mermaid or PlantUML output and compare the relationship layout.