Database schema design with Mermaid ER diagrams

Entity-relationship diagrams are one of the oldest tools in software design — and one of the most consistently abandoned. Teams sketch a schema on a whiteboard, build the database, and never update the diagram again. Mermaid's erDiagram syntax breaks that cycle: your ERD is a plain-text file that travels with your migrations and gets reviewed in the same pull request as the schema change.

What makes Mermaid ERDs different

Most ERD tools produce binary or XML files that are hard to diff and impossible to write from memory. Mermaid's erDiagram is text you can type in a minute and read back in five seconds:

erDiagram
    USER {
        uuid id PK
        string email UK
        string name
        timestamp created_at
    }
    ORDER {
        uuid id PK
        uuid user_id FK
        decimal total
        string status
    }
    USER ||--o{ ORDER : "places"

This renders a clean ER diagram with labeled relationship lines. The source lives in a markdown file, not a proprietary format.

The syntax at a glance

Entities are declared as named blocks with optional attribute lists:

ENTITY_NAME {
    type  attribute_name  PK|FK|UK
}

Attribute types are labels for readers, not enforced constraints. Use whatever matches your database: int, varchar, uuid, timestamptz, jsonb.

Relationships use crow's-foot notation on a single line:

ENTITY_A relationship ENTITY_B : "label"

The relationship string encodes cardinality on each side:

Symbol	Meaning
|o	Zero or one
||	Exactly one
o{	Zero or many
|{	One or many

So USER ||--o{ ORDER : "places" reads: one user places zero or many orders.

A real-world example: e-commerce schema

Here's a minimal e-commerce schema — users, products, orders, and line items:

erDiagram
    USER {
        uuid id PK
        string email UK
        string name
        timestamp created_at
    }
    ADDRESS {
        uuid id PK
        uuid user_id FK
        string line1
        string city
        string country
    }
    PRODUCT {
        uuid id PK
        string name
        decimal price
        int stock
    }
    ORDER {
        uuid id PK
        uuid user_id FK
        uuid address_id FK
        string status
        timestamp placed_at
    }
    ORDER_ITEM {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
    }

    USER ||--o{ ADDRESS : "has"
    USER ||--o{ ORDER : "places"
    ORDER ||--o{ ORDER_ITEM : "contains"
    ORDER }o--|| ADDRESS : "ships to"
    PRODUCT ||--o{ ORDER_ITEM : "appears in"

Five entities, five relationships, roughly 40 lines. A new engineer can orient to this schema in under two minutes.

What to include — and what to leave out

Include:

• Primary keys and foreign keys
• Unique constraints (UK)
• Junction tables, even if they feel obvious
• Relationship labels — "places," "contains," "ships to" — they answer the cardinality question before someone has to ask

Leave out:

• Indexes (those belong in migration files, not diagrams)
• Every column on wide tables (pick the 5–8 that matter; add a comment like "— audit columns omitted")
• Internal implementation details that change often

The goal is communication, not completeness. A diagram that shows everything teaches nothing.

Naming conventions that read well

A few habits keep Mermaid ERDs consistent across teams:

• UPPER_CASE entity names visually separate tables from attribute names and match the convention most SQL developers expect.
• Singular names (USER, not USERS) match how most ORMs model entities.
• Lowercase attribute names reduce visual noise inside entity blocks.
• Active-voice relationship labels — "places," "contains," "ships to" — make the diagram read like sentences. USER ||--o{ ORDER : "places" should parse as: one user places zero or many orders.

Keeping ERDs in sync with migrations

The most common failure mode for ERDs is drift — the diagram describes last quarter's schema. Two habits prevent it:

Update the diagram in the same PR as the migration. If a PR adds a discount_code column to ORDER, the ERD update belongs in the same review. Reviewers catch schema mistakes and diagram omissions at once, and the diagram is never more than one PR behind.

Put the ERD in the README of the relevant service, not in a separate wiki. When someone opens the repo, the diagram is one of the first things they see. Out-of-sight diagrams become out-of-date diagrams.

A Mermaid ERD in a README takes about five seconds to open and one line to change. That low friction is what keeps it accurate.

Multi-schema and microservice schemas

For systems split across multiple services, maintain one ERD per service and link them in prose rather than cramming everything into a single diagram. A 30-entity diagram is impressive; a readable one is useful.

When a foreign key crosses a service boundary (e.g., order_id stored in an analytics service), document it as a conceptual relationship without an enforced FK:

erDiagram
    ORDER {
        uuid id PK
        string status
    }
    ANALYTICS_EVENT {
        uuid id PK
        uuid order_id
        string event_type
        timestamp occurred_at
    }
    ORDER ||--o{ ANALYTICS_EVENT : "tracked by"

The label "tracked by" communicates intent even though there's no database-level constraint enforcing the relationship.

Tooling note

Mermaid ERDs render natively in GitHub markdown, GitLab, Notion, and most modern wiki tools. No dedicated ERD tool or diagram plugin required — the same syntax in your README renders in PR descriptions and internal docs.

For richer editing, MermaidCreator gives you a live preview as you build the schema. Once you're happy with the structure, copy the Mermaid source into your repository and it travels with the code forever.

A database schema that lives only in someone's head is a bus-factor problem. A Mermaid ERD in your README is not.