Relationships

One-to-One Relationship

A one-to-one (1:1) relationship exists when each row in Table A relates to exactly one row in Table B, and vice versa. This is the simplest relationship type but is relatively rare in practice.

When to Use One-to-One

• Splitting a table for security or performance
• Optional attributes that apply to a subset of rows
• Separate storage for different types of data

Implementation

-- User profiles: one user has one profile
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar_url VARCHAR(255),
    date_of_birth DATE,
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
);

-- Insert data
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO user_profiles (user_id, bio, phone) 
VALUES (1, 'Software developer', '555-0101');

Querying One-to-One

-- Get user with profile
SELECT 
    u.username,
    u.email,
    p.bio,
    p.phone
FROM users u
JOIN user_profiles p ON u.user_id = p.user_id
WHERE u.user_id = 1;

-- Left join to get all users (even without profiles)
SELECT 
    u.username,
    COALESCE(p.bio, 'No bio') AS bio
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id;

One-to-One Design Patterns

1. Vertical Partitioning: Split wide tables for performance
2. Security: Sensitive data in separate table with restricted access
3. Extension Tables: Add optional fields without modifying main table

-- Employee with optional emergency contact
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

CREATE TABLE emergency_contacts (
    emp_id INT PRIMARY KEY,
    contact_name VARCHAR(100) NOT NULL,
    contact_phone VARCHAR(20) NOT NULL,
    relationship VARCHAR(50),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

One-to-one relationships help organize data logically while maintaining clear boundaries between different types of information.

One-to-Many Relationship

A one-to-many (1:N) relationship is the most common relationship type. One row in Table A can relate to multiple rows in Table B, but each row in Table B relates to only one row in Table A.

Examples

• One department has many employees
• One customer has many orders
• One author has many books

Implementation

-- Department has many employees
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT NOT NULL,
    salary DECIMAL(10,2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Customer has many orders
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

Querying One-to-Many

-- Get all employees in each department
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- Get all orders for a customer
SELECT 
    c.name AS customer,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 1
ORDER BY o.order_date DESC;

-- Find customers with no orders
SELECT 
    c.name,
    c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Foreign Key Placement

In one-to-many relationships, the foreign key always goes in the many side table. The one side contains the primary key that is referenced.

departments (1) ----< (N) employees
     dept_id  <---  dept_id (FK)

customers (1) ----< (N) orders
   customer_id  <---  customer_id (FK)

Deleting Records

-- With ON DELETE CASCADE: deleting department deletes all employees
-- With ON DELETE RESTRICT: cannot delete department with employees
-- With ON DELETE SET NULL: employee.dept_id set to NULL

-- Example: Safe deletion
DELETE FROM departments WHERE dept_id = 5;
-- If ON DELETE RESTRICT and employees exist, this fails

One-to-many relationships are fundamental to relational database design and model most real-world hierarchical structures.

Many-to-Many Relationship

A many-to-many (M:N) relationship occurs when rows in Table A can relate to multiple rows in Table B, and vice versa. This requires a junction table (also called bridge, link, or association table) to implement.

Examples

• Students enroll in many courses; courses have many students
• Products belong to many categories; categories contain many products
• Tags are applied to many articles; articles have many tags

Implementation with Junction Table

-- Students and Courses (many-to-many)
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credits INT NOT NULL
);

-- Junction table
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    grade VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE (student_id, course_id)  -- Prevent duplicate enrollments
);

Another Example: Products and Tags

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(50) NOT NULL UNIQUE
);

-- Junction table
CREATE TABLE product_tags (
    product_id INT,
    tag_id INT,
    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
);

Querying Many-to-Many

-- Get all courses for a student
SELECT 
    c.course_name,
    c.credits,
    e.grade
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

-- Get all students in a course
SELECT 
    s.name,
    s.email,
    e.grade
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 101;

-- Count students per course
SELECT 
    c.course_name,
    COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

-- Find courses with no enrollments
SELECT c.course_name
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

Junction Table Best Practices

1. Primary Key: Use auto-incrementing ID or composite key
2. Additional Attributes: Store relationship metadata (enrollment_date, grade)
3. Unique Constraints: Prevent duplicate relationships
4. Naming: Use plural or descriptive names (enrollments, product_tags)

-- Junction table with additional attributes
CREATE TABLE book_authors (
    book_author_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    author_id INT NOT NULL,
    author_order INT DEFAULT 1,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    UNIQUE (book_id, author_id)
);

Many-to-many relationships are essential for modeling complex real-world associations. The junction table is the key to implementing them in relational databases.

Entity Relationship Diagrams (ERD)

An Entity Relationship Diagram (ERD) is a visual representation of the database structure. It shows entities (tables), their attributes (columns), and relationships between entities.

ERD Symbols

Symbol	Meaning
Rectangle	Entity (table)
Oval	Attribute (column)
Diamond	Relationship
Line	Connection
Crow's Foot	Many side
Circle	Optional (zero)

Crow's Foot Notation

One-to-One:    Entity A ||--|| Entity B
One-to-Many:   Entity A ||--|{ Entity B
Many-to-Many:  Entity A }|--|{ Entity B

Example ERD: E-Commerce

+---------------+       +---------------+
|   customers   |       |    orders     |
+---------------+       +---------------+
| customer_id (PK)|--<--| order_id (PK) |
| name          |       | customer_id(FK)|
| email         |       | order_date    |
+---------------+       | total         |
                         +---------------+
                                 |
                                 v
+---------------+       +---------------+
|   products    |       | order_items   |
+---------------+       +---------------+
| product_id(PK)|--<--| item_id (PK) |
| name          |       | order_id (FK) |
| price         |       | product_id(FK)|
+---------------+       | quantity      |
                         +---------------+

Creating an ERD in SQL

-- Tables representing an ERD
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_year INT
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    publication_year INT,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE genres (
    genre_id INT PRIMARY KEY,
    genre_name VARCHAR(50) NOT NULL
);

CREATE TABLE book_genres (
    book_id INT,
    genre_id INT,
    PRIMARY KEY (book_id, genre_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

Reading ERD Relationships

1. Identify Entities: Look for rectangles (tables)
2. Find Primary Keys: Look for underlined attributes or PK markers
3. Follow Lines: Trace connections between entities
4. Check Cardinality: Look for crow's feet or 1/N/M symbols

ERD Tools

• dbdiagram.io: Online tool for creating ERDs
• MySQL Workbench: Built-in ERD creation
• pgAdmin: PostgreSQL ERD tool
• draw.io: Free diagramming tool
• Lucidchart: Professional diagramming

ERD Design Process

1. Identify Entities: What objects need to be stored?
2. Define Attributes: What properties does each entity have?
3. Establish Relationships: How do entities relate?
4. Determine Cardinality: 1:1, 1:N, or M:N?
5. Add Constraints: PKs, FKs, NOT NULL, UNIQUE

-- ERD for a blog system
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(user_id)
);

CREATE TABLE comments (
    comment_id INT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    comment_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE tags (
    tag_id INT PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE
);

ERDs are essential tools for database design and communication. They help teams visualize the data model before implementation.