SQL Performance

Entity-Relationship Diagrams

Visualize your database design with entity-relationship diagrams for clearer communication and better architecture.

Entities & Attributes — Define the core objects and their properties
Relationships — Map connections between entities with cardinality rules
Visual Communication — Bridge the gap between developers, DBAs, and stakeholders A picture is worth a thousand rows — ER diagrams make database design intuitive.

What is an ER Diagram?

ER diagrams serve as the blueprint for database construction. Before writing a single CREATE TABLE statement, an ER diagram helps you think through the structure, spot design flaws, and communicate with your team.

ER Diagram Component	SQL Equivalent	Description
Entity	Table	A thing or concept that stores data
Attribute	Column	A property of an entity
Primary Key	PRIMARY KEY	Unique identifier for each row
Foreign Key	FOREIGN KEY	Column linking to another table
Relationship	JOIN condition	How entities relate to each other

Entity Types

-- Entities represented as tables
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    category TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    total DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Strong vs Weak Entities

-- Strong entity: customers
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Weak entity: customer_addresses (depends on customers)
CREATE TABLE customer_addresses (
    customer_id INTEGER NOT NULL,
    address_type TEXT NOT NULL,  -- Partial key (discriminator)
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    zip_code TEXT NOT NULL,
    PRIMARY KEY (customer_id, address_type),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

-- Weak entity: order_items (depends on orders)
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    line_number INTEGER NOT NULL,  -- Partial key
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

Relationship Types

Cardinality

Cardinality	Notation	Description	Example
One-to-One (1:1)	1 ─── 1	Each row in A maps to exactly one row in B	Person ↔ Passport
One-to-Many (1:N)	1 ─── N	Each row in A maps to many rows in B	Customer → Orders
Many-to-Many (M:N)	M ─── N	Rows in A map to many rows in B, and vice versa	Students ↔ Courses

-- One-to-One relationship
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY,
    bio TEXT,
    avatar_url TEXT,
    theme_preference TEXT DEFAULT 'light',
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- One-to-Many relationship
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    category_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Many-to-Many relationship (junction table)
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade TEXT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

Participation

-- Total participation: every order MUST have a customer
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,  -- NOT NULL enforces total participation
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Partial participation: not every customer has a loyalty card
CREATE TABLE loyalty_cards (
    card_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL UNIQUE,
    points INTEGER DEFAULT 0,
    issued_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    -- customer_id is UNIQUE but could be nullable for partial participation
);

ER Diagram Notation Reference

Chen Notation

Symbol	Meaning
Rectangle	Entity
Diamond	Relationship
Oval	Attribute
Underlined text	Primary key
Dashed oval	Derived attribute
Double rectangle	Weak entity

Crow's Foot Notation (Industry Standard)

Symbol	Meaning
`
`	o`
`>	`
`>o`	Optional many
Single line	Total participation
Double line	Partial participation

-- Crow's Foot notation example:
-- Customer ||--|{ Order : places
-- Order ||--|{ OrderItem : contains
-- Product ||--o{ OrderItem : referenced in

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    -- ||--|{ : Mandatory Many (every order has exactly one customer)
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
    -- ||--o{ : Optional Many (product might not be in any orders yet)
);

Real-World ER Design: Hospital Management

-- Entities
CREATE TABLE patients (
    patient_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE NOT NULL,
    gender TEXT,
    phone TEXT,
    insurance_id TEXT
);

CREATE TABLE doctors (
    doctor_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    specialization TEXT NOT NULL,
    license_number TEXT NOT NULL UNIQUE
);

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    floor_number INTEGER NOT NULL
);

CREATE TABLE appointments (
    appointment_id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    doctor_id INTEGER NOT NULL,
    appointment_date TIMESTAMP NOT NULL,
    reason TEXT,
    status TEXT DEFAULT 'scheduled',
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- Many-to-Many: doctors belong to departments
CREATE TABLE doctor_departments (
    doctor_id INTEGER NOT NULL,
    department_id INTEGER NOT NULL,
    role TEXT DEFAULT 'member',
    PRIMARY KEY (doctor_id, department_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Weak entity: medical records depend on patients
CREATE TABLE medical_records (
    patient_id INTEGER NOT NULL,
    record_date TIMESTAMP NOT NULL,
    diagnosis TEXT NOT NULL,
    treatment TEXT,
    notes TEXT,
    doctor_id INTEGER NOT NULL,
    PRIMARY KEY (patient_id, record_date),
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- One-to-One: patient emergency contact
CREATE TABLE emergency_contacts (
    patient_id INTEGER PRIMARY KEY,
    contact_name TEXT NOT NULL,
    relationship TEXT NOT NULL,
    phone TEXT NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE
);

ER Design Process

Step	Action	Output
1	Identify entities	List of nouns from requirements
2	Define attributes	Properties for each entity
3	Identify relationships	Connections between entities
4	Determine cardinality	1:1, 1:N, M:N for each relationship
5	Apply normalization	Eliminate redundancy
6	Review and iterate	Validate with stakeholders

Common ER Diagram Mistakes

Mistake	Problem	Solution
No junction table for M:N	Violates 1NF, data integrity issues	Create intersection entity
Using natural keys everywhere	Keys can change, causing cascade updates	Use surrogate keys (UUID, SERIAL)
Over-normalization	Excessive JOINs degrade performance	Balance with denormalization
Missing foreign keys	No referential integrity enforcement	Always declare FOREIGN KEY constraints
Ambiguous relationships	Unclear business rules	Document cardinality and participation

-- Bad: Storing multiple values in one column (violates 1NF)
CREATE TABLE products_bad (
    product_id INTEGER PRIMARY KEY,
    tags TEXT  -- "electronics, sale, new" -- BAD!
);

-- Good: Many-to-many with junction table
CREATE TABLE tags (
    tag_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE product_tags (
    product_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE
);

Quick Reference: Relationship Patterns

Pattern	Use Case	Implementation
User ↔ Profile	One-to-one extension	Shared primary key
Customer → Orders	One-to-many ownership	Foreign key in child
Student ↔ Course	Many-to-many enrollment	Junction table
Product → Variants	One-to-many with discriminator	Composite key on child
Invoice → Line Items	Parent with weak children	Composite FK + ON DELETE CASCADE

Entity-Relationship Diagrams

Entity-Relationship Diagrams

What is an ER Diagram?

Entity Types

Strong vs Weak Entities

Relationship Types

Cardinality

Participation

ER Diagram Notation Reference

Chen Notation

Crow's Foot Notation (Industry Standard)

Real-World ER Design: Hospital Management

ER Design Process

Common ER Diagram Mistakes

Quick Reference: Relationship Patterns

Need Expert SQL Help?