Entity-Relationship Diagrams (ERDs)

Before writing a single CREATE TABLE statement, experienced developers draw a picture. That picture is an Entity-Relationship Diagram, and it is the blueprint that translates business requirements into a database structure. An ERD shows you which tables your database needs, what columns each table contains, and how the tables connect to each other, all in a visual format that is far easier to discuss, review, and iterate on than raw SQL.

This ER diagram tutorial teaches you how to read ERDs so you can understand existing database designs, how to create your own ERDs from scratch when starting a new project, and which tools make the process fast and collaborative. Whether you are onboarding onto a team with an established schema or designing a database for a new application, ERDs are the shared language between developers, database administrators, and business stakeholders.

What Is an Entity-Relationship Diagram?

An ERD is a visual representation of a database's structure. It was introduced by Peter Chen in 1976 and has become the standard way to model relational databases before implementation.

An ERD shows three things:

1. Entities: The "things" your database stores data about (customers, products, orders). Each entity becomes a table.
2. Attributes: The properties of each entity (customer name, product price, order date). Each attribute becomes a column.
3. Relationships: How entities connect to each other (a customer places orders, an order contains products). Each relationship becomes a foreign key.

Think of it like an architectural floor plan. You would never build a house by laying bricks randomly. You draw the plan first, review it, adjust it, and then build. ERDs serve the same purpose for databases.

The Building Blocks of an ERD

Entities (Tables)

An entity is represented as a rectangle (or box) with the entity name at the top. In most modern ERD notations, the box also lists the entity's attributes:

┌──────────────────┐
│    customers     │
├──────────────────┤
│ PK customer_id   │
│    first_name    │
│    last_name     │
│    email         │
│    city          │
└──────────────────┘

The entity name is typically a plural noun that describes what the table stores: customers, products, orders.

Attributes (Columns)

Attributes are listed inside the entity box. Important attributes are marked with labels:

Label	Meaning	Example
PK	Primary Key	customer_id
FK	Foreign Key	department_id in an employees table
UK	Unique Key	email
NN	Not Null	Required fields

┌──────────────────────┐
│      products        │
├──────────────────────┤
│ PK product_id        │
│ NN product_name      │
│    description       │
│ NN price             │
│ FK category_id       │
│ NN is_active         │
└──────────────────────┘

At a glance, you can tell that product_id is the primary key, product_name and price are required, description is optional, and category_id references another table.

Relationships (Connections)

Relationships are drawn as lines connecting two entities. The line shows that rows in one table are related to rows in another, and special symbols at each end indicate how many rows can participate on each side.

┌────────────┐          ┌────────────┐
│ customers  │──────────│  orders    │
└────────────┘          └────────────┘

The style of the line endings communicates the cardinality of the relationship: one-to-one, one-to-many, or many-to-many.

Understanding Cardinality

Cardinality defines how many instances of one entity can be associated with instances of another. This is the most important concept to master when reading ERDs.

One-to-Many (1:N)

The most common relationship. One record in table A can relate to many records in table B, but each record in table B relates to only one record in table A.

Example: One customer can place many orders, but each order belongs to exactly one customer.

┌────────────┐       ┌────────────┐
│ customers  │1─────N│  orders    │
└────────────┘       └────────────┘

In the database, this is implemented by placing a foreign key in the "many" side table:

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),  -- FK on the "many" side
    order_date  DATE
);

Other examples of 1:N relationships:

• One department has many employees
• One category contains many products
• One author writes many books
• One country has many cities

One-to-One (1:1)

Each record in table A relates to at most one record in table B, and vice versa. These are relatively rare and usually indicate that the two tables could be merged, unless there is a specific reason to keep them separate (security, performance, optional data).

Example: Each employee has one badge, and each badge belongs to one employee.

┌────────────┐       ┌────────────┐
│ employees  │1─────1│  badges    │
└────────────┘       └────────────┘

CREATE TABLE badges (
    badge_id    INT PRIMARY KEY,
    employee_id INT UNIQUE REFERENCES employees(employee_id),  -- UNIQUE enforces 1:1
    badge_code  VARCHAR(20)
);

The UNIQUE constraint on the foreign key ensures that each employee can have at most one badge.

Common reasons for 1:1 tables:

• Separating sensitive data (user credentials separate from profile information)
• Isolating large or rarely accessed columns (storing BLOBs in a separate table)
• Optional extensions (not every user has a billing profile)

Many-to-Many (M:N)

Each record in table A can relate to many records in table B, and vice versa. Relational databases cannot represent this directly. You need a junction table (also called a bridge table, linking table, or associative table) that sits between the two entities.

Example: Students enroll in courses. One student can take many courses, and one course can have many students.

┌────────────┐       ┌──────────────┐       ┌────────────┐
│  students  │1─────N│ enrollments  │N─────1│  courses   │
└────────────┘       └──────────────┘       └────────────┘

The junction table enrollments turns one M:N relationship into two 1:N relationships:

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id  INT REFERENCES courses(course_id),
    enrolled_at DATE DEFAULT CURRENT_DATE,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Other examples of M:N relationships:

• Products and orders (one order has many products, one product appears in many orders)
• Actors and movies
• Users and roles
• Tags and articles

tip

Junction tables often carry their own attributes beyond the two foreign keys. An enrollments table might include grade and enrolled_at. An order_items table includes quantity and unit_price. These attributes belong to the relationship, not to either entity individually.

ERD Notation Styles

Several notation styles exist for drawing ERDs. You will encounter different ones depending on your tools and team. The three most common are:

Crow's Foot Notation (Most Popular)

Uses graphical symbols at line endings to show cardinality. This is the most widely used notation in industry and the default in most modern tools.

One:        ──────||──
Many:       ──────<|──  or  ────────╢<
Optional:   ──────O|──

The symbols combine to express the full relationship:

Symbol at Line End	Meaning
|| (single vertical line)	Exactly one
|O (line + circle)	Zero or one (optional)
>| or crow's foot	One or many
>O (crow's foot + circle)	Zero or many

Reading example:

┌────────────┐                ┌────────────┐
│ customers  │-||---------O<|-│  orders    │
└────────────┘                └────────────┘

Reading left to right: One customer has zero or many orders. Reading right to left: Each order belongs to exactly one customer.

Chen Notation (Academic)

The original notation from Peter Chen's 1976 paper. Uses diamonds for relationships and ovals for attributes. It is common in textbooks and university courses but rarely used in professional tools.

┌──────────┐     ┌──────────┐     ┌──────────┐
│ Customer ├──┬──┤  places  ├──┬──┤  Order   │
└──────────┘  1  └──────────┘  N  └──────────┘

UML Class Diagram Style

Uses numbers or * at line ends:

┌────────────┐  1         *  ┌────────────┐
│ Customer   │───────────────│   Order    │
└────────────┘               └────────────┘

1 means one, * means many, 0..1 means zero or one.

Which Notation Should You Use?

For practical work, Crow's Foot notation is the industry standard. Most ERD tools default to it, most teams expect it, and most online resources use it. If you learn one notation, learn Crow's Foot.

info

The notation style does not change the underlying design. Whether you draw a crow's foot or write "1:N," the database implementation is the same. Choose whatever notation your team uses or your tool supports, and be consistent.

How to Read an ERD

Reading an ERD is a skill that develops with practice. Here is a systematic approach for interpreting any diagram you encounter.

Step 1: Identify the Entities

Look at each box. Read its name. Each box is a table in the database.

┌──────────────┐   ┌───────────────┐   ┌──────────────┐
│  customers   │   │   orders      │   │  products    │
├──────────────┤   ├───────────────┤   ├──────────────┤
│ PK id        │   │ PK id         │   │ PK id        │
│    name      │   │ FK customer_id│   │    name      │
│    email     │   │    order_date │   │    price     │
└──────────────┘   │    status     │   └──────────────┘
                   └───────────────┘

Three entities: customers, orders, products.

Step 2: Read the Attributes

Look inside each box. Identify primary keys (PK), foreign keys (FK), and note which columns are required versus optional.

Step 3: Follow the Relationship Lines

For each line connecting two entities:

1. Read from left to right, then right to left, forming two sentences.
2. Check the cardinality symbols at each end to determine "how many."

customers -||--------o<- orders

• Left to right: "One customer has zero or many orders."
• Right to left: "Each order belongs to exactly one customer."

Step 4: Identify Junction Tables

Look for tables that have two or more foreign keys and exist primarily to connect other tables. These represent many-to-many relationships.

┌────────────┐          ┌──────────────┐          ┌────────────┐
│  orders    │-|-----<|-│ order_items  │-|>-----|-│ products   │
└────────────┘          ├──────────────┤          └────────────┘
                        │ FK order_id  │
                        │ FK product_id│
                        │    quantity  │
                        │    unit_price│
                        └──────────────┘

order_items is a junction table. It tells you that orders and products have a many-to-many relationship: one order contains many products, and one product appears in many orders.

Practice: Reading a Complete ERD

┌──────────────┐          ┌──────────────┐
│ departments  │          │  employees   │
├──────────────┤          ├──────────────┤
│ PK dept_id   │-|------<-│ PK emp_id    │
│    dept_name │          │    name      │
│    location  │          │ FK dept_id   │
└──────────────┘          │ FK manager_id│──┐
                          └──────────────┘  │
                                 │          │
                                 └──────────┘
                              (self-reference)

Reading this diagram:

1. departments to employees: One department has many employees. Each employee belongs to one department.
2. employees to employees (self-reference): One employee (as manager) manages many other employees. Each employee has zero or one manager. The manager_id foreign key points back to the same table.

How to Design an ERD

Designing an ERD is the process of translating business requirements into a database structure. Here is a repeatable methodology.

Step 1: List the Nouns

Read through your requirements document, user stories, or feature descriptions. Every significant noun is a candidate entity.

Requirement: "ShopSmart allows customers to place orders for products. Products are organized into categories. Each order can contain multiple products with varying quantities."

Nouns identified: customers, orders, products, categories, quantities

Step 2: Determine Entities vs Attributes

Not every noun becomes its own table. Ask: "Does this noun have its own properties that I need to track independently?"

Noun	Entity or Attribute?	Reasoning
Customer	Entity	Has name, email, address, many properties
Order	Entity	Has date, status, belongs to a customer
Product	Entity	Has name, price, description
Category	Entity	Has a name, products belong to it
Quantity	Attribute	It is a property of the relationship between orders and products

Step 3: Define Attributes for Each Entity

List the columns each entity needs. Mark primary keys, required fields, and data types:

customers:   customer_id (PK), first_name (NN), last_name (NN), email (UK, NN), city
products:    product_id (PK), product_name (NN), price (NN), category_id (FK)
categories:  category_id (PK), category_name (NN, UK)
orders:      order_id (PK), customer_id (FK, NN), order_date (NN), status (NN)

Step 4: Identify Relationships

For each pair of entities, ask: "Are these related? If so, how?"

Customers and Orders: A customer places orders. One customer can have many orders. Each order belongs to one customer. Relationship: 1:N.

Categories and Products: A category contains products. One category has many products. Each product belongs to one category. Relationship: 1:N.

Orders and Products: An order contains products, and a product can appear in many orders. Relationship: M:N. This requires a junction table: order_items.

Step 5: Draw the Diagram

Arrange entities on the canvas. Place related entities near each other. Draw relationship lines with correct cardinality symbols.

┌──────────────────┐          ┌──────────────────┐
│   categories     │          │    customers     │
├──────────────────┤          ├──────────────────┤
│ PK category_id   │          │ PK customer_id   │
│ NN category_name │          │ NN first_name    │
└────────┬─────────┘          │ NN last_name     │
         │                    │ UK email         │
         │1                   │    city          │
         │                    └────────┬─────────┘
         │N                            │
┌────────┴─────────┐                   │1
│    products      │                   │
├──────────────────┤                   │N
│ PK product_id    │          ┌────────┴─────────┐
│ NN product_name  │          │     orders       │
│ NN price         │          ├──────────────────┤
│ FK category_id   │          │ PK order_id      │
│ NN is_active     │          │ FK customer_id   │
└────────┬─────────┘          │ NN order_date    │
         │                    │ NN status        │
         │1                   └────────┬─────────┘
         │                             │
         │N                            │1
    ┌────┴─────────────────────────────┴──┐
    │          order_items                │
    ├─────────────────────────────────────┤
    │ PK order_item_id                    │
    │ FK order_id                         │
    │ FK product_id                       │
    │ NN quantity                         │
    │ NN unit_price                       │
    └─────────────────────────────────────┘

Step 6: Review and Validate

Ask these questions about your completed ERD:

• Can I represent every piece of data from the requirements? If a requirement mentions "customer phone numbers" and no table stores them, something is missing.
• Is every entity in at least 3NF? Check for redundancy, partial dependencies, and transitive dependencies.
• Are foreign keys placed on the correct side? Foreign keys go on the "many" side of a 1:N relationship.
• Are M:N relationships resolved? Every many-to-many must have a junction table.
• Can I answer the key business queries? Mentally trace the joins needed for important queries like "show all items in an order" or "list customers who bought a specific product."

tip

Walk through user stories with your ERD. For each user story ("As a customer, I want to view my order history"), trace the path through your diagram:

1. Start at customers (find the customer)
2. Follow the relationship to orders (get their orders)
3. Follow to order_items (get the line items)
4. Follow to products (get product names)

If the path is clear and every needed column exists, your design supports the use case. If you hit a dead end, your schema is missing something.

ERD Design Patterns

Certain patterns appear repeatedly across different domains. Recognizing them accelerates your design work.

Hierarchical (Self-Referencing)

An entity that references itself, forming a tree structure:

┌──────────────────┐
│   categories     │
├──────────────────┤
│ PK category_id   │
│    category_name │──┐
│ FK parent_id     │──┘ (self-reference)
└──────────────────┘

Used for: organizational charts, comment threads, file systems, nested categories.

Polymorphic Association

One table relates to multiple other tables through a type discriminator:

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│   posts      │  │  photos      │  │   videos     │
└──────┬───────┘  └──────┬───────┘  └──────┬───────┘
       │                 │                 │
       └─────────────────┼─────────────────┘
                         │
                ┌────────┴───────┐
                │   comments     │
                ├────────────────┤
                │ PK comment_id  │
                │    body        │
                │ FK entity_id   │
                │    entity_type │ ← 'post', 'photo', or 'video'
                └────────────────┘

warning

Polymorphic associations cannot be enforced with standard foreign keys because the target table varies. They sacrifice referential integrity for flexibility. Consider using separate junction tables (post_comments, photo_comments) if data integrity is critical.

Audit Trail

A pattern where every change to important tables is logged:

┌──────────────┐         ┌───────────────────┐
│   orders     │─|─────<─│  order_audit_log  │
├──────────────┤         ├───────────────────┤
│ PK order_id  │         │ PK log_id         │
│    status    │         │ FK order_id       │
│    ...       │         │    old_status     │
└──────────────┘         │    new_status     │
                         │    changed_by     │
                         │    changed_at     │
                         └───────────────────┘

Tools for Creating ERDs

dbdiagram.io

Best for: Quick, code-driven ERD creation with instant visual output.

dbdiagram.io uses a simple markup language called DBML (Database Markup Language) that lets you type table definitions and see the diagram update in real time.

Table customers {
  customer_id int [pk, increment]
  first_name varchar(50) [not null]
  last_name varchar(50) [not null]
  email varchar(254) [unique, not null]
  city varchar(50)
}

Table orders {
  order_id int [pk, increment]
  customer_id int [not null, ref: > customers.customer_id]
  order_date date [not null]
  status varchar(20) [not null, default: 'pending']
}

Table products {
  product_id int [pk, increment]
  product_name varchar(200) [not null]
  price decimal(10,2) [not null]
  category_id int [not null, ref: > categories.category_id]
}

Table categories {
  category_id int [pk, increment]
  category_name varchar(100) [not null]
  parent_id int [ref: > categories.category_id]
}

Table order_items {
  order_item_id int [pk, increment]
  order_id int [not null, ref: > orders.order_id]
  product_id int [not null, ref: > products.product_id]
  quantity int [not null]
  unit_price decimal(10,2) [not null]
}

Pros:

• Free tier available
• Code-based input feels natural for developers
• Exports to SQL (PostgreSQL, MySQL, SQL Server)
• Shareable via URL
• Clean, professional diagrams

Cons:

• Limited layout control in free tier
• No direct database connection for reverse engineering

Lucidchart

Best for: Team collaboration and presentation-quality diagrams.

Lucidchart is a general-purpose diagramming tool with dedicated ERD shapes and templates. It is drag-and-drop rather than code-based.

Pros:

• Real-time collaboration (like Google Docs)
• Professional visual quality for stakeholder presentations
• Integration with Confluence, Google Workspace, Slack
• ERD templates to start quickly
• Import from SQL or database connections (paid plans)

Cons:

• Paid for full features
• Drag-and-drop can be slower than code-based tools for large schemas
• Not specifically built for databases

draw.io (diagrams.net)

Best for: Free, offline-capable diagramming with maximum flexibility.

draw.io (now rebranded as diagrams.net) is completely free and runs in the browser or as a desktop app. It has ERD shapes built in.

Pros:

• Completely free, no account required
• Works offline (desktop app and VS Code extension)
• Saves to Google Drive, OneDrive, GitHub, or local files
• Full control over layout and styling
• UML and ERD shape libraries included

Cons:

• Manual drawing (no code-to-diagram conversion)
• No SQL export
• Less polished than Lucidchart for presentations
• No database reverse-engineering

Database-Specific Tools

Many database tools can generate ERDs from existing databases (reverse engineering):

Tool	Database Support	Free?
pgAdmin (ERD tool)	PostgreSQL	Yes
MySQL Workbench	MySQL	Yes
DBeaver (ER Diagrams)	All major databases	Community edition free
DataGrip (JetBrains)	All major databases	Paid (free for students)
SQL Server Management Studio	SQL Server	Free

Reverse engineering is invaluable when you join a project with an existing database and need to understand its structure quickly.

Tool Comparison Summary

Need	Best Tool
Quick schema design for a new project	dbdiagram.io
Team collaboration and stakeholder presentation	Lucidchart
Free, offline, maximum flexibility	draw.io
Generate ERD from an existing database	DBeaver or your database's native tool
Developer who prefers code over dragging	dbdiagram.io

tip

Start with dbdiagram.io for speed during initial design, then move to a richer tool if you need collaboration or presentation features. For existing databases, use DBeaver or your database's built-in ERD tool to reverse-engineer the schema into a diagram automatically.

From ERD to SQL: Translating the Diagram

Once your ERD is finalized, converting it to SQL is mechanical. Each element maps directly:

ERD Element	SQL Equivalent
Entity (box)	CREATE TABLE
Attribute	Column definition
PK marker	PRIMARY KEY
NN marker	NOT NULL
UK marker	UNIQUE
Relationship line	FOREIGN KEY ... REFERENCES
1:N line ending on the "N" side	FK column in the "many" table
M:N relationship	Junction table with two FKs
Self-reference arrow	FK referencing the same table

Example translation:

ERD entity:

┌──────────────────┐
│    products      │
├──────────────────┤
│ PK product_id    │
│ NN product_name  │
│    description   │
│ NN price         │
│ FK category_id   │
└──────────────────┘

SQL:

CREATE TABLE products (
    product_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name  VARCHAR(200) NOT NULL,
    description   TEXT,
    price         DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    category_id   INT NOT NULL REFERENCES categories(category_id)
);

Notice that the ERD does not typically show CHECK constraints or DEFAULT values. Those are implementation details added during the SQL writing phase. The ERD focuses on structure and relationships.

Common ERD Mistakes

Mistake 1: Missing Junction Tables for M:N Relationships

Drawing a direct line between two entities for a many-to-many relationship is incorrect. Relational databases cannot implement M:N directly.

Wrong:

┌──────────┐           ┌──────────┐
│ students │N─────────N│ courses  │
└──────────┘           └──────────┘

Correct:

┌──────────┐         ┌──────────────┐         ┌──────────┐
│ students │─|─────<─│ enrollments  │─>─────|─│ courses  │
└──────────┘         └──────────────┘         └──────────┘

Mistake 2: Foreign Keys on the Wrong Side

In a 1:N relationship, the foreign key always goes on the "many" side.

Wrong: Putting an order_id column in the customers table (implies a customer can have only one order).

Correct: Putting a customer_id column in the orders table (each order points to its customer; one customer can have many orders).

Mistake 3: Over-Engineering with Too Many Entities

Not every noun needs its own table. If "status" only has four possible values and no additional attributes, it can be a VARCHAR column with a CHECK constraint rather than a separate statuses table with a foreign key.

Over-engineered:

┌──────────┐         ┌──────────────┐
│  orders  │─|─────<─│  statuses    │
└──────────┘         ├──────────────┤
                     │ PK status_id │
                     │    name      │  ← only 4 rows, no other attributes
                     └──────────────┘

Simpler:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status   VARCHAR(20) CHECK (status IN ('pending','processing','shipped','delivered'))
);

info

A separate lookup table does make sense when the list of values is long, changes frequently, or has additional attributes (like a display label, sort order, or icon). For short, stable lists, a CHECK constraint is simpler.

Mistake 4: No Primary Keys

Every entity must have a primary key. An ERD without PK markers is incomplete and will lead to tables without unique row identification.

Mistake 5: Ignoring Cardinality

Drawing plain lines without cardinality indicators leaves the relationship ambiguous. Always specify whether each side is "one" or "many" and whether the relationship is optional or required.

Summary

Entity-Relationship Diagrams are the visual blueprints that bridge the gap between business requirements and database implementation. They show entities (tables), attributes (columns), and relationships (foreign keys) in a format that is easy to discuss, review, and iterate on before writing any SQL.

Key takeaways:

• Reading an ERD requires understanding three things: entities are boxes (tables), attributes are listed inside (columns), and lines between boxes represent relationships (foreign keys). Cardinality symbols at line endings tell you whether the relationship is one-to-one, one-to-many, or many-to-many.
• Crow's Foot notation is the industry standard. Learn to read the symbols: a single line means "one," a crow's foot means "many," a circle means "zero (optional)."
• Designing an ERD follows a systematic process: extract nouns from requirements, determine which are entities vs attributes, define columns for each entity, identify relationships and their cardinality, resolve M:N relationships with junction tables, and validate the design against user stories.
• Foreign keys go on the "many" side of a one-to-many relationship. Many-to-many relationships always require a junction table.
• Tools range from code-driven (dbdiagram.io) to drag-and-drop (Lucidchart, draw.io) to reverse-engineering from existing databases (DBeaver, pgAdmin, MySQL Workbench). Choose based on your workflow: code-based for speed, visual tools for collaboration, database tools for existing schemas.
• Translate ERDs to SQL mechanically: each entity becomes a CREATE TABLE, each PK becomes a PRIMARY KEY, each FK becomes a FOREIGN KEY ... REFERENCES, and each M:N relationship becomes a junction table.
• Common mistakes include missing junction tables for M:N relationships, placing foreign keys on the wrong side, over-engineering with unnecessary lookup tables, and omitting cardinality symbols.

Draw the picture before writing the code. An hour spent on an ERD saves days of refactoring when the schema turns out to be wrong.