Mermaid Erd Creation

Mermaid ERD Creation Guide

Entity Relationship Diagrams (ERDs) visualize database schemas and relationships between entities. Use Mermaid syntax for all ERD diagrams in this project.

Basic Syntax

Minimal ERD Example

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

Entity Declaration

Format: ENTITY_NAME { }

Rules:

• Entity names in UPPERCASE (e.g., USER, ORDER, PAYMENT)
• Use singular nouns (USER not USERS)
• Avoid abbreviations unless industry-standard (e.g., OAUTH_TOKEN is fine)

Field Declaration

Format: type name constraint

Components:

1. Type - Data type (string, int, uuid, timestamp, boolean, decimal, text, json)
2. Name - Field name in snake_case
3. Constraint - PK (primary key), FK (foreign key), UK (unique key), or empty

Examples:

erDiagram
    USER {
        uuid id PK
        string email UK
        string password_hash
        timestamp created_at
        timestamp updated_at
        boolean is_active
        int login_count
    }

Relationship Declaration

Format: ENTITY_A CARDINALITY ENTITY_B : "relationship_label"

Cardinality Symbols:

• ||--|| : Exactly one to exactly one
• ||--o| : Exactly one to zero or one
• ||--o{ : Exactly one to zero or many
• }o--o{ : Zero or many to zero or many
• }|--|{ : One or many to one or many

Reading Relationships:

• || = exactly one
• o| = zero or one
• o{ = zero or many
• |{ = one or many

Relationship Types Explained

One-to-One (1:1)

Use case: Extension tables, profile data

Syntax: ||--||

erDiagram
    USER {
        uuid id PK
        string email
    }
    USER_PROFILE {
        uuid id PK
        uuid user_id FK
        string bio
        string avatar_url
    }
    USER ||--|| USER_PROFILE : "has"

When to use:

• Each user has exactly one profile
• Splitting large tables for performance
• Separating frequently vs rarely accessed data

One-to-Many (1:N)

Use case: Most common relationship type

Syntax: ||--o{ (one to zero-or-many)

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

Reading: "One USER places zero or many ORDERS"

When to use:

• Parent-child relationships
• Ownership (user owns posts, orders, etc.)
• Hierarchical data

Many-to-Many (N:M)

Use case: Multiple associations in both directions

Syntax: }o--o{ (zero-or-many to zero-or-many)

IMPORTANT: Requires junction table

erDiagram
    USER {
        uuid id PK
        string email
    }
    ROLE {
        uuid id PK
        string name UK
    }
    USER_ROLE {
        uuid user_id FK
        uuid role_id FK
    }
    USER ||--o{ USER_ROLE : "has"
    ROLE ||--o{ USER_ROLE : "assigned_to"
    USER }o--o{ ROLE : "has_roles"

When to use:

• Users have multiple roles, roles have multiple users
• Products in multiple categories, categories have multiple products
• Students enrolled in courses, courses have multiple students

Best Practice: Always create explicit junction table

Complete Example: E-Commerce System

erDiagram
    USER {
        uuid id PK
        string email UK
        string password_hash
        string first_name
        string last_name
        timestamp created_at
        timestamp updated_at
        boolean is_active
    }
    
    ADDRESS {
        uuid id PK
        uuid user_id FK
        string street_line1
        string street_line2
        string city
        string state
        string postal_code
        string country
        boolean is_default
    }
    
    ORDER {
        uuid id PK
        uuid user_id FK
        uuid shipping_address_id FK
        uuid billing_address_id FK
        decimal subtotal
        decimal tax
        decimal shipping_cost
        decimal total
        string status
        timestamp created_at
        timestamp updated_at
    }
    
    ORDER_ITEM {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
        decimal total_price
    }
    
    PRODUCT {
        uuid id PK
        string sku UK
        string name
        text description
        decimal price
        int stock_quantity
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }
    
    CATEGORY {
        uuid id PK
        string name UK
        string slug UK
        text description
    }
    
    PRODUCT_CATEGORY {
        uuid product_id FK
        uuid category_id FK
    }
    
    PAYMENT {
        uuid id PK
        uuid order_id FK
        decimal amount
        string payment_method
        string transaction_id UK
        string status
        timestamp created_at
    }
    
    USER ||--o{ ADDRESS : "has"
    USER ||--o{ ORDER : "places"
    ADDRESS ||--o{ ORDER : "ships_to"
    ADDRESS ||--o{ ORDER : "bills_to"
    ORDER ||--o{ ORDER_ITEM : "contains"
    ORDER ||--o| PAYMENT : "paid_by"
    PRODUCT ||--o{ ORDER_ITEM : "ordered_as"
    PRODUCT ||--o{ PRODUCT_CATEGORY : "categorized_in"
    CATEGORY ||--o{ PRODUCT_CATEGORY : "contains"
    PRODUCT }o--o{ CATEGORY : "belongs_to"

Advanced Patterns

Self-Referencing Relationships

Use case: Hierarchies (org charts, comment threads)

erDiagram
    COMMENT {
        uuid id PK
        uuid parent_comment_id FK
        uuid user_id FK
        text content
        timestamp created_at
    }
    
    COMMENT ||--o{ COMMENT : "replies_to"

Reading: "A COMMENT can have zero or many child COMMENTS"

Polymorphic Relationships

Use case: Comments on multiple entity types

Approach 1: Separate junction tables (recommended)

erDiagram
    COMMENT {
        uuid id PK
        uuid user_id FK
        text content
        timestamp created_at
    }
    
    POST {
        uuid id PK
        string title
    }
    
    VIDEO {
        uuid id PK
        string title
    }
    
    POST_COMMENT {
        uuid post_id FK
        uuid comment_id FK
    }
    
    VIDEO_COMMENT {
        uuid video_id FK
        uuid comment_id FK
    }
    
    POST ||--o{ POST_COMMENT : "has"
    COMMENT ||--o{ POST_COMMENT : "on"
    VIDEO ||--o{ VIDEO_COMMENT : "has"
    COMMENT ||--o{ VIDEO_COMMENT : "on"

Approach 2: Type discriminator (not recommended)

erDiagram
    COMMENT {
        uuid id PK
        uuid user_id FK
        uuid commentable_id FK
        string commentable_type
        text content
        timestamp created_at
    }

Problem: Can't use foreign key constraints, breaks referential integrity.

Naming Conventions

Entity Names

• Use: UPPERCASE, singular nouns
• Examples: USER, ORDER, PRODUCT, PAYMENT
• Avoid: USERS (plural), user (lowercase), Usr (abbreviation)

Field Names

• Use: snake_case
• Examples: user_id, created_at, email_verified, password_hash
• Avoid: userId (camelCase), USERID (uppercase), usrID (abbreviation)

Relationship Labels

• Use: Verb phrases describing the relationship
• Examples: "places", "has", "belongs_to", "ships_to", "paid_by"
• Avoid: Vague labels like "related", "associated", "linked"

Common Patterns

Timestamps (Audit Fields)

Always include:

timestamp created_at
timestamp updated_at

Optional (for soft deletes):

timestamp deleted_at

Soft Deletes

erDiagram
    USER {
        uuid id PK
        string email UK
        boolean is_deleted
        timestamp deleted_at
        timestamp created_at
        timestamp updated_at
    }

When to use:

• Regulatory requirements (data retention)
• Audit trails needed
• Undelete functionality required

Versioning

erDiagram
    DOCUMENT {
        uuid id PK
        string title
        int version
        timestamp created_at
    }
    
    DOCUMENT_VERSION {
        uuid id PK
        uuid document_id FK
        int version_number
        text content
        uuid created_by_user_id FK
        timestamp created_at
    }
    
    DOCUMENT ||--o{ DOCUMENT_VERSION : "has_versions"

Best Practices

1. Always Define Primary Keys

Good:

USER {
    uuid id PK
    string email
}

Bad:

USER {
    string email
}

2. Use Meaningful Foreign Key Names

Good:

ORDER {
    uuid id PK
    uuid user_id FK
    uuid shipping_address_id FK
}

Bad:

ORDER {
    uuid id PK
    uuid fk1 FK
    uuid fk2 FK
}

3. Include Relationship Labels

Good:

USER ||--o{ ORDER : "places"

Bad:

USER ||--o{ ORDER : ""

4. Document Constraints

Use UK for unique constraints:

USER {
    uuid id PK
    string email UK
}

5. Be Explicit About Optionality

• Use ||--o{ for "zero or many" (optional)
• Use ||--|{ for "one or many" (required)

Common Mistakes

Mistake 1: Forgetting Junction Tables

❌ Wrong:

erDiagram
    USER }o--o{ ROLE : "has"

✓ Correct:

erDiagram
    USER {
        uuid id PK
    }
    ROLE {
        uuid id PK
    }
    USER_ROLE {
        uuid user_id FK
        uuid role_id FK
    }
    USER ||--o{ USER_ROLE : "has"
    ROLE ||--o{ USER_ROLE : "assigned_to"

Mistake 2: Wrong Cardinality

❌ Wrong: User has exactly one order

USER ||--|| ORDER : "places"

✓ Correct: User has zero or many orders

USER ||--o{ ORDER : "places"

Mistake 3: Missing Foreign Keys

❌ Wrong:

ORDER {
    uuid id PK
    uuid user_id
}

✓ Correct:

ORDER {
    uuid id PK
    uuid user_id FK
}

Mistake 4: Plural Entity Names

❌ Wrong:

USERS {
    uuid id PK
}

✓ Correct:

USER {
    uuid id PK
}

When to Use ERDs

Always Use For:

1. Data model design phase - Before writing any schema code
2. Schema migrations - Visualize changes before implementing
3. Documentation - Include in architecture docs, ADRs
4. Stakeholder communication - Non-technical reviewers need visuals

Update ERDs When:

1. Adding new entities
2. Adding/removing relationships
3. Changing cardinality (1:1 → 1:N)
4. Adding significant fields (foreign keys, unique constraints)

Don't Bother For:

1. Adding simple fields to existing entities (unless FK)
2. Index-only changes (ERD shows logical schema, not physical)
3. Minor data type changes

Integration with Data Model Design Process

Step 1: Discovery (use data-model-discovery skill)

Ask questions about entities, relationships, query patterns.

Step 2: ERD Creation (this skill)

Create visual diagram of entities and relationships.

Step 3: Schema Definition

Translate ERD to SQL/ORM schema:

CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Step 4: Review

Validate ERD against:

• Normalization rules (3NF typically)
• Query patterns (denormalize if needed)
• Performance requirements (indexes)

Tools & Rendering

Rendering Options

1. Mermaid Live Editor - https://mermaid.live
2. GitHub/GitLab - Auto-renders in markdown
3. VS Code - Mermaid preview extensions
4. Documentation sites - Sphinx, MkDocs with mermaid plugin

Example Markdown Integration

# Database Schema

Our e-commerce system uses the following schema:

```mermaid
erDiagram
    USER ||--o{ ORDER : "places"
    ORDER ||--o{ ORDER_ITEM : "contains"
```

Output Checklist

Before finalizing an ERD:

• All entities in UPPERCASE
• All fields in snake_case
• Primary keys marked with PK
• Foreign keys marked with FK
• Unique constraints marked with UK
• Relationships have cardinality symbols
• Relationships have descriptive labels
• Junction tables for many-to-many relationships
• Timestamps (created_at, updated_at) on all entities
• No plural entity names
• Diagram renders correctly in Mermaid Live Editor

Summary

Key Principles:

1. Entities = UPPERCASE singular nouns
2. Fields = snake_case with type and constraints
3. Relationships = correct cardinality + descriptive label
4. Many-to-many = always use junction table
5. ERDs are living documents - update with schema changes