MySQL & Database Design

Entity-Relationship Diagrams (ERD)

13 min Lesson 2 of 40

Entity-Relationship Diagrams (ERD)

Entity-Relationship Diagrams are the visual blueprint of your database. In this lesson, we'll learn how to create ERDs that clearly communicate your database structure to both technical and non-technical stakeholders.

Understanding Entities and Attributes

An entity is a thing or object in the real world that can be distinguished from other objects. Each entity has attributes that describe its properties.

Entity: CUSTOMER
Attributes:
- customer_id (Primary Key)
- first_name
- last_name
- email
- phone
- created_at

Entity: ORDER
Attributes:
- order_id (Primary Key)
- customer_id (Foreign Key)
- order_date
- total_amount
- status

Key Concept: An entity typically becomes a table in your database, and each attribute becomes a column in that table.

Relationships and Cardinality

Relationships describe how entities are connected to each other. Cardinality defines the numerical relationship between entity instances.

Relationship Types:

1. One-to-One (1:1)
   Example: Person - Passport
   One person has one passport, one passport belongs to one person

2. One-to-Many (1:N)
   Example: Customer - Orders
   One customer can place many orders, one order belongs to one customer

3. Many-to-Many (M:N)
   Example: Students - Courses
   One student can enroll in many courses, one course has many students

One-to-One Relationships (1:1)

One-to-one relationships are less common but important when you need to separate data for security, performance, or logical reasons.

Example: User and UserProfile

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255),
    created_at DATETIME
);

CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    user_id INT UNIQUE,
    bio TEXT,
    avatar_url VARCHAR(255),
    birth_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Why separate?
- Keep authentication table lean and fast
- Profile data accessed less frequently
- Easier to manage permissions

One-to-Many Relationships (1:N)

This is the most common relationship type. The "many" side contains a foreign key referencing the "one" side.

Example: Author and Books

CREATE TABLE authors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    author_id INT,
    published_year INT,
    isbn VARCHAR(13),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

Relationship:
- One author writes many books
- Each book has one author (simplified example)
- Foreign key (author_id) goes on the "many" side (books)

Memory Trick: The foreign key always goes on the "many" side of a one-to-many relationship.

Many-to-Many Relationships (M:N)

Many-to-many relationships require a junction table (also called bridge table or associative table) to connect the two entities.

Example: Students and Courses

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    code VARCHAR(10)
);

CREATE TABLE enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    UNIQUE KEY unique_enrollment (student_id, course_id)
);

Relationship:
- One student enrolls in many courses
- One course has many students
- Junction table stores the relationship plus additional data

Important: The junction table often includes additional attributes like enrollment_date, grade, or status that describe the relationship itself.

Primary Keys and Foreign Keys

Keys are fundamental to relational database design and relationships.

Primary Key (PK):
- Uniquely identifies each row in a table
- Cannot be NULL
- Must be unique
- Usually an auto-incrementing integer
- Example: customer_id, order_id, product_id

Foreign Key (FK):
- Creates a link between two tables
- References a primary key in another table
- Enforces referential integrity
- Can be NULL (optional relationship)
- Example: customer_id in orders table

ERD Notation - Crow's Foot

Crow's Foot is the most popular ERD notation. Here's how to read it:

Crow's Foot Symbols:

│  = One (exactly one)
○─ = Zero or one (optional)
├─ = One or many
○< = Zero or many

Reading Examples:

CUSTOMER ├────○< ORDER
"One customer places zero or many orders"

EMPLOYEE │─────│ COMPANY_CAR
"One employee has exactly one company car"

BOOK ○<────├─ AUTHOR
"Zero or many books written by one or many authors"

Reading Tip: Always read ERD relationships from left to right: "One [left entity] has [cardinality] [right entity]".

Creating ERDs for Real-World Scenarios

Let's design a complete ERD for an e-commerce system:

Entities and Relationships:

CUSTOMER
- customer_id (PK)
- name, email, phone

ORDER
- order_id (PK)
- customer_id (FK)
- order_date, status, total

ORDER_ITEM (Junction table)
- id (PK)
- order_id (FK)
- product_id (FK)
- quantity, price

PRODUCT
- product_id (PK)
- category_id (FK)
- name, description, price, stock

CATEGORY
- category_id (PK)
- name, description

Relationships:
CUSTOMER ├────○< ORDER (one customer, many orders)
ORDER ├────├─ ORDER_ITEM (one order, many items)
PRODUCT ○<────├─ ORDER_ITEM (many products, many orders - M:N)
CATEGORY ├────○< PRODUCT (one category, many products)

Advanced ERD Concepts

Composite Keys:
Primary key made of multiple columns
Example: (student_id, course_id) in enrollments

Weak Entities:
Entity that depends on another entity for existence
Example: ORDER_ITEM depends on ORDER

Identifying Relationships:
Child cannot exist without parent
Example: Comment cannot exist without Post

Non-Identifying Relationships:
Child can exist independently
Example: Order can exist without immediately assigning a Courier

ERD Best Practices

Use clear entity names: Singular nouns (User, Order, Product)
Name relationships: Describe the action (places, contains, belongs_to)
Show cardinality: Always indicate one-to-one, one-to-many, etc.
Include primary keys: Mark them clearly (PK)
Show foreign keys: Indicate with (FK) notation
Keep it simple: Start with main entities, add details later
Use consistent notation: Stick to one notation style (Crow's Foot recommended)

Common Mistake: Don't create many-to-many relationships without a junction table. They cannot be directly implemented in relational databases.

ERD Tools

Popular tools for creating professional ERDs:

Free Tools:
- draw.io (diagrams.net) - Web-based, free
- MySQL Workbench - Official MySQL tool
- DBeaver - Free database tool with ERD
- Lucidchart - Free tier available

Paid Tools:
- dbdiagram.io - Simple online tool
- Vertabelo - Collaborative design
- ERDPlus - Academic-friendly
- Enterprise Architect - Full UML suite

From ERD to SQL

Converting an ERD to SQL tables:

ERD Design:
AUTHOR ├────○< BOOK
(One author writes many books)

SQL Implementation:
CREATE TABLE authors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    published_year INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Key Points:
- Each entity becomes a table
- Attributes become columns
- Primary keys created first
- Foreign keys reference primary keys
- Constraints enforce relationships

Practice Exercise:

Design an ERD for a Hospital Management System with these requirements:

Doctors treat multiple patients
Patients can be treated by multiple doctors
Each appointment links a doctor and patient with a date/time
Patients have a medical history
Doctors belong to departments

Solution Outline:

Entities:
- DOCTOR (id, name, specialization, department_id)
- PATIENT (id, name, dob, phone, address)
- APPOINTMENT (id, doctor_id, patient_id, date_time, status)
- MEDICAL_HISTORY (id, patient_id, condition, diagnosis_date)
- DEPARTMENT (id, name, location)

Relationships:
- DEPARTMENT ├────○< DOCTOR (1:N)
- DOCTOR ○<────├─ APPOINTMENT ├────>○ PATIENT (M:N)
- PATIENT ├────○< MEDICAL_HISTORY (1:N)

Summary

In this lesson, you learned:

Entities represent things or objects with attributes describing them
Relationships connect entities with cardinality (1:1, 1:N, M:N)
Primary keys uniquely identify rows; foreign keys create relationships
One-to-many is most common; many-to-many needs a junction table
Crow's Foot notation is industry-standard for ERDs
ERDs are converted to SQL tables with constraints

Next Up: In the next lesson, we'll learn about normalization and the First and Second Normal Forms (1NF & 2NF)!