Buy Me A CoffeeIf you have ever designed a database, you have probably sketched boxes and lines on a whiteboard to figure out how your tables relate to each other. That sketch has a formal name: an Entity Relationship Diagram, or ERD. ERDs are one of the oldest and most valuable tools in software engineering, and understanding them deeply will make you a better database designer, a clearer communicator, and a more effective engineer.
What Are Entity Relationship Diagrams?
An ERD is a visual representation of the data model behind a system. It shows entities (the things you store data about, like users, orders, or products), their attributes (the columns in your tables), and the relationships between them (foreign keys and join tables). ERDs answer the fundamental question of database design: "What data do we have, and how does it connect?"
A Brief History
The ERD was introduced by Peter Chen in 1976 in his seminal paper "The Entity-Relationship Model -- Toward a Unified View of Data." Chen proposed a way to describe data that was independent of any particular database management system. His notation became the foundation for conceptual data modeling and influenced every major database design methodology that followed. Today, variations like Crow's Foot notation (used by Mermaid.js) have become the industry standard because they communicate cardinality more intuitively than Chen's original diamonds and ovals.
Understanding Cardinality
Cardinality defines how many instances of one entity can be associated with instances of another. It is the most important concept in ERD notation and the one that developers most commonly get wrong. Here is a reference table for Mermaid's Crow's Foot notation:
| Mermaid Syntax | Meaning | Example |
|---|---|---|
||--|| |
Exactly one to exactly one | User has one Profile |
||--o| |
Exactly one to zero or one | Employee has optional Parking Spot |
||--o{ |
Exactly one to zero or many | Author writes zero or more Books |
||--|{ |
Exactly one to one or many | Order contains at least one Item |
}o--o{ |
Zero or many to zero or many | Students enroll in Courses |
The symbols at each end of the relationship line tell you the minimum and maximum participation. A vertical line (|) means "exactly one" or "at least one." A circle (o) means "zero" (optional). A crow's foot ({) means "many." Read each end independently: ||--o{ means "the left entity participates exactly once, the right entity participates zero or many times."
When to Use ERDs
- Before writing migrations: Sketch the ERD first to catch design issues before they become schema changes.
- During code reviews: Attach an ERD to PRs that add or modify database tables so reviewers can see the structural impact.
- In documentation: Every service that owns data should have an ERD in its README. See our guide on using diagrams for documentation.
- When debugging data issues: ERDs help you trace foreign key paths when investigating data integrity problems.
- During system design interviews: ERDs are a common ask in backend interviews; fluency here is a career advantage.
Designing a Database Schema Step by Step
Let us walk through designing an ERD for a simple blog platform. We will start with requirements, identify entities, define relationships, and refine the model.
Step 1: Identify Entities
From the requirements "users can write posts, posts can have tags, and users can comment on posts," we extract four entities: User, Post, Tag, and Comment.
Step 2: Define Relationships
- A User writes zero or many Posts (one-to-many).
- A Post has zero or many Comments (one-to-many).
- A User writes zero or many Comments (one-to-many).
- A Post has zero or many Tags, and a Tag appears on zero or many Posts (many-to-many).
Step 3: Add Attributes
For each entity, list the columns and mark primary keys (PK) and foreign keys (FK).
Step 4: Build the ERD
erDiagram
USER ||--o{ POST : writes
USER ||--o{ COMMENT : authors
POST ||--o{ COMMENT : has
POST }o--o{ TAG : "tagged with"
USER {
int id PK
string username
string email
datetime created_at
}
POST {
int id PK
int author_id FK
string title
text body
datetime published_at
}
COMMENT {
int id PK
int post_id FK
int user_id FK
text body
datetime created_at
}
TAG {
int id PK
string name
string slug
}Notice that the many-to-many relationship between Post and Tag will require a join table (POST_TAG) in the actual database. Mermaid's ERD syntax models the conceptual relationship; the physical join table is an implementation detail.
More ERD Examples
E-Commerce Schema
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ LINE_ITEM : contains
PRODUCT ||--o{ LINE_ITEM : "appears in"
PRODUCT }o--|| CATEGORY : "belongs to"
ORDER ||--o| SHIPMENT : "shipped via"
CUSTOMER {
int id PK
string name
string email
}
ORDER {
int id PK
int customer_id FK
decimal total
string status
datetime ordered_at
}
PRODUCT {
int id PK
string name
decimal price
int category_id FK
}
CATEGORY {
int id PK
string name
}
LINE_ITEM {
int id PK
int order_id FK
int product_id FK
int quantity
decimal unit_price
}
SHIPMENT {
int id PK
int order_id FK
string tracking_number
datetime shipped_at
}SaaS Multi-Tenant Schema
erDiagram
ORGANIZATION ||--|{ USER : employs
ORGANIZATION ||--o{ PROJECT : owns
PROJECT ||--o{ TASK : contains
USER ||--o{ TASK : "assigned to"
USER ||--o{ TASK : creates
ORGANIZATION {
int id PK
string name
string plan
}
USER {
int id PK
int org_id FK
string name
string role
}
PROJECT {
int id PK
int org_id FK
string name
string status
}
TASK {
int id PK
int project_id FK
int assignee_id FK
int creator_id FK
string title
string priority
}Common Mistakes
- Skipping cardinality analysis: Drawing lines without thinking carefully about minimum and maximum participation leads to schemas that allow invalid data. Ask yourself: "Can this entity exist without the related entity? Can there be zero, one, or many?"
- Modeling join tables as entities: A many-to-many relationship at the conceptual level does not need a visible join table entity in your ERD unless the join table carries its own attributes (like an enrollment date or a role).
- Overloading entities: If an entity has 20+ attributes, it is probably doing too much. Consider splitting it into related entities.
- Ignoring nullable foreign keys: Using
||(exactly one) when the relationship is actually optional (o|) misrepresents your data model and can lead to NOT NULL constraints that do not match reality. - Not labeling relationships: A line between User and Post is ambiguous. Does the user write the post, review it, or own it? Always include a relationship label.
Normalization Tips
ERDs and normalization go hand in hand. As you build your diagram, keep these guidelines in mind:
- First Normal Form (1NF): Every attribute should be atomic. If you are tempted to store a comma-separated list in a column (like tags), create a separate entity and a relationship instead.
- Second Normal Form (2NF): Every non-key attribute should depend on the entire primary key, not just part of it. If your composite key has attributes that depend on only one part, split the entity.
- Third Normal Form (3NF): No transitive dependencies. If attribute A determines B and B determines C, then C should live in a separate entity keyed by B.
Pro Tip: Do not normalize blindly. Over-normalization can hurt read performance. Use your ERD to find the right balance -- normalize for data integrity, then denormalize strategically where performance requires it. Document both the normalized ERD and the denormalized physical schema so future developers understand why they differ.
ERDs are not just academic exercises. They are the blueprint for your data layer. A well-designed ERD prevents data integrity bugs, simplifies queries, and makes your schema self-documenting. With Mermaid syntax, creating and maintaining ERDs is as fast as typing a few lines of text. Open SimpleMermaid, paste any example from this article, and start designing your own data model today.
Design Your Database Schema
Paste any ERD example above into SimpleMermaid and start modeling your data. Free, instant, no signup.
Open the Editor