E-commerce ERD: separate orders, products and order items
Design a small shop where a customer orders several products. Start with customers, products, orders and order_items. This example assumes registered customers and one currency; payments, shipping, inventory and refunds are separate extensions. Understand each table’s responsibility and why an order keeps its original price, then edit the design yourself.
K marks a primary key; * marks a required column. On small screens, scroll the diagram horizontally or select it to view at full size.
A DBML example you can import
This DBML creates four tables and three foreign keys. A product appears at most once per order, with quantity recording multiple units. Automatic ID generation and CHECK constraints for amounts and quantities are not included.
Table customers {
id bigint [pk]
email varchar(255) [not null, unique]
}
Table products {
id bigint [pk]
name varchar(120) [not null]
price decimal(12,2) [not null]
}
Table orders {
id bigint [pk]
customer_id bigint [not null]
ordered_at timestamp [not null]
}
Table order_items {
id bigint [pk]
order_id bigint [not null]
product_id bigint [not null]
quantity integer [not null]
unit_price decimal(12,2) [not null]
indexes {
(order_id, product_id) [unique]
}
}
Ref: orders.customer_id > customers.id
Ref: order_items.order_id > orders.id
Ref: order_items.product_id > products.idThe button opens a new document made from this code. Edit without signing up; your existing work is kept. To practice importing it yourself, copy the code and paste it into Import in the editor.
Start with this example →Give each table a clear responsibility
customers describes the customer, products the current catalog, and orders who purchased and when. order_items records which products were purchased, how many, and at what price. Multiple products fit without adding product_1, product_2 and more columns to each order.
Keep historical orders stable when prices change
products.price is the current price; order_items.unit_price captures the price when the order is placed. Two units ordered at 10.00 still total 20.00 if the catalog price later becomes 12.00. A foreign key does not copy the value: the order-processing logic must record it. Define separate calculation rules when adding discounts, tax and shipping.
Can one product occupy several order lines?
UNIQUE(order_id, product_id) prevents repeated product lines in this example. Buying two units means quantity is 2. If variants, separate discounts or split fulfillment require multiple lines for a product, revise the unique constraint and choose a line identity such as variant_id or an order-scoped line_number. The id primary key alone cannot prevent duplicate order/product pairs.
Extend the minimal model for a real service
Enforce positive quantities and non-negative prices with database CHECK constraints and input validation; those constraints are not included here. Multiple currencies also need a currency code and a precision policy. Choose deletion rules that preserve order history. Account deletion, delivery-address snapshots and payment records need their own requirements and design.
Try it: preserve the purchased product name
Open the example and add product_name varchar(120) to order_items. Like the unit price, a captured product name can explain an old order after the catalog changes. Order-processing logic must copy this value too. Export a PNG of the design and keep a JSON backup.
Open the example and practice →What you can use for free today
Free currently supports DBML and Schema Studio JSON import and export, plus table and relationship editing. Sign in to save to the cloud and back up important work as JSON. Supported PostgreSQL/MySQL DDL imports, the PostgreSQL metadata query and PNG export are also free. SQL/SVG exports are coming soon. MySQL input is converted to the PostgreSQL document model; review warnings against the original.
See plans and storage limits →