Mapping One-to-Many Relationships to Relations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

-- =========================================
-- E-Commerce Order System - Complete Schema
-- =========================================
 
-- Customer: Parent entity (independent)
CREATE TABLE Customer (
    customer_id     SERIAL PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    full_name       VARCHAR(200) NOT NULL,
    phone           VARCHAR(20),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Product: Parent entity (independent)
CREATE TABLE Product (
    product_id      SERIAL PRIMARY KEY,
    sku             VARCHAR(50) UNIQUE NOT NULL,
    name            VARCHAR(300) NOT NULL,
    description     TEXT,
    price           DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity  INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Order: Child of Customer, Parent of OrderLine
CREATE TABLE "Order" (
    order_id        SERIAL PRIMARY KEY,
    order_number    VARCHAR(20) UNIQUE NOT NULL,  -- Business-friendly ID
    order_date      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    subtotal        DECIMAL(12,2) NOT NULL DEFAULT 0,
    tax_amount      DECIMAL(10,2) NOT NULL DEFAULT 0,
    total_amount    DECIMAL(12,2) NOT NULL DEFAULT 0,
    shipping_address TEXT,
    
    -- FK to Customer: Total participation (order MUST have customer)
    customer_id     INT NOT NULL,
    
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
        ON DELETE RESTRICT  -- Don't delete customers with orders
        ON UPDATE CASCADE,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- CRITICAL: Index on FK for join performance
CREATE INDEX idx_order_customer ON "Order"(customer_id);
CREATE INDEX idx_order_status ON "Order"(status);
CREATE INDEX idx_order_date ON "Order"(order_date);
 
-- OrderLine: Child of both Order and Product (Weak Entity)
CREATE TABLE OrderLine (
    order_line_id   SERIAL PRIMARY KEY,
    
    -- FK to Order: Total participation (line MUST belong to order)
    order_id        INT NOT NULL,
    
    -- FK to Product: Total participation (line MUST reference product)
    product_id      INT NOT NULL,
    
    -- Line details
    quantity        INT NOT NULL CHECK (quantity > 0),
    unit_price      DECIMAL(10,2) NOT NULL,  -- Snapshot at order time
    line_total      DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    
    -- Unique constraint: one entry per product per order
    CONSTRAINT uq_order_product UNIQUE (order_id, product_id),
    
    CONSTRAINT fk_orderline_order
        FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
        ON DELETE CASCADE   -- Delete order → delete all lines
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_orderline_product
        FOREIGN KEY (product_id) REFERENCES Product(product_id)
        ON DELETE RESTRICT  -- Don't delete products on active orders
        ON UPDATE CASCADE
);
 
-- Indexes for FK columns
CREATE INDEX idx_orderline_order ON OrderLine(order_id);
CREATE INDEX idx_orderline_product ON OrderLine(product_id);
 
 
-- =========================================
-- Sample Queries
-- =========================================
 
-- Customer's order history with totals
SELECT 
    c.full_name,
    o.order_number,
    o.order_date,
    o.status,
    o.total_amount
FROM Customer c
JOIN "Order" o ON c.customer_id = o.customer_id
WHERE c.customer_id = 1
ORDER BY o.order_date DESC;
 
-- Order details with line items
SELECT 
    o.order_number,
    p.name AS product,
    ol.quantity,
    ol.unit_price,
    ol.line_total
FROM "Order" o
JOIN OrderLine ol ON o.order_id = ol.order_id
JOIN Product p ON ol.product_id = p.product_id
WHERE o.order_id = 100;
 
-- Products never ordered (partial participation allows this)
SELECT p.name, p.sku
FROM Product p
LEFT JOIN OrderLine ol ON p.product_id = ol.product_id
WHERE ol.order_line_id IS NULL;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146

-- =========================================
-- Content Management System - Blog Platform
-- =========================================
 
-- Author: Parent entity
CREATE TABLE Author (
    author_id       SERIAL PRIMARY KEY,
    username        VARCHAR(50) UNIQUE NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    display_name    VARCHAR(100) NOT NULL,
    bio             TEXT,
    avatar_url      VARCHAR(500),
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Category: Parent entity (optional relationship)
CREATE TABLE Category (
    category_id     SERIAL PRIMARY KEY,
    name            VARCHAR(100) UNIQUE NOT NULL,
    slug            VARCHAR(100) UNIQUE NOT NULL,
    description     TEXT,
    parent_id       INT,  -- Self-referential for subcategories
    
    CONSTRAINT fk_category_parent
        FOREIGN KEY (parent_id) REFERENCES Category(category_id)
        ON DELETE SET NULL  -- Parent category deleted → subcategories become top-level
        ON UPDATE CASCADE
);
 
CREATE INDEX idx_category_parent ON Category(parent_id);
 
-- Post: Child of Author and Category
CREATE TABLE Post (
    post_id         SERIAL PRIMARY KEY,
    title           VARCHAR(500) NOT NULL,
    slug            VARCHAR(500) UNIQUE NOT NULL,
    excerpt         TEXT,
    content         TEXT NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'published', 'archived')),
    published_at    TIMESTAMP,
    view_count      INT DEFAULT 0,
    
    -- FK to Author: Total participation (post MUST have author)
    author_id       INT NOT NULL,
    
    -- FK to Category: Partial participation (post MAY have category)
    category_id     INT,  -- Nullable allows uncategorized posts
    
    CONSTRAINT fk_post_author
        FOREIGN KEY (author_id) REFERENCES Author(author_id)
        ON DELETE RESTRICT  -- Don't delete authors with posts
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_post_category
        FOREIGN KEY (category_id) REFERENCES Category(category_id)
        ON DELETE SET NULL  -- Category deleted → post becomes uncategorized
        ON UPDATE CASCADE,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_post_author ON Post(author_id);
CREATE INDEX idx_post_category ON Post(category_id);
CREATE INDEX idx_post_status ON Post(status);
CREATE INDEX idx_post_published ON Post(published_at) WHERE status = 'published';
 
-- Comment: Child of Post (Weak Entity semantically)
CREATE TABLE Comment (
    comment_id      SERIAL PRIMARY KEY,
    content         TEXT NOT NULL,
    author_name     VARCHAR(100) NOT NULL,  -- Guest comments allowed
    author_email    VARCHAR(255),
    is_approved     BOOLEAN DEFAULT FALSE,
    
    -- FK to Post: Total participation (comment MUST belong to post)
    post_id         INT NOT NULL,
    
    -- Self-referential for reply threads
    parent_comment_id INT,
    
    CONSTRAINT fk_comment_post
        FOREIGN KEY (post_id) REFERENCES Post(post_id)
        ON DELETE CASCADE   -- Post deleted → all comments deleted
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_comment_parent
        FOREIGN KEY (parent_comment_id) REFERENCES Comment(comment_id)
        ON DELETE CASCADE   -- Parent comment deleted → replies deleted
        ON UPDATE CASCADE,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_comment_post ON Comment(post_id);
CREATE INDEX idx_comment_parent ON Comment(parent_comment_id);
CREATE INDEX idx_comment_approved ON Comment(is_approved) WHERE is_approved = TRUE;
 
 
-- =========================================
-- Sample Queries
-- =========================================
 
-- Published posts with author and category
SELECT 
    p.title,
    a.display_name AS author,
    c.name AS category,
    p.published_at,
    p.view_count
FROM Post p
JOIN Author a ON p.author_id = a.author_id
LEFT JOIN Category c ON p.category_id = c.category_id  -- LEFT: category optional
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10;
 
-- Post with comment tree (PostgreSQL recursive CTE)
WITH RECURSIVE comment_tree AS (
    -- Base: top-level comments
    SELECT comment_id, content, author_name, parent_comment_id, 0 AS depth
    FROM Comment
    WHERE post_id = 1 AND parent_comment_id IS NULL AND is_approved = TRUE
    
    UNION ALL
    
    -- Recursive: replies
    SELECT c.comment_id, c.content, c.author_name, c.parent_comment_id, ct.depth + 1
    FROM Comment c
    JOIN comment_tree ct ON c.parent_comment_id = ct.comment_id
    WHERE c.is_approved = TRUE
)
SELECT * FROM comment_tree ORDER BY depth, comment_id;
 
-- Categories with post counts
SELECT 
    c.name,
    c.slug,
    COUNT(p.post_id) AS post_count
FROM Category c
LEFT JOIN Post p ON c.category_id = p.category_id AND p.status = 'published'
GROUP BY c.category_id
ORDER BY post_count DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158

-- =========================================
-- Human Resources System - Complete Schema
-- =========================================
 
-- Department: Self-referential hierarchy
CREATE TABLE Department (
    dept_id         SERIAL PRIMARY KEY,
    dept_code       VARCHAR(10) UNIQUE NOT NULL,
    name            VARCHAR(100) NOT NULL,
    description     TEXT,
    budget          DECIMAL(15,2),
    
    -- Self-reference for department hierarchy
    parent_dept_id  INT,  -- NULL for top-level departments
    
    -- Manager reference (added after Employee exists)
    manager_id      INT,  -- Set later via ALTER
    
    CONSTRAINT fk_dept_parent
        FOREIGN KEY (parent_dept_id) REFERENCES Department(dept_id)
        ON DELETE SET NULL  -- Parent deleted → becomes top-level
        ON UPDATE CASCADE,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_dept_parent ON Department(parent_dept_id);
 
-- Employee: Core entity with multiple 1:N relationships
CREATE TABLE Employee (
    emp_id          SERIAL PRIMARY KEY,
    employee_number VARCHAR(20) UNIQUE NOT NULL,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    phone           VARCHAR(20),
    hire_date       DATE NOT NULL,
    job_title       VARCHAR(100) NOT NULL,
    salary          DECIMAL(12,2) NOT NULL CHECK (salary > 0),
    employment_status VARCHAR(20) NOT NULL DEFAULT 'active'
                    CHECK (employment_status IN ('active', 'on_leave', 'terminated')),
    
    -- FK to Department: Total participation
    dept_id         INT NOT NULL,
    
    -- Self-reference for manager: Partial (CEO has no manager)
    manager_id      INT,
    
    CONSTRAINT fk_emp_department
        FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE RESTRICT  -- Cannot delete department with employees
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_emp_manager
        FOREIGN KEY (manager_id) REFERENCES Employee(emp_id)
        ON DELETE SET NULL  -- Manager leaves → subordinates manager-less
        ON UPDATE CASCADE,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_emp_department ON Employee(dept_id);
CREATE INDEX idx_emp_manager ON Employee(manager_id);
CREATE INDEX idx_emp_status ON Employee(employment_status);
 
-- Now add department manager FK
ALTER TABLE Department
ADD CONSTRAINT fk_dept_manager
    FOREIGN KEY (manager_id) REFERENCES Employee(emp_id)
    ON DELETE SET NULL  -- Manager leaves → department temporarily without manager
    ON UPDATE CASCADE;
 
CREATE INDEX idx_dept_manager ON Department(manager_id);
 
-- Performance Review: Time-series relationship
CREATE TABLE PerformanceReview (
    review_id       SERIAL PRIMARY KEY,
    review_period   VARCHAR(20) NOT NULL,  -- e.g., '2024-Q1'
    review_date     DATE NOT NULL,
    rating          INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    strengths       TEXT,
    improvements    TEXT,
    goals           TEXT,
    
    -- FK to Employee: Total participation
    employee_id     INT NOT NULL,
    
    -- FK to reviewer (also an employee)
    reviewer_id     INT NOT NULL,
    
    CONSTRAINT fk_review_employee
        FOREIGN KEY (employee_id) REFERENCES Employee(emp_id)
        ON DELETE CASCADE   -- Employee deleted → reviews deleted
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_review_reviewer
        FOREIGN KEY (reviewer_id) REFERENCES Employee(emp_id)
        ON DELETE RESTRICT  -- Cannot delete reviewer with active reviews
        ON UPDATE CASCADE,
        
    -- One review per employee per period
    CONSTRAINT uq_review_period UNIQUE (employee_id, review_period)
);
 
CREATE INDEX idx_review_employee ON PerformanceReview(employee_id);
CREATE INDEX idx_review_reviewer ON PerformanceReview(reviewer_id);
 
 
-- =========================================
-- Sample Queries
-- =========================================
 
-- Organizational chart: employees with managers
SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    e.job_title,
    d.name AS department,
    m.first_name || ' ' || m.last_name AS manager
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id
LEFT JOIN Employee m ON e.manager_id = m.emp_id
WHERE e.employment_status = 'active'
ORDER BY d.name, e.last_name;
 
-- Department hierarchy with recursive CTE
WITH RECURSIVE dept_tree AS (
    -- Base: top-level departments
    SELECT dept_id, name, parent_dept_id, 0 AS level,
           name::TEXT AS path
    FROM Department
    WHERE parent_dept_id IS NULL
    
    UNION ALL
    
    -- Recursive: sub-departments
    SELECT d.dept_id, d.name, d.parent_dept_id, dt.level + 1,
           dt.path || ' > ' || d.name
    FROM Department d
    JOIN dept_tree dt ON d.parent_dept_id = dt.dept_id
)
SELECT * FROM dept_tree ORDER BY path;
 
-- Find all subordinates (direct and indirect)
WITH RECURSIVE subordinates AS (
    -- Base: direct reports
    SELECT emp_id, first_name, last_name, manager_id, 1 AS level
    FROM Employee
    WHERE manager_id = 1  -- Starting manager ID
    
    UNION ALL
    
    -- Recursive: reports of reports
    SELECT e.emp_id, e.first_name, e.last_name, e.manager_id, s.level + 1
    FROM Employee e
    JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT * FROM subordinates ORDER BY level, last_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192

-- =========================================
-- Financial System - Banking Schema
-- =========================================
 
-- Customer: Core entity with strict handling
CREATE TABLE Customer (
    customer_id     SERIAL PRIMARY KEY,
    customer_number VARCHAR(20) UNIQUE NOT NULL,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    email           VARCHAR(255) UNIQUE,
    phone           VARCHAR(20),
    date_of_birth   DATE,
    ssn_last_four   CHAR(4),  -- Partial SSN for verification
    
    -- Soft delete: never actually delete customers
    is_active       BOOLEAN DEFAULT TRUE,
    deactivated_at  TIMESTAMP,
    
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Account Types: Reference data
CREATE TABLE AccountType (
    type_id         SERIAL PRIMARY KEY,
    type_code       VARCHAR(20) UNIQUE NOT NULL,
    type_name       VARCHAR(100) NOT NULL,
    interest_rate   DECIMAL(5,4) DEFAULT 0,
    min_balance     DECIMAL(12,2) DEFAULT 0,
    monthly_fee     DECIMAL(10,2) DEFAULT 0
);
 
-- Insert default account types
INSERT INTO AccountType (type_code, type_name, interest_rate, min_balance) VALUES
('CHECKING', 'Checking Account', 0, 0),
('SAVINGS', 'Savings Account', 0.0025, 100),
('MONEY_MARKET', 'Money Market', 0.0100, 2500);
 
-- Account: Child of Customer and AccountType
CREATE TABLE Account (
    account_id      SERIAL PRIMARY KEY,
    account_number  VARCHAR(20) UNIQUE NOT NULL,
    current_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    available_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    opened_date     DATE NOT NULL DEFAULT CURRENT_DATE,
    
    -- Account status (soft close instead of delete)
    status          VARCHAR(20) NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'frozen', 'closed')),
    closed_date     DATE,
    
    -- FK to Customer: Total participation
    customer_id     INT NOT NULL,
    
    -- FK to AccountType: Total participation
    type_id         INT NOT NULL,
    
    CONSTRAINT fk_account_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
        ON DELETE RESTRICT  -- NEVER delete customers with accounts
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_account_type
        FOREIGN KEY (type_id) REFERENCES AccountType(type_id)
        ON DELETE RESTRICT  -- Cannot delete account types in use
        ON UPDATE CASCADE,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_account_customer ON Account(customer_id);
CREATE INDEX idx_account_type ON Account(type_id);
CREATE INDEX idx_account_status ON Account(status);
 
-- Transaction: Critical financial record (NEVER delete)
CREATE TABLE Transaction (
    transaction_id  SERIAL PRIMARY KEY,
    transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    type            VARCHAR(20) NOT NULL
                    CHECK (type IN ('deposit', 'withdrawal', 'transfer', 'fee', 'interest')),
    amount          DECIMAL(12,2) NOT NULL,
    description     VARCHAR(500),
    reference_number VARCHAR(50) UNIQUE,
    
    -- Running balance after this transaction
    balance_after   DECIMAL(15,2) NOT NULL,
    
    -- FK to Account: Total participation (transaction MUST belong to account)
    account_id      INT NOT NULL,
    
    -- For transfers: the other account
    related_account_id INT,
    
    CONSTRAINT fk_transaction_account
        FOREIGN KEY (account_id) REFERENCES Account(account_id)
        ON DELETE RESTRICT  -- NEVER allow account deletion with transactions
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_transaction_related
        FOREIGN KEY (related_account_id) REFERENCES Account(account_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE ,
        
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Indexes for transaction queries
CREATE INDEX idx_trans_account ON Transaction(account_id);
CREATE INDEX idx_trans_date ON Transaction(transaction_date);
CREATE INDEX idx_trans_type ON Transaction(type);
CREATE INDEX idx_trans_related ON Transaction(related_account_id);
 
-- Audit Log: Track all changes to financial data
CREATE TABLE AuditLog (
    audit_id        SERIAL PRIMARY KEY,
    table_name      VARCHAR(100) NOT NULL,
    record_id       INT NOT NULL,
    action          VARCHAR(20) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,
    new_values      JSONB,
    changed_by      VARCHAR(100),
    changed_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_audit_table ON AuditLog(table_name, record_id);
CREATE INDEX idx_audit_date ON AuditLog(changed_at);
 
 
-- =========================================
-- Trigger for Audit Trail
-- =========================================
 
CREATE OR REPLACE FUNCTION audit_account_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO AuditLog (table_name, record_id, action, old_values, new_values, changed_by)
        VALUES ('Account', NEW.account_id, 'UPDATE', 
                row_to_json(OLD)::JSONB, 
                row_to_json(NEW)::JSONB,
                current_user);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER tr_audit_account
AFTER UPDATE ON Account
FOR EACH ROW
EXECUTE FUNCTION audit_account_changes();
 
 
-- =========================================
-- Sample Queries
-- =========================================
 
-- Customer accounts with balances
SELECT 
    c.first_name || ' ' || c.last_name AS customer,
    a.account_number,
    at.type_name,
    a.current_balance,
    a.status
FROM Customer c
JOIN Account a ON c.customer_id = a.customer_id
JOIN AccountType at ON a.type_id = at.type_id
WHERE c.is_active = TRUE
ORDER BY c.last_name, a.account_number;
 
-- Recent transactions for an account
SELECT 
    t.transaction_date,
    t.type,
    t.amount,
    t.balance_after,
    t.description
FROM Transaction t
WHERE t.account_id = 1
ORDER BY t.transaction_date DESC
LIMIT 50;
 
-- Monthly statement (aggregated by type)
SELECT 
    type,
    COUNT(*) AS transaction_count,
    SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS total_credits,
    SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END) AS total_debits
FROM Transaction
WHERE account_id = 1
  AND transaction_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY type;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- =========================================
-- Anti-Pattern 1: Missing FK Constraint
-- (Application thinks there's a relationship, but DB doesn't enforce it)
-- =========================================
 
-- BAD: No constraint
CREATE TABLE Order_Bad (
    order_id    INT PRIMARY KEY,
    customer_id INT  -- No FK constraint!
);
 
-- This allows invalid data:
INSERT INTO Order_Bad VALUES (1, 99999);  -- Customer 99999 doesn't exist!
 
-- GOOD: Proper constraint
CREATE TABLE Order_Good (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
 
 
-- =========================================
-- Anti-Pattern 2: Missing FK Index
-- =========================================
 
-- BAD: FK defined but no index
CREATE TABLE Employee_Bad (
    emp_id      INT PRIMARY KEY,
    dept_id     INT NOT NULL REFERENCES Department(dept_id)
    -- No index on dept_id!
);
 
-- Operations on Department become O(n) scans!
-- DELETE FROM Department WHERE dept_id = 10;
-- ↑ Scans entire Employee_Bad table to check references
 
-- GOOD: Index the FK
CREATE TABLE Employee_Good (
    emp_id      INT PRIMARY KEY,
    dept_id     INT NOT NULL REFERENCES Department(dept_id)
);
CREATE INDEX idx_emp_dept ON Employee_Good(dept_id);  -- Critical!
 
 
-- =========================================
-- Anti-Pattern 3: Wrong Participation Mapping
-- =========================================
 
-- BAD: Total participation but nullable FK
-- Business rule: "Every order line MUST belong to an order"
CREATE TABLE OrderLine_Bad (
    line_id     INT PRIMARY KEY,
    order_id    INT,  -- Nullable! Allows orphan order lines
    product_id  INT NOT NULL
);
 
-- GOOD: NOT NULL enforces total participation
CREATE TABLE OrderLine_Good (
    line_id     INT PRIMARY KEY,
    order_id    INT NOT NULL,  -- Cannot be NULL
    product_id  INT NOT NULL,
    CONSTRAINT fk_orderline_order
        FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
);