Database Relationships Explained: One-to-Many, Many-to-Many & When to Use Each

-- ─────────────────────────────────────────────────────────
-- SCENARIO: An e-commerce store where customers place orders.
-- One customer can place many orders.
-- The foreign key lives on the 'many' side (orders table).
-- ─────────────────────────────────────────────────────────

-- Step 1: Create the 'one' side first (parent table)
CREATE TABLE customers (
    customer_id   INT           PRIMARY KEY AUTO_INCREMENT,
    full_name     VARCHAR(100)  NOT NULL,
    email         VARCHAR(150)  NOT NULL UNIQUE
);

-- Step 2: Create the 'many' side (child table)
-- Notice: customer_id here is a FOREIGN KEY pointing to the parent
CREATE TABLE orders (
    order_id      INT           PRIMARY KEY AUTO_INCREMENT,
    customer_id   INT           NOT NULL,                    -- FK column
    order_date    DATE          NOT NULL,
    total_amount  DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT   -- prevent deleting a customer who has orders
        ON UPDATE CASCADE    -- if customer_id changes, propagate it
);

-- Step 3: Seed some data
INSERT INTO customers (full_name, email) VALUES
    ('Sarah Mitchell', 'sarah@example.com'),
    ('James Okafor',   'james@example.com');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
    (1, '2024-03-01', 129.99),  -- Sarah's first order
    (1, '2024-04-15', 49.00),   -- Sarah's second order
    (2, '2024-04-20', 310.50);  -- James's only order

-- Step 4: Fetch every customer alongside their order count
-- This is the most common query pattern for 1:N relationships
SELECT
    c.full_name,
    COUNT(o.order_id)        AS total_orders,
    SUM(o.total_amount)      AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id  -- LEFT JOIN keeps customers with 0 orders
GROUP BY c.customer_id, c.full_name
ORDER BY lifetime_value DESC;

-- ─────────────────────────────────────────────────────────
-- SCENARIO: A university system.
-- Students enrol in many courses; each course has many students.
-- The junction table (enrolments) turns M:N into two 1:N links.
-- ─────────────────────────────────────────────────────────

CREATE TABLE students (
    student_id    INT          PRIMARY KEY AUTO_INCREMENT,
    full_name     VARCHAR(100) NOT NULL,
    email         VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE courses (
    course_id     INT          PRIMARY KEY AUTO_INCREMENT,
    course_code   VARCHAR(10)  NOT NULL UNIQUE,  -- e.g. 'CS101'
    course_title  VARCHAR(200) NOT NULL
);

-- Junction table: each row represents ONE student enrolled in ONE course
CREATE TABLE enrolments (
    enrolment_id    INT         PRIMARY KEY AUTO_INCREMENT,
    student_id      INT         NOT NULL,
    course_id       INT         NOT NULL,
    enrolled_on     DATE        NOT NULL,
    final_grade     CHAR(2),                    -- NULL until the course ends

    -- Composite UNIQUE ensures a student can't enrol in the same course twice
    UNIQUE KEY uq_student_course (student_id, course_id),

    CONSTRAINT fk_enrolment_student
        FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE,   -- remove enrolments if student is deleted

    CONSTRAINT fk_enrolment_course
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
        ON DELETE RESTRICT   -- block deleting a course that has enrolments
);

-- Seed data
INSERT INTO students (full_name, email) VALUES
    ('Priya Nair',    'priya@uni.edu'),
    ('Tom Bergmann',  'tom@uni.edu'),
    ('Aisha Mensah',  'aisha@uni.edu');

INSERT INTO courses (course_code, course_title) VALUES
    ('CS101', 'Introduction to Programming'),
    ('DB201', 'Database Design Fundamentals'),
    ('ML301', 'Machine Learning Basics');

INSERT INTO enrolments (student_id, course_id, enrolled_on) VALUES
    (1, 1, '2024-01-10'),  -- Priya in CS101
    (1, 2, '2024-01-10'),  -- Priya in DB201
    (2, 1, '2024-01-11'),  -- Tom in CS101
    (2, 3, '2024-01-11'),  -- Tom in ML301
    (3, 2, '2024-01-12'),  -- Aisha in DB201
    (3, 3, '2024-01-12');  -- Aisha in ML301

-- Query 1: Which courses is Priya enrolled in?
SELECT
    s.full_name,
    c.course_code,
    c.course_title,
    e.enrolled_on
FROM enrolments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
WHERE s.full_name = 'Priya Nair'
ORDER BY e.enrolled_on;

-- Query 2: How many students are in each course?
SELECT
    c.course_code,
    c.course_title,
    COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrolments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_code, c.course_title
ORDER BY student_count DESC;

-- ─────────────────────────────────────────────────────────
-- SCENARIO: A SaaS app separating core login data from
-- optional profile details. Most queries only touch 'users'.
-- Profile data is only loaded when the profile page is viewed.
-- ─────────────────────────────────────────────────────────

-- Core login data — accessed on EVERY authenticated request
CREATE TABLE users (
    user_id       INT          PRIMARY KEY AUTO_INCREMENT,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    email         VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

-- Extended profile — only loaded when a user visits their profile page
CREATE TABLE user_profiles (
    profile_id    INT           PRIMARY KEY AUTO_INCREMENT,
    user_id       INT           NOT NULL UNIQUE,  -- UNIQUE enforces the 1:1
    display_name  VARCHAR(100),
    bio           TEXT,
    avatar_url    VARCHAR(500),
    website_url   VARCHAR(500),
    location      VARCHAR(100),

    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE  -- delete the profile if the user account is removed
);

-- Seed data: only some users have profiles
INSERT INTO users (username, email, password_hash) VALUES
    ('sarah_m',  'sarah@example.com', '$2b$12$hashed...'),
    ('james_o',  'james@example.com', '$2b$12$hashed...'),
    ('priya_n',  'priya@example.com', '$2b$12$hashed...');

-- Only Sarah and Priya have filled in their profiles
INSERT INTO user_profiles (user_id, display_name, bio, location) VALUES
    (1, 'Sarah Mitchell', 'Software engineer & coffee enthusiast.', 'Dublin, Ireland'),
    (3, 'Priya Nair',     'ML researcher. Writes about data.',       'Bangalore, India');

-- Fetch a user's profile — use LEFT JOIN so users without a profile still appear
SELECT
    u.username,
    u.email,
    COALESCE(p.display_name, u.username) AS display_name,  -- fallback to username
    p.bio,
    p.location
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id
ORDER BY u.user_id;

-- ─────────────────────────────────────────────────────────
-- SCENARIO: A company org chart stored in a single table.
-- Each employee can have a manager, who is also an employee.
-- The CEO has no manager, so manager_id is NULL at the top.
-- ─────────────────────────────────────────────────────────

CREATE TABLE employees (
    employee_id   INT          PRIMARY KEY AUTO_INCREMENT,
    full_name     VARCHAR(100) NOT NULL,
    job_title     VARCHAR(100) NOT NULL,
    manager_id    INT          NULL,  -- NULL means this person is the top of the chain

    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)  -- points to the SAME table
        ON DELETE SET NULL  -- if a manager is removed, reports become unmanaged
);

-- Build an org chart: CEO → VP → Managers → Developers
INSERT INTO employees (full_name, job_title, manager_id) VALUES
    ('Linda Forsythe',  'CEO',                NULL),   -- id=1, no manager
    ('Carlos Rivera',   'VP of Engineering',  1),      -- id=2, reports to Linda
    ('Aiko Tanaka',     'VP of Product',      1),      -- id=3, reports to Linda
    ('Ben Hughes',      'Engineering Manager',2),      -- id=4, reports to Carlos
    ('Fatima Al-Rashid','Senior Developer',   4),      -- id=5, reports to Ben
    ('Noah Eriksson',   'Developer',          4);      -- id=6, reports to Ben

-- Recursive CTE to walk the full org chart top-down
-- This works in PostgreSQL, MySQL 8+, SQL Server, and SQLite 3.35+
WITH RECURSIVE org_chart AS (

    -- Anchor: start with the CEO (no manager)
    SELECT
        employee_id,
        full_name,
        job_title,
        manager_id,
        0 AS depth,                         -- depth 0 = top level
        full_name AS reporting_chain
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: find direct reports of the current level
    SELECT
        e.employee_id,
        e.full_name,
        e.job_title,
        e.manager_id,
        oc.depth + 1,
        CONCAT(oc.reporting_chain, ' → ', e.full_name)  -- build the chain string
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id  -- join child to parent
)
SELECT
    REPEAT('    ', depth) || full_name AS indented_name,  -- indent by depth
    job_title,
    depth
FROM org_chart
ORDER BY reporting_chain;