How to Draw an ER Diagram
Drawing an ER diagram is a structured process for turning requirements into a visual database design. You identify the entities, list each entity's attributes and mark its primary key, identify the relationships between entities, determine each relationship's cardinality, and then draw the diagram using standard notation such as rectangles for entities.
Drawing an ER diagram is a structured process, not a free-for-all sketch. Following a consistent sequence of steps makes it far less likely to miss an entity, an important attribute, or get a relationship's cardinality wrong.
Step-by-step process
-
Identify the entities. Read through the requirements and pull out every distinct real-world "thing" that needs to be tracked, usually the nouns in a requirements description. For a food delivery app:
Customer,Restaurant,MenuItem,Order. -
List the attributes for each entity. For each entity, list its relevant properties.
Customermight havecustomer_id,name,phone,address. Mark which attribute (or attributes) uniquely identifies each row, this becomes the primary key. -
Identify relationships between entities. Look for how entities connect: a
Customerplaces anOrder; anOrdercontainsMenuItems; aRestaurantoffersMenuItems. -
Determine the cardinality of each relationship. For every relationship, ask: can one of A relate to many of B? Can one of B relate to many of A? A
Customercan place manyOrders, but eachOrderbelongs to oneCustomer, so that's one-to-many. AnOrdercan contain manyMenuItems, and aMenuItemcan appear in many differentOrders, so that's many-to-many. -
Draw the diagram. Standard ER notation represents entities as rectangles, attributes as ovals connected to their entity, and relationships as diamonds connecting entities, with lines labeled with cardinality (1, N, or M). Many teams today use "Crow's Foot" notation instead, which shows cardinality as small fork/line symbols at each end of a connecting line, since it's more compact.
-
Resolve many-to-many relationships into a linking table. Since the relational model can't directly represent a many-to-many relationship, any M:N relationship in the diagram needs an associative (junction) entity in between, holding foreign keys to both sides plus any attributes specific to that relationship itself.
-
Review for missing pieces. Check every entity has a clear primary key, every relationship has a clearly marked cardinality, and no obvious real-world entity got left out.
A worked example
Continuing the food delivery app:
Customer(1) places (N)Order: one customer, many orders, each order tied to one customer.Order(M) contains (N)MenuItem: many-to-many, resolved by a linking entity, call itOrderItem, with attributesorder_id,menu_item_id, andquantity.Restaurant(1) offers (N)MenuItem: one restaurant can offer many menu items, but each menu item belongs to exactly one restaurant.
After step 6, the many-to-many Order-MenuItem relationship becomes a real table:
order_items
| order_id | menu_item_id | quantity |
|---|---|---|
| 501 | 12 | 2 |
| 501 | 7 | 1 |
| 502 | 12 | 1 |
This table is what lets one order contain multiple different menu items, and lets the same menu item appear across multiple different orders, exactly the many-to-many relationship the diagram called for.
Where this matters in real systems
This exact process, list entities, attributes, relationships, mark cardinality, resolve M:N relationships into linking tables, is what real schema design looks like in practice before a single CREATE TABLE statement gets written, whether it's done on a literal whiteboard, in a diagramming tool, or just worked through carefully in a design document. Skipping straight to writing SQL without this step is exactly how schemas end up with missing relationships or structural mistakes that are painful to fix once real data exists.
What interviewers actually ask about drawing ER diagrams
- "Walk me through how you'd design a schema for [some scenario]." This is essentially asking for the step-by-step process above, live: identify entities, attributes, relationships, cardinality.
- "How do you represent a many-to-many relationship in an actual database?" Through a linking (junction) table holding foreign keys to both sides, since a plain foreign key can't express many-to-many on its own.
- "What notation do you use for ER diagrams?" Either classic Chen notation (rectangles, ovals, diamonds) or Crow's Foot notation, both are fair answers, the key is understanding what the symbols represent.
- "What do you do after identifying a many-to-many relationship?" Resolve it into a separate table with foreign keys to both entities, and add any attributes that specifically belong to that relationship (like
quantityin the order items example).
Key takeaways
- ER diagrams are built through a defined sequence: identify entities, list attributes, find relationships, determine cardinality, then resolve many-to-many relationships into linking tables.
- A linking (junction) table is required to represent any many-to-many relationship in a relational schema.
- Cardinality (1:1, 1:N, M:N) should be explicitly determined for every relationship, not assumed.
- This process is meant to happen before writing SQL, catching structural issues while they're still cheap to fix.
More in RDBMS
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
Walk me through how you'd design a schema for [some scenario].
This is essentially asking for the step-by-step process above, live: identify entities, attributes, relationships, cardinality.
How do you represent a many-to-many relationship in an actual database?
Through a linking (junction) table holding foreign keys to both sides, since a plain foreign key can't express many-to-many on its own.
What notation do you use for ER diagrams?
Either classic Chen notation (rectangles, ovals, diamonds) or Crow's Foot notation, both are fair answers, the key is understanding what the symbols represent.
What do you do after identifying a many-to-many relationship?
Resolve it into a separate table with foreign keys to both entities, and add any attributes that specifically belong to that relationship (like `quantity` in the order items example).