Designing Mermaid ER Diagrams

Welcome back! In our previous lesson, we established the conceptual foundation for data modeling by learning how to derive entities, attributes, and relationships from a domain description. You now have a process for creating a logical blueprint of a system's data.

Today, we'll turn that blueprint into a tangible artifact. This lesson focuses on translating your conceptual model into a concrete Entity-Relationship Diagram (ERD) using Mermaid's syntax. We will cover how to define entities and their attributes, but our main goal is to master the notation for relationships, ensuring we can accurately represent both the cardinality (how many) and optionality (whether it's required) of the connections between your data entities.

From Concepts to Code: Basic ERD Syntax

Let's begin with the fundamental building blocks of a Mermaid ERD. As with other Mermaid diagrams, you start with a declaration, erDiagram, to signal what you're creating. From there, you define each entity and its attributes within a set of curly braces.

This process is a direct translation of the conceptual work you did in the last lesson. An entity becomes a named block, and its attributes are listed inside.

As you can see, the syntax is straightforward and declarative, which should feel familiar given your development experience. You define what exists (USER, POST), its properties (int id, string email), and their constraints (PK, UK). Notice that Mermaid doesn't enforce specific data types; you can use conventional types like int and string, or more database-specific ones like varchar(255) and datetime. Consistency is the key.

Defining Relationships: Cardinality and Optionality

The most expressive—and initially, most complex—part of Mermaid ERD syntax is how it defines relationships. It uses a compact notation that precisely captures the business rules governing how entities connect. Mastering this allows you to specify rules like "an order must contain at least one item" or "a user may have zero or one billing address."

The image below shows a Mermaid editor with the code on the left and the resulting visual ER diagram on the right. This instant feedback loop is one of Mermaid's greatest strengths, allowing you to iterate on your data model visually.

To understand the connecting lines, we need to decode the cardinality syntax.

To summarize the core components of a relationship:
ENTITY_A cardinality_A -- cardinality_B ENTITY_B : "label"

The cardinality markers define the relationship from the perspective of each entity. The label describes the action or connection, usually a verb.

Let's break down a common example: CUSTOMER ||--o{ ORDER : "places"

• CUSTOMER ||: The || next to CUSTOMER means one and only one customer.
• --: This is a solid line, typically representing an identifying relationship where the child entity (ORDER) depends on the parent (CUSTOMER) for its existence. A dotted line .. represents a non-identifying relationship where entities can exist independently.
• ORDER o{: The o{ next to ORDER means zero or more orders.
• : "places": This is the label describing the relationship.

Reading it all together: "One and only one Customer places zero or more Orders."

Here is a quick reference table for the most common cardinality pairs:

Mermaid Syntax	Meaning	Example Use Case
`		--
`		--o
`		--
`		--o{`

Worked Example: The Bug Tracker

Let's apply this to the bug tracking system from our previous lesson.

Recall the model:

• Entities: User, BugReport
• Attributes:
  • User: user_id (PK), name, email
  • BugReport: bug_id (PK), title, description, priority, status
• Relationships:
  • A BugReport is submitted by exactly one User.
  • A BugReport can be assigned to zero or one User (developer).
  • A User can submit many BugReports and be assigned many BugReports.

To model this, we need two foreign keys in the BUG_REPORT entity: one for the reporter and one for the assignee.

Here is the complete Mermaid diagram:

Let's analyze the relationships we defined:

1. USER ||--o{ BUG_REPORT : "reports"

   • This line states: One USER reports zero or more BUG_REPORTs. This correctly models the relationship from the perspective of the USER and links to the reporter_id foreign key.

2. USER ||--o{ BUG_REPORT : "is assigned"

   • This second line defines the other role. It states: One USER is assigned zero or more BUG_REPORTs. This models the relationship from the USER (as a developer) to the reports they are working on, corresponding to the assignee_id foreign key.

Note that while a BUG_REPORT must have a reporter, it might not have an assignee yet. The current model using ||--o{ is a standard one-to-many, which is the most common way to express this in Mermaid. The constraint that reporter_id cannot be null, while assignee_id can, is a physical-level detail that the logical diagram implies through the business rules.

Practice Exercise

Now it's your turn to put this into practice. Let's model a simple product catalog.

Scenario:

You are designing a database for an e-commerce platform. The system needs to track products and categories. A product can belong to multiple categories (e.g., a "Running Shoe" can be in "Footwear" and "Sports"). Likewise, a category can contain many products.

• A PRODUCT has a unique ID, a unique SKU (stock keeping unit), a name, and a price.
• A CATEGORY has a unique ID, a unique name, and a short description.

Your task is to create the complete Mermaid ER diagram for this scenario. Remember how many-to-many relationships are typically implemented in a relational database.

Click here for a hint.

A direct many-to-many relationship (`o{--o{`) is a conceptual idea. In a physical relational database, you need an intermediate table, often called a "join table" or "junction table," to implement it. What would that table look like here? It needs to connect a product to a category.

Click here to see the solution.

To resolve the many-to-many relationship between PRODUCT and CATEGORY, we introduce a PRODUCT_CATEGORY join table. This table's primary purpose is to hold pairs of product_id and category_id.

Here is the complete Mermaid code:

Explanation:

• We defined PRODUCT and CATEGORY entities with their respective attributes and keys (PK, UK).
• We created PRODUCT_CATEGORY to link them. It contains two foreign keys, one pointing to PRODUCT's primary key and one to CATEGORY's.
• PRODUCT ||--o{ PRODUCT_CATEGORY : "belongs to": This relationship shows that one PRODUCT is associated with zero or more entries in the PRODUCT_CATEGORY table.
• CATEGORY ||--o{ PRODUCT_CATEGORY : "contains": Similarly, one CATEGORY is associated with zero or more entries in the PRODUCT_CATEGORY table.

This structure correctly models that a product can be in many categories and a category can hold many products.

Conclusion

In this lesson, you've bridged the gap between conceptual data modeling and concrete implementation with Mermaid. You've learned the syntax for defining entities, attributes, and keys (PK, FK, UK). Most importantly, you now know how to use Mermaid's powerful cardinality notation (||, o|, |{, o{) to create relationships that accurately reflect business rules. By practicing with the bug tracker and product catalog examples, you've seen how to model one-to-many and many-to-many relationships, which are fundamental patterns in database design.

We have now explored diagrams for structure (Class Diagrams) and data (ER Diagrams). In the next module, we will shift our focus to behavior over time. Our first lesson will introduce Sequence Diagrams, which are invaluable for visualizing the flow of interactions between different parts of a system, such as the calls in an API request.