January 2, 2026

ER Diagrams for What’s in the Database with Mermaid

Mermaid ER diagrams are a simple way to answer “what’s in the database?” without drowning in DDL. Learn cardinality, optional relationships, join tables, reference data, and real-world patterns that keep diagrams readable and accurate.

When someone asks, “What’s in the database?”, they usually don’t want table DDL.

They want a mental model:

What are the core entities?
How do they relate?
What is one-to-many vs many-to-many?
Where do foreign keys live?
What can be NULL / optional?

Mermaid ER diagrams are a great fit because they’re readable, versionable, and simple enough to keep current.

This guide focuses on ER diagrams for real-world schemas: join tables, optional relationships, audit fields, and the patterns that make diagrams stay useful over time.

A quick refresher

Mermaid ER diagrams start with erDiagram and use crow’s-foot cardinality markers.

Cardinality cheatsheet

|| exactly one
o| zero or one
|{ one or more
o{ zero or more

Relationships combine two sides, like:

CUSTOMER ||--o{ ORDER : places
- a customer places 0..many orders
- an order belongs to exactly one customer

1) Start with the smallest useful model

A good ER diagram is not “every table.” It’s the tables you need to understand the domain.

Customers and orders

Source

erDiagram
  CUSTOMER ||--o{ ORDER : places

  CUSTOMER {
    uuid id PK
    string email
    string name
    datetime created_at
  }

  ORDER {
    uuid id PK
    uuid customer_id FK
    string status
    datetime created_at
  }

erDiagram

CUSTOMER ||--o{ ORDER : places

CUSTOMER {

uuid id PK

string email

string name

datetime created_at

}

ORDER {

uuid id PK

uuid customer_id FK

string status

datetime created_at

}

Rendered

erDiagram CUSTOMER ||--o{ ORDER : places CUSTOMER { uuid id PK string email string name datetime created_at } ORDER { uuid id PK uuid customer_id FK string status datetime created_at }

Tip: Add attributes only for fields that clarify the contract: primary keys, foreign keys, and a few “identity” fields.

2) Model one-to-many and enforce the “FK lives on the many side” rule

A classic mistake: placing foreign keys on both sides in your thinking.

Orders and line items

Source

erDiagram
  ORDER ||--|{ LINE_ITEM : contains

  ORDER {
    uuid id PK
    uuid customer_id FK
    string status
  }

  LINE_ITEM {
    uuid id PK
    uuid order_id FK
    string sku
    int quantity
    decimal unit_price
  }

erDiagram

ORDER ||--|{ LINE_ITEM : contains

ORDER {

uuid id PK

uuid customer_id FK

string status

}

LINE_ITEM {

uuid id PK

uuid order_id FK

string sku

int quantity

decimal unit_price

}

Rendered

erDiagram ORDER ||--|{ LINE_ITEM : contains ORDER { uuid id PK uuid customer_id FK string status } LINE_ITEM { uuid id PK uuid order_id FK string sku int quantity decimal unit_price }

3) Optional relationships (where NULLs come from)

Optionality is where a lot of production bugs hide. ER diagrams are a clean way to document it.

An order may have zero or one coupon

Source

erDiagram
  ORDER }o--o| COUPON : applies

  ORDER {
    uuid id PK
    uuid coupon_id FK
    decimal total
  }

  COUPON {
    uuid id PK
    string code
    decimal discount
  }

erDiagram

ORDER }o--o| COUPON : applies

ORDER {

uuid id PK

uuid coupon_id FK

decimal total

}

COUPON {

uuid id PK

string code

decimal discount

}

Rendered

erDiagram ORDER }o--o| COUPON : applies ORDER { uuid id PK uuid coupon_id FK decimal total } COUPON { uuid id PK string code decimal discount }

How to read }o--o|:

Each ORDER has 0..1 coupon
Each COUPON is associated with 0..many orders

4) Many-to-many: show the join table explicitly

In real databases, many-to-many relationships almost always exist as a join table.

Users, roles, and a join table (with audit fields)

Source

erDiagram
  USER ||--o{ USER_ROLE : has
  ROLE ||--o{ USER_ROLE : assigned

  USER {
    uuid id PK
    string email
    string name
  }

  ROLE {
    uuid id PK
    string name
  }

  USER_ROLE {
    uuid user_id PK,FK
    uuid role_id PK,FK
    datetime granted_at
    uuid granted_by FK
  }

erDiagram

USER ||--o{ USER_ROLE : has

ROLE ||--o{ USER_ROLE : assigned

USER {

uuid id PK

string email

string name

}

ROLE {

uuid id PK

string name

}

USER_ROLE {

uuid user_id PK,FK

uuid role_id PK,FK

datetime granted_at

uuid granted_by FK

}

Rendered

erDiagram USER ||--o{ USER_ROLE : has ROLE ||--o{ USER_ROLE : assigned USER { uuid id PK string email string name } ROLE { uuid id PK string name } USER_ROLE { uuid user_id PK,FK uuid role_id PK,FK datetime granted_at uuid granted_by FK }

Tip: Many-to-many join tables are often where you store extra meaning (like who granted a role, when, and why).

5) Reference data vs core entities

Not everything needs full attributes in the diagram. For “reference tables” (countries, currencies, statuses), you can keep them minimal.

Source

erDiagram
  CURRENCY ||--o{ MONEY_AMOUNT : used_by

  CURRENCY {
    string code PK
  }

  MONEY_AMOUNT {
    uuid id PK
    string currency_code FK
    decimal amount
  }

erDiagram

CURRENCY ||--o{ MONEY_AMOUNT : used_by

CURRENCY {

string code PK

}

MONEY_AMOUNT {

uuid id PK

string currency_code FK

decimal amount

}

Rendered

erDiagram CURRENCY ||--o{ MONEY_AMOUNT : used_by CURRENCY { string code PK } MONEY_AMOUNT { uuid id PK string currency_code FK decimal amount }

6) Modeling soft deletes and lifecycle fields (without clutter)

Schemas often have lifecycle fields that matter operationally. Include them, but don’t let them dominate.

Source

erDiagram
  CUSTOMER ||--o{ ORDER : places

  CUSTOMER {
    uuid id PK
    string email
    datetime created_at
    datetime deleted_at
  }

  ORDER {
    uuid id PK
    uuid customer_id FK
    string status
    datetime created_at
    datetime canceled_at
  }

erDiagram

CUSTOMER ||--o{ ORDER : places

CUSTOMER {

uuid id PK

string email

datetime created_at

datetime deleted_at

}

ORDER {

uuid id PK

uuid customer_id FK

string status

datetime created_at

datetime canceled_at

}

Rendered

erDiagram CUSTOMER ||--o{ ORDER : places CUSTOMER { uuid id PK string email datetime created_at datetime deleted_at } ORDER { uuid id PK uuid customer_id FK string status datetime created_at datetime canceled_at }

Tip: If lifecycle fields are the story (e.g., “draining/archived”), a state diagram often complements the ER diagram nicely.

7) Common gotchas (and how to avoid them)

Gotcha: the diagram becomes unreadable

Fix: make one diagram per bounded context (billing, auth, orders) and link between posts instead of cramming.

Gotcha: unclear naming

Fix:

Tables: SNAKE_CASE or PascalCase, but be consistent
Primary keys: id
Foreign keys: <entity>_id (customer_id, order_id)

Gotcha: mismatched cardinality

Fix: sanity-check with real questions:

“Can an order exist without a customer?” (usually no)
“Can a coupon apply to multiple orders?” (yes)
“Can a user have zero roles?” (often yes)

8) A blog-ready checklist for ER diagrams

Before you publish:

Start with entities and relationships, then add only the attributes that clarify meaning.
Show PK and FK fields at a minimum.
Make optionality explicit (o|, o{).
Use join tables for many-to-many.
Keep the diagram to ~8–12 tables. Split larger schemas.
Add relationship labels that read like English: places, contains, assigned.

Where to go next

If ER diagrams are working for you, the next Mermaid diagram type that pairs well is sequence diagrams for queries and write paths:

Use ER diagrams to show the data model.
Use sequence diagrams to show which tables are touched during a request (and where latency might come from).