One-to-Many Database Relationships: Complete Guide with Interactive Examples

-- Parent table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Child table with foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,           -- Foreign key
    order_date DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- Create index on foreign key for performance
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Sample data shows the relationship in action
-- Alice has multiple orders, Bob has multiple orders, Carol has none

-- Basic query to join related data
SELECT c.name, o.total, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- CASCADE: Delete child records when parent is deleted
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

-- RESTRICT: Prevent parent deletion if children exist
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT

-- SET NULL: Set foreign key to NULL when parent is deleted
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL

-- SET DEFAULT: Set foreign key to default value when parent is deleted
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET DEFAULT

-- Simple e-commerce example
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    total DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Authors write many posts
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    author_id INTEGER,
    title VARCHAR(255),
    content TEXT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Posts can have many comments
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER,
    commenter_name VARCHAR(100),
    comment_text TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- Simple project hierarchy
CREATE TABLE teams (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    team_id INTEGER,
    name VARCHAR(200),
    FOREIGN KEY (team_id) REFERENCES teams(id)
);

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    project_id INTEGER,
    title VARCHAR(255),
    assigned_to VARCHAR(100),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

-- How many orders does each customer have?
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Which customers have never ordered?
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

-- Total spending per customer
SELECT c.name, COALESCE(SUM(o.total), 0) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Basic foreign key index (essential)
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Composite indexes for common query patterns
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Partial indexes for specific conditions
CREATE INDEX idx_orders_pending ON orders(customer_id, order_date) 
WHERE status = 'pending';

-- Covering indexes to avoid table lookups
CREATE INDEX idx_orders_summary ON orders(customer_id, status) 
INCLUDE (order_date, total);

-- Expression indexes for computed values
CREATE INDEX idx_orders_year_month ON orders(customer_id, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date));

-- Use EXISTS instead of IN for large datasets
-- Efficient
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
    AND o.total > 1000
);

-- Less efficient for large order tables
SELECT c.name
FROM customers c
WHERE c.id IN (
    SELECT customer_id FROM orders 
    WHERE total > 1000
);

-- Use LIMIT with ORDER BY for pagination
SELECT c.name, o.order_date, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
ORDER BY o.order_date DESC
LIMIT 20 OFFSET 0;  -- First page

-- Use window functions instead of correlated subqueries
-- Efficient
SELECT 
    c.name,
    o.order_date,
    o.total,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.order_date DESC) as order_rank
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Less efficient
SELECT 
    c.name,
    o.order_date,
    o.total,
    (SELECT COUNT(*) FROM orders o2 
     WHERE o2.customer_id = c.id 
     AND o2.order_date >= o.order_date) as order_rank
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Efficient bulk updates using JOINs
UPDATE orders 
SET status = 'shipped'
FROM (
    SELECT order_id 
    FROM shipping_manifest 
    WHERE shipped_date = CURRENT_DATE
) shipped
WHERE orders.id = shipped.order_id;

-- Bulk insert with foreign key validation
INSERT INTO orders (customer_id, order_date, total, status)
SELECT 
    c.id,
    CURRENT_DATE,
    new_orders.total,
    'pending'
FROM customers c
INNER JOIN temp_new_orders new_orders ON c.email = new_orders.customer_email
WHERE c.status = 'active';

-- Efficient deletion with EXISTS
DELETE FROM orders 
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.id = orders.customer_id 
    AND c.status = 'inactive'
    AND c.last_login < CURRENT_DATE - INTERVAL '2 years'
);

-- Employee hierarchy
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    manager_id INTEGER,  -- Self-referencing foreign key
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
);

-- Category tree
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER,
    sort_order INTEGER DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Recursive queries for hierarchical data
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level managers (no manager)
    SELECT 
        id, name, manager_id, salary, 0 as level,
        CAST(name AS TEXT) as path,
        ARRAY[id] as path_ids
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT 
        e.id, e.name, e.manager_id, e.salary, eh.level + 1,
        eh.path || ' > ' || e.name,
        eh.path_ids || e.id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
    REPEAT('  ', level) || name as indented_name,
    level,
    salary,
    path
FROM employee_hierarchy
ORDER BY path_ids;

-- Calculate total team size for each manager
WITH RECURSIVE team_size AS (
    SELECT id, name, 1 as team_size
    FROM employees
    WHERE id NOT IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL)
    
    UNION ALL
    
    SELECT 
        e.id, e.name, 
        1 + COALESCE(SUM(ts.team_size), 0)
    FROM employees e
    LEFT JOIN team_size ts ON ts.id IN (
        SELECT id FROM employees WHERE manager_id = e.id
    )
    GROUP BY e.id, e.name
)
SELECT name, team_size
FROM team_size
ORDER BY team_size DESC;

-- Foreign key without index - poor JOIN performance
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,  -- No index!
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Always index foreign key columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Consider composite indexes for common query patterns
CREATE INDEX idx_orders_customer_status_date ON orders(customer_id, status, order_date);

-- Instead of multiple queries, use aggregation
SELECT 
    c.name,
    COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Wrong: This prevents customer deletion if they have orders
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT

-- Wrong: This deletes order history when customer is deleted
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

-- Better: Archive customer but keep order history
-- Add archived flag to customers table
ALTER TABLE customers ADD COLUMN archived BOOLEAN DEFAULT FALSE;

-- Use SET NULL if order history should remain
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL

-- Or create a separate archived_customers table for deleted customers
CREATE TABLE archived_customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- This excludes customers without orders
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Use LEFT JOIN to include all customers
SELECT 
    c.name, 
    COALESCE(SUM(o.total), 0) as total_spent,
    COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Consistent, descriptive naming
CREATE TABLE customers (id, name, email);           -- Parent table
CREATE TABLE customer_orders (id, customer_id);    -- Child with clear reference

-- Foreign key naming
customer_id  -- References customers.id
author_id    -- References authors.id
category_id  -- References categories.id

-- Index naming
idx_orders_customer_id           -- Basic foreign key index
idx_orders_customer_status_date  -- Composite index

-- Use consistent data types
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,          -- Standard primary key type
    name VARCHAR(100) NOT NULL      -- Appropriate length limits
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,   -- Same type as customers.id
    total DECIMAL(10,2) NOT NULL,   -- Appropriate precision for money
    order_date DATE NOT NULL,       -- Use specific date/time types
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

-- Add appropriate constraints
ALTER TABLE orders ADD CONSTRAINT chk_total_positive CHECK (total > 0);
ALTER TABLE orders ADD CONSTRAINT chk_valid_status 
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'));

-- Document complex relationships
COMMENT ON TABLE orders IS 'Customer orders with line items tracked separately';
COMMENT ON COLUMN orders.customer_id IS 'References customers.id - CASCADE delete not used to preserve order history';

-- Document business rules
COMMENT ON CONSTRAINT fk_orders_customer IS 'RESTRICT prevents customer deletion with existing orders - archive customer instead';

-- Test foreign key constraints
-- This should fail
INSERT INTO orders (customer_id, total) VALUES (999, 100.00);  -- Non-existent customer

-- This should succeed
INSERT INTO customers (name, email) VALUES ('Test Customer', 'test@example.com');
INSERT INTO orders (customer_id, total) VALUES (
    (SELECT id FROM customers WHERE email = 'test@example.com'), 
    100.00
);

-- Test cascade behavior
DELETE FROM customers WHERE email = 'test@example.com';  -- Should handle based on ON DELETE rule

-- Monitor query performance
EXPLAIN (ANALYZE, BUFFERS) 
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY order_count DESC;

-- Check index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename IN ('customers', 'orders')
ORDER BY idx_scan DESC;