How to Draw an ER Diagram Step by Step
An entity-relationship (ER) diagram is the standard way to plan a database before writing any code. Getting the diagram right first saves you from painful schema migrations later. This guide walks through the process from a blank page to a complete ER diagram, using a practical example throughout.
What Goes into an ER Diagram
An ER diagram has three building blocks:
- Entities — the things you store data about, represented as rectangles. In a database, each entity becomes a table.
- Attributes — the properties of each entity, shown as columns inside the rectangle. Each attribute has a name and a data type.
- Relationships — lines connecting entities that share a foreign key reference. The symbols at each end of the line show the cardinality.
Cardinality Notation
Cardinality describes how many rows in one table can relate to rows in another. The most common notation is crow's foot (also called IE notation), which uses symbols at the end of each relationship line:
- One — a single vertical line
| - Many — a crow's foot (three lines fanning out)
- Zero or one — a circle and a vertical line
- One or more — a vertical line and a crow's foot
- Zero or more — a circle and a crow's foot
For example, a one-to-many relationship between users and orders (one user, many orders) would show a single line on the users side and a crow's foot on the orders side.
Step-by-Step: Drawing an ER Diagram
We'll use a simple blog platform as our example: users write posts, and posts have comments.
Step 1 — List your entities
Write down the things your system needs to store. For a blog: User, Post, Comment. Each becomes a rectangle in your diagram.
Step 2 — Add attributes to each entity
For each entity, list the columns you need. Start with the primary key, then add the meaningful attributes:
- User:
id,email,name,created_at - Post:
id,title,body,published_at,user_id - Comment:
id,body,created_at,post_id,user_id
Step 3 — Identify the relationships
Ask: which entities reference each other?
- A User writes many Posts → one-to-many (FK:
posts.user_id) - A Post has many Comments → one-to-many (FK:
comments.post_id) - A User writes many Comments → one-to-many (FK:
comments.user_id)
Step 4 — Draw the relationship lines
Connect the entities with lines. The foreign key column always lives on the "many" side of the relationship. Add crow's foot notation at the "many" end and a single line at the "one" end.
Step 5 — Mark primary keys and constraints
Mark each primary key (usually with PK). Mark columns that can't be null (NN), columns with unique constraints (UQ), and auto-increment columns (AI). This gives anyone reading the diagram a complete picture of the schema.
Step 6 — Review for normalization
Look for columns that store the same data in multiple places (redundancy), columns that depend on non-key columns (3NF violation), or any comma-separated values (1NF violation). Restructure before you build.
Step 7 — Generate the SQL
Once the diagram looks correct, generate your CREATE TABLE DDL from it. A diagram tool that exports SQL eliminates transcription errors between design and implementation.
Common ER Diagram Mistakes
- Missing many-to-many join tables. A student can enrol in many courses, and a course can have many students. This needs a
enrolmentsjoin table — you can't model it with a single foreign key. - Putting the foreign key on the wrong side. The FK always goes on the "many" side. An order has one user, so
orders.user_idreferencesusers.id— not the other way around. - Forgetting optional relationships. If a post can exist without an author (e.g., guest posts),
user_idshould allowNULL. Mark this in your diagram. - Treating the diagram as final. ER diagrams should evolve with your understanding. It's much cheaper to redraw a line than to run a production migration.
Draw your ER diagram in the browser — for free
SQL Designer gives you a drag-and-drop canvas to draw entities, add attributes, and connect relationships. Export MySQL DDL when you're done. No installation required.
Create a Free Account