Three-Level Architecture

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

-- Conceptual Schema: The actual base table
CREATE TABLE Employee (
    employee_id     INT PRIMARY KEY,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    email           VARCHAR(100),
    phone           VARCHAR(20),
    department_id   INT,
    hire_date       DATE,
    salary          DECIMAL(10,2),
    ssn             CHAR(11),             -- Sensitive!
    performance_rating INT,
    manager_id      INT
);
 
-- External Schema 1: HR Manager View (Full Access)
CREATE VIEW HR_Employee_View AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    email,
    phone,
    department_id,
    hire_date,
    salary,
    CASE 
        WHEN performance_rating >= 4 THEN 'Excellent'
        WHEN performance_rating >= 3 THEN 'Good'
        WHEN performance_rating >= 2 THEN 'Needs Improvement'
        ELSE 'Under Review'
    END AS performance_status
FROM Employee;
 
-- External Schema 2: Employee Directory View (Limited Access)
CREATE VIEW Employee_Directory_View AS
SELECT 
    first_name,
    last_name,
    email,
    phone,
    d.department_name
FROM Employee e
JOIN Department d ON e.department_id = d.department_id;
-- Note: No salary, no SSN, no performance data
 
-- External Schema 3: Payroll System View (Financial Only)
CREATE VIEW Payroll_View AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS employee_name,
    salary,
    hire_date,
    CASE 
        WHEN hire_date < CURRENT_DATE - INTERVAL '1 year' 
        THEN salary * 0.05 
        ELSE 0 
    END AS annual_bonus_estimate
FROM Employee;
-- Note: Performance visible indirectly through bonus calculation

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

-- ✅ UPDATABLE: Simple projection view
CREATE VIEW Active_Employees AS
SELECT employee_id, first_name, last_name, email, department_id
FROM Employee
WHERE status = 'active';
 
-- This works: clear mapping to base table row
UPDATE Active_Employees 
SET email = 'newemail@company.com' 
WHERE employee_id = 123;
 
-- ❌ NOT UPDATABLE: Aggregate view
CREATE VIEW Department_Salary_Stats AS
SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM Employee
GROUP BY department_id;
 
-- This fails: what does it mean to update an average?
UPDATE Department_Salary_Stats 
SET avg_salary = 75000 
WHERE department_id = 5;
-- Error: Cannot update aggregate view
 
-- ⚠️ PARTIALLY UPDATABLE: View with computed column
CREATE VIEW Employee_Full_Name AS
SELECT employee_id, 
       first_name, 
       last_name,
       first_name || ' ' || last_name AS full_name
FROM Employee;
 
-- This works: updating base columns
UPDATE Employee_Full_Name 
SET first_name = 'Jonathan' 
WHERE employee_id = 123;
 
-- This fails: cannot update computed column
UPDATE Employee_Full_Name 
SET full_name = 'Jonathan Smith' 
WHERE employee_id = 123;
-- Error: Cannot update generated column

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

-- Layer 1: Base cleansing view (standardizes data)
CREATE VIEW v_Employee_Clean AS
SELECT 
    employee_id,
    TRIM(UPPER(first_name)) AS first_name,
    TRIM(UPPER(last_name)) AS last_name,
    LOWER(email) AS email,
    COALESCE(department_id, 0) AS department_id,  -- Default for null
    hire_date,
    COALESCE(salary, 0) AS salary
FROM Employee
WHERE status != 'deleted';  -- Soft deletes filtered out
 
-- Layer 2: Enrichment view (joins with reference data)
CREATE VIEW v_Employee_Enriched AS
SELECT 
    e.*,
    d.department_name,
    d.cost_center,
    m.first_name || ' ' || m.last_name AS manager_name
FROM v_Employee_Clean e
LEFT JOIN Department d ON e.department_id = d.department_id
LEFT JOIN v_Employee_Clean m ON e.manager_id = m.employee_id;
 
-- Layer 3: Application-specific views (for end users)
CREATE VIEW HR_Dashboard AS
SELECT 
    department_name,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MIN(hire_date) AS earliest_hire,
    MAX(hire_date) AS latest_hire
FROM v_Employee_Enriched
GROUP BY department_name;
 
-- Benefits of layering:
-- 1. Cleansing logic defined once, reused everywhere
-- 2. Changes to cleansing propagate to all derived views
-- 3. Each layer has a clear, testable responsibility
-- 4. Complex views become compositions of simple ones

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

-- Customer-facing order history view
CREATE VIEW Customer_Order_History AS
SELECT 
    o.order_id,
    o.customer_id,
    o.order_date,
    o.order_status,
    '$' || TO_CHAR(o.total_amount, 'FM999,999.00') AS formatted_total,
    STRING_AGG(p.product_name, ', ') AS items_summary,
    COUNT(oi.item_id) AS item_count
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE o.order_status != 'cancelled'
GROUP BY o.order_id, o.customer_id, o.order_date, 
         o.order_status, o.total_amount;
 
-- Warehouse shipping view
CREATE VIEW Pending_Shipments AS
SELECT 
    o.order_id,
    c.shipping_address,
    c.shipping_city,
    c.shipping_zip,
    oi.product_id,
    p.product_name,
    oi.quantity,
    inv.warehouse_location,
    inv.bin_number,
    o.promised_ship_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
JOIN Inventory inv ON p.product_id = inv.product_id
WHERE o.order_status = 'paid' 
  AND o.shipped_date IS NULL
ORDER BY o.promised_ship_date;
 
-- Finance revenue materialized view (refreshed nightly)
CREATE MATERIALIZED VIEW Revenue_By_Period AS
SELECT 
    DATE_TRUNC('day', o.order_date) AS period_day,
    DATE_TRUNC('week', o.order_date) AS period_week,
    DATE_TRUNC('month', o.order_date) AS period_month,
    SUM(o.total_amount) AS gross_revenue,
    SUM(o.total_amount) FILTER (WHERE o.order_status = 'refunded') AS refunds,
    SUM(o.total_amount) - SUM(o.total_amount) FILTER (WHERE o.order_status = 'refunded') AS net_revenue,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM Orders o
WHERE o.order_date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY DATE_TRUNC('day', o.order_date),
         DATE_TRUNC('week', o.order_date),
         DATE_TRUNC('month', o.order_date);