ON DELETE CASCADE — The $2.3M Revenue Discrepancy

-- ─────────────────────────────────────────────────────────
-- 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;

// io.thecodeforge — database tutorial

-- Bad: no foreign key, no index
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,  -- no constraint, no index
    total DECIMAL(10,2)
);

-- Good: foreign key with index
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    CONSTRAINT fk_orders_users
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE  -- or RESTRICT, based on your business rules
);

CREATE INDEX idx_orders_user_id ON orders(user_id);  -- critical for JOIN performance

-- Check constraint violations don't exist:
SELECT count(*) FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- Output: 0 (if enforcement is working)

// io.thecodeforge — database tutorial

-- Student-course enrollment with composite primary key
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade CHAR(2),
    
    -- Natural key: no two enrollments for same student+course pair
    PRIMARY KEY (student_id, course_id),
    
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Child table referencing composite key
CREATE TABLE assignments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    assignment_id SERIAL,
    score DECIMAL(5,2),
    
    PRIMARY KEY (student_id, course_id, assignment_id),
    
    -- Must reference both columns
    FOREIGN KEY (student_id, course_id) 
        REFERENCES enrollments(student_id, course_id)
);

-- Query needs both columns
SELECT e.*, a.score
FROM enrollments e
JOIN assignments a 
  ON e.student_id = a.student_id 
 AND e.course_id = a.course_id
WHERE e.student_id = 42;

// io.thecodeforge — database tutorial

CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Patient (
    PatientID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Medication (
    MedicationID INT PRIMARY KEY,
    DrugName VARCHAR(100)
);

CREATE TABLE Prescription (
    DoctorID INT,
    PatientID INT,
    MedicationID INT,
    Dosage VARCHAR(50),
    PRIMARY KEY (DoctorID, PatientID, MedicationID),
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID),
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
    FOREIGN KEY (MedicationID) REFERENCES Medication(MedicationID)
);

// io.thecodeforge — database tutorial

-- One-to-Many: One department, many employees
CREATE TABLE Department (
    DeptID INT PRIMARY KEY
);

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    DeptID INT FOREIGN KEY REFERENCES Department(DeptID)
);

-- Many-to-Many: Many students, many courses
CREATE TABLE Student (
    StudentID INT PRIMARY KEY
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY
);

CREATE TABLE Enrollment (
    StudentID INT FOREIGN KEY REFERENCES Student(StudentID),
    CourseID INT FOREIGN KEY REFERENCES Course(CourseID),
    PRIMARY KEY (StudentID, CourseID)
);

-- Create employees table with self-referential foreign key
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

-- Insert sample data
INSERT INTO employees VALUES (1, 'Alice', NULL);
INSERT INTO employees VALUES (2, 'Bob', 1);
INSERT INTO employees VALUES (3, 'Charlie', 1);
INSERT INTO employees VALUES (4, 'Diana', 2);

-- Recursive CTE to get all subordinates of Alice
WITH RECURSIVE org_tree AS (
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE employee_id = 1
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.employee_id
)
SELECT * FROM org_tree;

-- Create tables for a many-to-many relationship
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

-- Junction table with composite primary key
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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
);

-- Index foreign key columns for performance
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

-- Query: Get all courses for a student
SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

-- Polymorphic association (anti-pattern example)
CREATE TABLE comments (
    comment_id INT PRIMARY KEY,
    body TEXT,
    target_type VARCHAR(50),  -- e.g., 'Post', 'Video'
    target_id INT,
    -- No foreign key constraint possible
    created_at TIMESTAMP
);

-- Better alternative: separate junction tables
CREATE TABLE post_comments (
    comment_id INT PRIMARY KEY,
    post_id INT REFERENCES posts(post_id) ON DELETE CASCADE,
    body TEXT
);

CREATE TABLE video_comments (
    comment_id INT PRIMARY KEY,
    video_id INT REFERENCES videos(video_id) ON DELETE CASCADE,
    body TEXT
);

-- Or use a single table with nullable foreign keys
CREATE TABLE comments (
    comment_id INT PRIMARY KEY,
    body TEXT,
    post_id INT REFERENCES posts(post_id) ON DELETE CASCADE,
    video_id INT REFERENCES videos(video_id) ON DELETE CASCADE,
    CHECK ( (post_id IS NOT NULL AND video_id IS NULL) OR (post_id IS NULL AND video_id IS NOT NULL) )
);