ER diagram for e-commerce website
This ER diagram models a simple online store with six tables: customers, addresses, categories, products, orders and order_items. It is for students learning database design and for developers who need a clear starting schema for a shopping site.
Example: ER diagram for e-commerce website: click to open it in the editor and change it.
Entities and key attributes
| Entity | Primary key | Foreign keys | Other attributes |
|---|---|---|---|
| customers | id | none | name, email, phone |
| addresses | id | customer_id | line1, city, pincode |
| categories | id | none | name |
| products | id | category_id | name, price, stock_count |
| orders | id | customer_id, address_id | total, status, created_at |
| order_items | id | order_id, product_id | quantity, price |
Relationships and cardinality
- customers 1 to N addresses: one customer can save many addresses.
- categories 1 to N products: each product belongs to one category.
- customers 1 to N orders: a customer can place many orders.
- addresses 1 to N orders: each order ships to one saved address.
- orders 1 to N order_items: an order holds one or more line items.
- products 1 to N order_items: a product can appear in many orders.
order_items is the junction table that resolves the many-to-many link between orders and products. It stores its own price so past orders keep the price paid, even if the product price changes later.
How to adapt it for your project
- Add a payments table linked to orders if you need to track transactions.
- Add a reviews table between customers and products for ratings.
- Add state and country columns to addresses for international shipping.
- Add a parent_id to categories if you need subcategories.
- Add a cart table if baskets must persist before checkout.
Tip: Keep orders.total consistent by calculating it from the sum of order_items.quantity × order_items.price when the order is created.
Frequently asked questions
What are the entities in an ER diagram for an e-commerce website?
This diagram has six entities: customers, addresses, categories, products, orders and order_items. Together they cover users, shipping, the product catalog and purchases.
Why is there an order_items table?
An order can contain many products, and a product can appear in many orders. The order_items table breaks this many-to-many relationship into two one-to-many relationships and stores quantity and price per line.
Why does the orders table link to addresses?
The address_id foreign key records which saved address the order ships to. A customer with several addresses can choose a different one for each order.
Make your own ER diagram with AI →