Chapter 12: How to Create ER Diagrams — Visualizing Business Requirements

What you'll learn in this chapter

• Explain the basic elements of an ER diagram (entities / attributes / relationships)
• Write cardinality (one-to-one / one-to-many / many-to-many) in mermaid notation
• Read a multiplicity marker as a maximum and a minimum, and explain that the shapes mirror between the two sides
• Follow the steps for creating an ER diagram from business requirements (e.g., a library)
• Know how to choose between the major ER diagram tools (mermaid / draw.io / PlantUML)

Prerequisites

You should understand Chapter 10: Key Concepts and Chapter 11: Normalization.

What Is an ER Diagram?

An ER diagram (Entity-Relationship Diagram) is a diagram that visually represents a database's structure as a combination of entities and relationships. It was proposed in 1976 by Peter Chen.

Element	Example
Entity	Customer, product, order, employee, department
Attribute	Customer name, product price, order date-time
Relationship	"A customer places an order," "a product is included in an order"

Entity Notation

Using mermaid's erDiagram notation lets you write an ER diagram as text (this guide uses it throughout, in every chapter).

Symbol	Meaning
PK	Primary Key
FK	Foreign Key
UK	Unique Key

Relationships and Cardinality

A relationship expresses the "multiplicity" between entities. In mermaid's erDiagram, you write one like this:

CUSTOMER ||--o{ ORDER : places

|| and o{ are the multiplicity markers, -- is the relationship line, and places is the relationship name. Relationship names are written as verbs.

How to Read the Markers

A multiplicity marker is two characters, and one sits on each side of the line. The shapes are mirrored between the two sides.

Meaning	Left	Right
Exactly one	||	||
Zero or one	|o	o|
One or more	}|	|{
Zero or more	}o	o{

"Zero or more" is }o on the left and o{ on the right — in both, the crow's foot faces the entity and the circle sits on the line side.

The orientation does not affect what gets drawn. mermaid reads o{ on the left as the same thing as }o, so the diagram is identical. You still follow the table because the reading rule below depends on the orientation. Write o{ on the left and the character on the entity side becomes o, which under that rule says "at most zero" — not a meaning you want.

The two characters follow a rule.

• The character nearer the entity is the maximum: | for one, } or { (a crow's foot) for many
• The character nearer the line is the minimum: | for one, o (a circle) for zero

Take o{ on the right: { on the entity side means "at most many," o on the line side means "at least zero," so together they mean zero or more. A circle means "zero is allowed" — that is, optional.

A marker describes the entity it sits next to.

Position	Marker	Reading
CUSTOMER side	||	Exactly one CUSTOMER
ORDER side	o{	Zero or more ORDERs

So CUSTOMER ||--o{ ORDER reads: one customer has zero or more orders, and one order belongs to exactly one customer.

You can also spell the markers out as only one, zero or more, one or more and so on; "exactly one" can also be written 1. That reads more naturally but makes lines longer, so this guide sticks to the symbols.

"One-to-many" names only the maximum side

"One-to-one," "one-to-many," and "many-to-many" name only the maximum side. Two relationships can both be "one-to-many" and still differ in whether the minimum is zero or one.

Written as	Meaning
CUSTOMER ||--o{ ORDER	A customer with no orders at all is allowed
CUSTOMER ||--|{ ORDER	A customer must have at least one order

The business rule shows up on the minimum side. Whether you allow "a customer who registered but never bought anything" is the difference between o and |.

The same goes for "one-to-one." The PERSON ||--|| PASSPORT under One-to-One below states the classification, but read down to the minimum and it says "a person without a passport cannot exist." To allow one, write PERSON ||--o| PASSPORT. The diagram under Supertype and Subtype in this chapter does use o|.

Line Types

Besides --, the line can also be written ... mermaid treats -- as identifying and .. as non-identifying, rendering them as a solid and a dashed line respectively. Every diagram in this guide uses --; we do not draw that distinction.

One-to-One

This is a relationship like "a person and a passport" (one passport per person, one person per passport).

Protecting PASSPORT.person_id with a UK (UNIQUE constraint) guarantees "one passport per person."

One-to-Many

This is a relationship like "a department and its employees" (a department has multiple employees, an employee belongs to one department).

As long as the foreign key EMPLOYEE.dept_id isn't made UNIQUE, multiple employees can reference the same department (= one-to-many).

Many-to-Many

This is a relationship like "students and courses" (a student takes multiple courses, a course has multiple students enrolled).

Many-to-many is expressed with a junction table (ENROLLMENT in the example above). The benefit is that the junction table itself can hold attributes (enrollment date, grade). Many-to-many is covered in detail in Chapter 14: Many-to-Many.

A direct many-to-many notation is misleading

mermaid also lets you write a "direct many-to-many" notation like }o--o{, but an RDB always implements it with a junction table. At the physical-design level, an ER diagram is more accurate — and less prone to misunderstanding — if it makes the junction table explicit.

A Practical Example: An ER Diagram for an E-Commerce Site

Let's look at an e-commerce site as a typical example of an ER diagram.

Reading the direction of the lines and the symbols:

• One customer places multiple orders (CUSTOMER ||--o{ ORDER)
• One order has multiple order items (ORDER ||--o{ ORDER_ITEM)
• One product is included in multiple order items (PRODUCT ||--o{ ORDER_ITEM)
• Categories express a hierarchy through self-reference (the parent category ID)

Steps for Creating an ER Diagram

Here's the standard procedure for creating an ER diagram from business requirements.

Step 1: Extract Entities

As you read through the business requirements, pay attention to nouns. Things, people, and events that need to be persisted are entity candidates.

Step 2: Identify Attributes

List the attributes each entity needs. Judge based on "will this need to be searched, displayed, or aggregated in the future?"

Candidate attributes for CUSTOMER:
- Identifier: customer_id (PK)
- Basic info: name (required), email (required, unique), phone
- Auth info: password_hash (required)
- Meta: created_at, updated_at, is_active

Step 3: Define Relationships

Classify the relationships between entities as "one-to-one / one-to-many / many-to-many."

Step 4: Check Normalization

Run the Chapter 11: Normalization checks (first through third normal form), and decompose further if needed.

Step 5: Move on to Physical Design

Expand the ER diagram into CREATE TABLE statements, and decide on data types, indexes, and constraints (see the physical-design section of Chapter 9: The Flow of Design for details).

Supertype and Subtype — Expressing an Inheritance Relationship

A technique for expressing an inheritance relationship, like "a user can be both a customer and an employee."

In this design, common attributes go on the users table, and the customers / employees subtype tables add their own specific attributes. The pattern of implementing multiple subtypes around a central USER also comes up often in authentication/authorization system design (covered in Chapter 15: Authentication and Authorization).

A Practical Example: A Library Management System

Let's build an ER diagram using a more composite example.

Business Requirements

• A member can borrow books
• A book can have multiple authors
• Books have categories (a hierarchical structure)
• A member can reserve a book (if it's currently on loan)
• A loan has a due date and a late fee

ER Diagram

Key points:

• BOOK_AUTHOR is the junction table expressing the many-to-many relationship between books and authors
• CATEGORY.parent_category_id self-references to express a hierarchy
• LOAN is the entity representing the event "a member borrows a book"
• RESERVATION is the event "a member reserves a book"

ER Diagram Tools

Tool	Characteristics	Price	Used in this guide
mermaid	Text-based, works with Git, embeds in Markdown	Free	✓ (every diagram in this guide is mermaid)
draw.io (app.diagrams.net)	GUI, has a web version	Free	Recommended when drawing takes priority
PlantUML	Text-based, works with version control	Free	Good for drawing large diagrams
MySQL Workbench	MySQL-only, forward/reverse engineering	Free	Handy when adopting MySQL
Lucidchart	Feature-rich, supports team collaboration	Paid	Documentation for commercial projects

PlantUML Notation (For Reference)

@startuml
entity "Customer" {
  * customer_id : int <<PK>>
  --
  * email : varchar(255) <<UK>>
  * name : varchar(100)
  created_at : timestamp
}

entity "Order" {
  * order_id : int <<PK>>
  --
  * customer_id : int <<FK>>
  * order_date : datetime
  total_amount : decimal(12,2)
}

Customer ||--o{ Order : places
@enduml

See the official PlantUML documentation for more.

Best Practices

Tips for junior engineers

• Start on paper: sketch out the main entities and relationships with pen and paper before formalizing them with a tool
• Build it together with business stakeholders: rather than aiming for perfection alone, check in with people on the ground — "I understood it this way, is that right?"
• There's no single correct answer: even for the same business, different designers can land on different ER diagrams. Reaching agreement as a team is what matters

Exercises

Exercise 1: Build a simple ER diagram

Create an ER diagram from the following business requirements. Try writing the entities, attributes, and cardinality in mermaid notation.

• On a blog site, users can post articles
• A single user can post multiple articles
• A single article can have multiple tags attached
• A single tag is used across multiple articles
• Other users can comment on each article (one article, multiple comments)
• A comment is linked to both the article and the user who wrote it

Sample answer

Key points:

• USER → POST is one-to-many (one user, multiple articles)
• POST ↔ TAG is many-to-many → the junction table POST_TAG
• COMMENT references both POST and USER (one-to-many × 2)

Common mistakes:

1. Putting tags into a POST.tags column as CSV: violates normalization (breaks first normal form). Makes searching and aggregating difficult
2. Forgetting COMMENT.user_id: you can't tell who wrote a comment
3. Making COMMENT self-referencing (a reply feature): the requirements above don't mention "replies," so this is unnecessary. Don't add things that aren't in the requirements

Alternative approach: if you want to quickly retrieve "how many times a tag has been used," you could add a usage_count column to TAG (denormalization). But this requires managing consistency on update, so it's safer to handle it with an aggregate query at first (discussed in Chapter 14: Many-to-Many).

Exercise 2: Determine cardinality

Which is each of the following relationships — "one-to-one," "one-to-many," or "many-to-many"?

1. A user and a profile (a user has one profile)
2. A blog post and likes (a post has multiple likes, a like belongs to one post)
3. A recipe and ingredients (a recipe has multiple ingredients, an ingredient is used in multiple recipes)
4. An order and a delivery (one delivery per order, one delivery slip corresponds to one order)
5. A playlist and songs (a playlist has multiple songs, a song is included in multiple playlists)

Sample answer

#	Relationship	Reason
1. A user and a profile	One-to-one	1 user → 1 profile
2. A post and likes	One-to-many	1 post → multiple likes, 1 like → 1 post
3. A recipe and ingredients	Many-to-many	1 recipe has multiple ingredients, and 1 ingredient is used in multiple recipes → junction table recipe_ingredients
4. An order and a delivery	One-to-one	1 order → 1 delivery
5. A playlist and songs	Many-to-many	1 playlist has multiple songs, and 1 song is in multiple playlists → junction table playlist_songs

Additional note (designing one-to-one relationships): for a one-to-one relationship, first consider "could this be merged into the same table?" (for example, a user and a profile are often combined into a single users table). Deliberately splitting them makes sense when there's a clear reason, like:

• The profile information is large and isn't accessed frequently (a vertical split for performance)
• You want to separate access permissions (basic info is visible to everyone, sensitive info only to the person themselves)

A common mistake: judging #5, a playlist and songs, as "one-to-many." It's easy to overlook that "one song is included in multiple playlists." The trick is to check whether either side can have multiple on both directions.

Summary

Here's a recap of what this chapter covered.

• An ER diagram visualizes a database's structure through entities, attributes, and relationships
• Cardinality has three basic kinds: one-to-one / one-to-many / many-to-many — but those name only the maximum side; the business rule lives on the minimum side (o or |)
• In a multiplicity marker, the character nearer the entity is the maximum and the one nearer the line is the minimum; the shapes mirror between the two sides
• Many-to-many is implemented with a junction table
• The creation process: "extract entities → identify attributes → define relationships → check normalization → physical design"
• Choose between tools based on the situation: mermaid (Git-friendly), draw.io (drawing-first), PlantUML (large diagrams), Workbench (MySQL-only)

The next chapter combines the design techniques covered so far into a hands-on, end-to-end exercise: requirements → ER diagram → table definitions.

Next chapter

Chapter 13: Database Design in Practice