Database ER Diagram: Users, Orders and Products
An entity relationship (ER) diagram shows the tables of a database and how they refer to each other. Each box is a table, each row in a box is a column, and each line is a relationship. It is the quickest way to agree on a schema before writing the first migration, and to explain an existing one to someone new.
This template models a small online shop. A user places many orders, an order contains many order items, and each order item refers to one product. The result is that orders and products have a many-to-many relationship, which a relational database stores through a junction table: here, the order items.
Scroll sideways to see the whole diagram
Start from this diagram and edit it on your own board.
By continuing, you agree to the Terms of Service and Privacy Policy, including sending images of your strokes, diagram labels and similar data to providers in the United States (Cloudflare, Inc. and TypeSafe AI, Inc.) for AI conversion.
What each part does
- Users
- One row for each person with an account. The primary key (PK) id identifies a row. The email is usually kept unique, so that it can be used to sign in.
- Orders
- One row for each order. user_id is a foreign key (FK): a column that holds the id of a row in Users and tells you who placed the order. Because many orders can hold the same user_id, a user has many orders.
- Order items
- One row for each product on an order. It holds two foreign keys, order_id and product_id, so it is the junction table between Orders and Products. It also keeps quantity and unit_price, which belong to this pairing and to neither side alone.
- Products
- One row for each product that can be sold. A product can appear on many orders, through many rows in Order items. The sku is a code that identifies the product in the warehouse.
How to read the diagram
- Each box is a table. The row marked PK is its primary key, the column whose value identifies one row.
- A row marked FK is a foreign key: it stores the primary key of a row in another table, and the database can enforce that such a row exists.
- Each line is a relationship, labelled with its cardinality, read from left to right. 1 : N means one row on the left relates to many on the right.
- Users to Orders is 1 : N: a user places many orders, and an order belongs to exactly one user.
- Orders to Order items is 1 : N: an order contains many items, and an item belongs to one order.
- Order items to Products is N : 1: many items can refer to the same product. Put together, Orders and Products are many-to-many, and Order items is the table in the middle.
When to use it
- Designing the schema of a new feature and agreeing on it with the team before writing a migration.
- Documenting an existing database for a new colleague, with the keys and the one-to-many links in one picture.
- Teaching how a many-to-many relationship turns into two one-to-many relationships and a junction table.
Common variations
Add a one-to-one table
Add a Profiles box whose primary key is also a foreign key to Users, and label the line 1 : 1. It is a way to keep rarely used columns out of the main table.
Add a second many-to-many relationship
To tag products with categories, add a Categories table and a junction table with product_id and category_id between it and Products. Use a composite primary key of the two columns, so that the same pair cannot be stored twice.
Mark which columns are required or unique
Write NOT NULL or UNIQUE after a column name, for example email UNIQUE. A foreign key that may be empty, such as an optional coupon_id on Orders, makes the parent side optional: each order has 0 or 1 coupon. Say it on the line from Orders to Coupons as N : 0..1.
Switch to crow's foot notation
Many ER diagrams draw the many side of a line as a three-pronged foot. Sketio has no such line ends, so this template writes the cardinality on each line as text, which is just as exact. If your team uses crow's foot, keep the labels and add the feet by hand.
Make it yours
Replace the columns with the ones your tables really have, keeping a PK on every table and an FK on every column that refers to another table.
Opens this diagram as a board you can edit.
By continuing, you agree to the Terms of Service and Privacy Policy, including sending images of your strokes, diagram labels and similar data to providers in the United States (Cloudflare, Inc. and TypeSafe AI, Inc.) for AI conversion.