Core tables
users, orders, order_items, products
Commerce example
Ecommerce Order ERD Example
A practical ecommerce order ERD example covering customers, orders, order items, products, payments, and shipments.
Scenario
Ecommerce Order ERD Example
A typical ecommerce flow where customers place orders, each order contains line items, and payment and shipping states must be tracked.
Key relation
orders 1:N order_items
Main caution
Separate ordered price from current product price
Requirements
Requirements
- A user can create many orders, and every order belongs to one user.
- An order contains multiple product lines with quantity and price at purchase time.
- Payments and shipments need their own statuses and history.
Table design
Table design
- id PK
- email UNIQUE
- name
- created_at
- id PK
- name
- sku UNIQUE
- current_price
- stock_quantity
- id PK
- user_id FK
- status
- ordered_at
- total_amount
- id PK
- order_id FK
- product_id FK
- quantity
- unit_price
- id PK
- order_id FK
- provider
- status
- approved_at
- id PK
- order_id FK
- carrier
- tracking_number
- shipped_at
Relationships
Relationships
users 1:N ordersorders 1:N order_itemsproducts 1:N order_itemsorders 1:N paymentsorders 1:1 shipments
Design notes
Design notes
Copy product price into the line item
Current product prices can change. Store the purchase-time price on order_items.unit_price for reliable revenue and refund calculations.
Split order and payment status
An order may exist while payment fails or retries. A payments table keeps payment attempts separate from fulfillment state.
Start with one shipment and expand later
A 1:1 shipment is enough for many early stores. If partial delivery appears, change the relationship to orders 1:N shipments.
Implementation checks
Implementation checks
- Add foreign keys on orders.user_id, order_items.order_id, and order_items.product_id.
- Use unique constraints for products.sku and users.email.
- Restrict status columns with enum or check constraints.
More ERD examples
More ERD examples
Use these structures as a starting point, or open a demo canvas before creating an account.