ER Diagram Best Practices for Database Design

A well-designed ER diagram is the blueprint for a clean, scalable database. Poor naming, missing relationships, and unclear cardinality lead to schemas that are painful to query and expensive to refactor. This guide covers practical best practices with Mermaid.js erDiagram examples you can use as starting templates.

Naming Conventions

Consistent naming is the single highest-impact thing you can do for your schema. Pick a convention and enforce it everywhere.

Tables

Plural nouns — users, orders, order_items
snake_case — avoids quoting issues across databases
No prefixes — skip tbl_ or t_; they add noise without value
Join tables — combine both table names: user_roles, project_members

Columns

snake_case — created_at, user_id, not createdAt or UserID
Primary keys — id (simple) or user_id (if you need to avoid ambiguity in joins)
Foreign keys — <referenced_table_singular>_id: user_id, order_id
Booleans — prefix with is_ or has_: is_active, has_paid
Timestamps — suffix with _at: created_at, deleted_at

erDiagram
    users {
        uuid id PK
        string email
        string display_name
        bool is_active
        timestamp created_at
        timestamp updated_at
    }
    subscriptions {
        uuid id PK
        uuid user_id FK
        string plan
        string status
        timestamp starts_at
        timestamp ends_at
    }
    users ||--o{ subscriptions : has

Relationship Types

Getting cardinality right is critical. The three core patterns:

One-to-One (1:1)

Each record in table A maps to exactly one record in table B. Common for splitting large tables or storing optional extended data.

erDiagram
    users {
        uuid id PK
        string email
    }
    user_profiles {
        uuid id PK
        uuid user_id FK
        string bio
        string avatar_url
    }
    users ||--|| user_profiles : has

When to use: Separating frequently-queried columns from rarely-accessed ones. Keeping the core table lean.

One-to-Many (1:N)

The most common relationship. One user has many orders. One project has many tasks. The foreign key lives on the "many" side.

erDiagram
    projects {
        uuid id PK
        string name
        uuid owner_id FK
    }
    tasks {
        uuid id PK
        uuid project_id FK
        string title
        string status
        int priority
    }
    projects ||--o{ tasks : contains

Many-to-Many (M:N)

Requires a join table (also called a junction or bridge table). A user can have many roles; a role can belong to many users.

erDiagram
    users {
        uuid id PK
        string email
    }
    roles {
        uuid id PK
        string name
    }
    user_roles {
        uuid id PK
        uuid user_id FK
        uuid role_id FK
        timestamp assigned_at
    }
    users ||--o{ user_roles : has
    roles ||--o{ user_roles : assigned_to

Tip: Join tables often accumulate their own columns over time (assigned_at,assigned_by, expires_at). Give them a proper name, not just users_roles.

Normalization: How Far to Go

Normalization eliminates data duplication. In practice, most applications benefit from 3NF (Third Normal Form) without going further.

First Normal Form (1NF)

Every column holds a single value. No arrays, no comma-separated lists, no JSON blobs for structured data you need to query.

Bad: tags: "frontend,react,typescript"
Good: Separate tags table with a join table

Second Normal Form (2NF)

Every non-key column depends on the entire primary key. In a join table with a composite key, don't store data that only depends on one half of the key.

Third Normal Form (3NF)

No transitive dependencies. If column C depends on column B which depends on the primary key, pull C into its own table.

Bad: orders table with customer_name and customer_email
Good: orders.customer_id → customers table with name and email

When to Denormalize

Denormalization trades storage for query performance. It's acceptable when:

Read-heavy workloads need to avoid expensive joins
Analytics/reporting tables that are populated by ETL
Caching frequently-computed aggregates (e.g., orders.total_items)

Always normalize first, then denormalize specific columns with a clear reason.

Complete Example: E-Commerce Schema

erDiagram
    customers {
        uuid id PK
        string email
        string name
        timestamp created_at
    }
    products {
        uuid id PK
        string name
        text description
        decimal price
        int stock_count
    }
    orders {
        uuid id PK
        uuid customer_id FK
        decimal subtotal
        decimal tax
        decimal total
        string status
        timestamp placed_at
    }
    order_items {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
    }
    addresses {
        uuid id PK
        uuid customer_id FK
        string line1
        string city
        string postal_code
        string country
    }
    customers ||--o{ orders : places
    customers ||--o{ addresses : has
    orders ||--|{ order_items : contains
    products ||--o{ order_items : "included in"

Common Mistakes

Storing calculated values without a source of truth — if you store total, also store the inputs (quantity, unit_price) so the total can be recomputed
Missing timestamps — every table should have created_at; most should have updated_at
Overusing ENUM — string columns with a check constraint or a reference table are easier to extend
No soft delete strategy — decide early if you'll use deleted_at timestamps or archive tables
Skipping indexes on foreign keys — most databases don't auto-index FKs; missing indexes cause slow joins

Converting Existing ER Diagrams

If you have ER diagrams as images — from a whiteboard session, a database tool export, or a PDF — you can convert them into editable Mermaid code with AI. See the conversion guide for a step-by-step walkthrough.

Try It Yourself

Upload a screenshot of your existing database diagram and get clean, editable Mermaid erDiagram code in seconds. Try ImageToMermaid free.