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
• 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 a relationship between two entities like this:

CUSTOMER ||--o{ ORDER : places

This means "a CUSTOMER has zero or more ORDERs (one-to-many)," and the relationship name (places) is written as a verb.

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
• 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