Views - Learning Module

Loading content...

0/241

View Benefits: Why Views Are Indispensable

The Strategic Value of Views

Knowing how to create views is just the beginning. The real power lies in understanding when and why to use them. Views aren't merely syntactic convenience—they're a strategic tool that addresses fundamental challenges in database design, security, and maintenance.

Consider a large enterprise with hundreds of applications, thousands of users, and terabytes of sensitive data. How do you:

Ensure developers only see data they need?
Protect schema changes from breaking applications?
Simplify complex queries that get repeated constantly?
Maintain consistency when business logic must be applied universally?

Views answer all of these questions. This page explores the strategic benefits that make views indispensable in production database systems.

What You Will Learn

By the end of this page, you will understand how views provide security through row and column filtering, abstraction through schema insulation, simplification through query encapsulation, and maintainability through centralized logic.

Security Through Data Subsetting

Views are one of the most powerful mechanisms for implementing row-level and column-level security in relational databases. Instead of granting users direct access to tables containing sensitive data, you create views that expose only what each user group needs.

Column-level security:

A table might contain columns that some users should never see—salaries, social security numbers, medical records. A view can project only safe columns:

-- Base table contains sensitive data
CREATE TABLE employees (
    id INT, name VARCHAR(100), email VARCHAR(100),
    salary DECIMAL(10,2), ssn VARCHAR(11), medical_notes TEXT
);

-- Public view hides sensitive columns
CREATE VIEW v_employee_directory AS
    SELECT id, name, email
    FROM employees;
-- Grant SELECT on view, not table

row_column_security.sql
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
-- Row-level security: Users see only their own data
CREATE VIEW v_my_records AS
    SELECT *
    FROM documents
    WHERE owner_id = CURRENT_USER_ID()  -- Session function
WITH CHECK OPTION;
 
-- Combined row + column security
CREATE VIEW v_department_employees AS
    SELECT e.id, e.name, e.email, e.department_id
    FROM employees e
    WHERE e.department_id = GET_USER_DEPARTMENT();
-- Hides salary AND restricts to same department
 
-- Multi-level access views
-- Junior staff view
CREATE VIEW v_projects_junior AS
    SELECT id, name, status, assigned_to
    FROM projects
    WHERE classification = 'public';
 
-- Senior staff view (includes budget info)
CREATE VIEW v_projects_senior AS
    SELECT id, name, status, assigned_to, budget, cost_to_date
    FROM projects
    WHERE classification IN ('public', 'internal');
 
-- Executive view (everything)
CREATE VIEW v_projects_executive AS
    SELECT *
    FROM projects;
 
-- Grant appropriate view to each role
GRANT SELECT ON v_projects_junior TO role_junior;
GRANT SELECT ON v_projects_senior TO role_senior;
GRANT SELECT ON v_projects_executive TO role_executive;

Defense in Depth

Views provide application-level security, not storage-level security. A DBA can still query base tables directly. For truly sensitive data, combine view-based access control with encryption, auditing, and administrative access controls.

Abstraction and Schema Insulation

Database schemas evolve. Tables get split for performance, columns get renamed for clarity, relationships get restructured for new requirements. Without abstraction, every schema change ripples through every application—a maintenance nightmare.

Views create a stable interface between applications and the underlying schema. When the schema changes, you update the view definition rather than modifying hundreds of application queries.

The insulation architecture:

Applications → Views (stable interface) → Base Tables (can evolve)

schema_insulation.sql
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
-- SCENARIO: Legacy application expects 'customer' table with specific columns
-- But we've restructured data into normalized tables
 
-- Old structure (what apps expect):
-- customer(id, name, address, city, state, zip, phone, email)
 
-- New normalized structure:
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE addresses (
    id INT PRIMARY KEY, customer_id INT, 
    street VARCHAR(200), city VARCHAR(100), 
    state VARCHAR(50), postal_code VARCHAR(20)
);
CREATE TABLE contact_info (
    id INT PRIMARY KEY, customer_id INT,
    phone VARCHAR(20), email VARCHAR(100)
);
 
-- Compatibility view: Apps continue querying 'v_customer'
CREATE VIEW v_customer AS
    SELECT 
        c.id,
        c.name,
        a.street AS address,
        a.city,
        a.state,
        a.postal_code AS zip,
        ci.phone,
        ci.email
    FROM customers c
    LEFT JOIN addresses a ON c.id = a.customer_id AND a.is_primary = TRUE
    LEFT JOIN contact_info ci ON c.id = ci.customer_id AND ci.is_primary = TRUE;
 
-- Applications query the view without modification
SELECT name, city, email FROM v_customer WHERE id = 12345;
-- Works exactly as before the restructuring!

Real-world insulation scenarios:

Schema Change	Without Views	With Views
Split table for performance	Update all queries	Update view only
Rename column for clarity	Update all queries	Add alias in view
Add partitioning	Potentially break queries	View remains stable
Migrate to new schema version	Massive refactor	View provides compatibility
Archive to different storage	Complex union logic everywhere	Encapsulate in view

The migration pattern:

A common pattern for schema migrations uses views to maintain backward compatibility:

Create new schema structure alongside old
Create views matching old table names pointing to new structure
Migrate applications gradually to query new structure directly
Drop compatibility views when all apps are updated
Drop old tables

Views as API Contracts

Think of views as database-level API contracts. Just as REST APIs provide stability while backends evolve, views provide stability while schemas evolve. The view's column list is your API; the underlying implementation can change freely.

Query Simplification

Complex queries are a fact of life in sophisticated databases. Multi-table joins, subqueries, aggregations, and business logic expressions can create SQL that spans dozens of lines. When these queries are repeated across applications, the complexity proliferates—becoming harder to understand, modify, and maintain.

Views address this by encapsulating complexity. A 50-line join becomes a simple table reference. The complexity exists once, in the view definition, rather than being copied to every application.

without_view.sql
-- Every report needs this complex query
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    c.email,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total,
    o.order_date,
    o.status,
    s.company_name AS shipper,
    e.name AS sales_rep
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
LEFT JOIN shippers s ON o.shipper_id = s.id
LEFT JOIN employees e ON o.employee_id = e.id
WHERE o.deleted_at IS NULL
  AND c.active = TRUE;
 
-- Copy this EVERYWHERE...

with_view.sql

-- Define complexity once
CREATE VIEW v_order_details AS
    SELECT 
        o.id AS order_id,
        c.name AS customer_name,
        c.email,
        p.name AS product_name,
        oi.quantity,
        oi.unit_price,
        oi.quantity * oi.unit_price 
            AS line_total,
        o.order_date,
        o.status,
        s.company_name AS shipper,
        e.name AS sales_rep
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    LEFT JOIN shippers s ON o.shipper_id = s.id
    LEFT JOIN employees e ON o.employee_id = e.id
    WHERE o.deleted_at IS NULL
      AND c.active = TRUE;
 
-- Query simply:
SELECT * FROM v_order_details
WHERE order_date > '2024-01-01';

Layered view architecture:

For extremely complex domains, views can be layered—building increasingly sophisticated derived data:

-- Layer 1: Join customers with their primary address
CREATE VIEW v_customers_located AS ...

-- Layer 2: Add aggregated order metrics
CREATE VIEW v_customer_with_orders AS
    SELECT cl.*, order_count, total_spent
    FROM v_customers_located cl
    LEFT JOIN (SELECT ...) order_metrics ...

-- Layer 3: Add customer scoring
CREATE VIEW v_customer_scored AS
    SELECT *, calculate_score(...) AS customer_score
    FROM v_customer_with_orders;

Each layer adds derived data, and downstream queries select from the layer with the abstraction they need.

Layering Trade-offs

Deep view nesting can sometimes challenge the query optimizer, especially with complex aggregations across layers. Profile queries against deeply nested views. If performance degrades, consider materializing intermediate layers or flattening the structure.

Centralized Business Logic

Business logic in SQL is inevitable—computed columns, derived statuses, data transformations, filtering rules. When this logic is duplicated across applications, it diverges over time. One team uses status = 'active', another uses status IN ('active', 'pending'), a third uses is_active = true. Inconsistency breeds bugs.

Views centralize business logic in a single source of truth. The definition of 'active customer' or 'profitable product' exists once, and all consumers inherit it automatically.

centralized_logic.sql
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
-- Business logic: What makes a customer "active"?
-- - Has logged in within 90 days
-- - Has at least one non-cancelled order
-- - Account is not suspended
-- - Email is verified
 
-- WITHOUT centralization: Every team implements differently...
 
-- WITH centralization: Single authoritative definition
CREATE VIEW v_active_customers AS
    SELECT c.*
    FROM customers c
    WHERE c.last_login_date > CURRENT_DATE - INTERVAL '90 days'
      AND c.account_status != 'suspended'
      AND c.email_verified = TRUE
      AND EXISTS (
          SELECT 1 FROM orders o 
          WHERE o.customer_id = c.id 
            AND o.status != 'cancelled'
      );
 
-- All teams query v_active_customers
-- Logic changes? Update ONE view definition.
 
-- Complex derived columns: Centralize calculations
CREATE VIEW v_products_with_metrics AS
    SELECT 
        p.*,
        -- Profit margin calculation
        (p.sale_price - p.cost) / p.sale_price * 100 AS margin_percent,
        -- Stock status derivation
        CASE 
            WHEN p.quantity_on_hand = 0 THEN 'out_of_stock'
            WHEN p.quantity_on_hand < p.reorder_level THEN 'low_stock'
            WHEN p.quantity_on_hand > p.max_stock_level THEN 'overstock'
            ELSE 'normal'
        END AS stock_status,
        -- Days until stockout estimate
        CASE 
            WHEN p.avg_daily_sales > 0 
            THEN p.quantity_on_hand / p.avg_daily_sales
            ELSE NULL
        END AS days_until_stockout
    FROM products p;

The single source of truth principle:

When business definitions exist in one place:

Consistency: All applications see the same definition
Auditability: You can trace exactly what 'active customer' means
Agility: Changing the definition updates all consumers automatically
Testing: Business logic can be tested at the database level

Common candidates for centralization:

Status derivations (lead stage, order status, health indicators)
Calculated metrics (profit margins, conversion rates, scores)
Filter definitions (active, eligible, qualified, valid)
Temporal logic (current period, year-to-date, trailing 30 days)

Version Views for Major Logic Changes

If business logic changes significantly, consider versioning: v_active_customers_v2. Migrate applications gradually, then deprecate the old view. This prevents breaking changes while allowing evolution.

Data Transformation and Formatting

Raw data rarely matches what applications need. Dates need formatting, names need concatenation, codes need translation, values need calculations. Views can perform these transformations, presenting data in application-ready format.

Why transform in views rather than applications?

Performance: Transformations happen at the database, reducing data transfer
Consistency: Every application gets the same transformations
Simplicity: Application code deals with ready-to-use data
Centralization: Transform logic defined once, used everywhere

data_transformation.sql
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
-- Transform raw data into application-ready format
CREATE VIEW v_customer_display AS
    SELECT 
        c.id,
        -- Name formatting
        CONCAT(c.first_name, ' ', c.last_name) AS full_name,
        UPPER(c.last_name) || ', ' || c.first_name AS formal_name,
        
        -- Date formatting
        TO_CHAR(c.created_at, 'Mon DD, YYYY') AS member_since,
        EXTRACT(YEAR FROM AGE(c.created_at))::INT AS years_as_member,
        
        -- Phone formatting
        '(' || SUBSTRING(c.phone, 1, 3) || ') ' ||
        SUBSTRING(c.phone, 4, 3) || '-' ||
        SUBSTRING(c.phone, 7, 4) AS formatted_phone,
        
        -- Status translation (code to display value)
        CASE c.status
            WHEN 'A' THEN 'Active'
            WHEN 'I' THEN 'Inactive'
            WHEN 'S' THEN 'Suspended'
            ELSE 'Unknown'
        END AS status_display,
        
        -- Currency formatting
        TO_CHAR(c.account_balance, 'FM$999,999,999.00') AS balance_display,
        
        -- Boolean to icon/text
        CASE WHEN c.email_verified THEN '✓' ELSE '✗' END AS verified_icon,
        
        -- Derived display categories
        CASE 
            WHEN c.total_purchases > 10000 THEN 'Platinum'
            WHEN c.total_purchases > 5000 THEN 'Gold'
            WHEN c.total_purchases > 1000 THEN 'Silver'
            ELSE 'Bronze'
        END AS tier
        
    FROM customers c;
 
-- Application simply queries:
SELECT full_name, member_since, tier, balance_display
FROM v_customer_display
WHERE id = 12345;

Locale Considerations

Database-level formatting may not handle locale-specific requirements (date formats, currency symbols). For global applications, consider passing localization parameters at query time or handling final formatting in the application tier while keeping logic in the database.

Backward Compatibility Support

Database evolution is challenging when existing applications depend on specific table structures. Views provide a powerful mechanism for maintaining backward compatibility during transitions.

Common scenarios requiring compatibility:

Column renames that would break applications
Table splits for performance optimization
Data type changes
Schema reorganization
Migration from legacy systems

backward_compatibility.sql
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
-- SCENARIO 1: Column rename with compatibility
-- Old column: 'cust_name' → New column: 'customer_full_name'
 
-- Update table
ALTER TABLE customers RENAME COLUMN cust_name TO customer_full_name;
 
-- Compatibility view preserving old name
CREATE VIEW v_customers_legacy AS
    SELECT 
        id,
        customer_full_name AS cust_name,  -- Old apps see old name
        email,
        created_at
    FROM customers;
 
-- SCENARIO 2: Table split with unified view
-- Was: products(id, name, description, price, quantity, supplier_id, ...)
-- Now: products(id, name, price) + product_details(id, description) 
--      + inventory(product_id, quantity)
 
CREATE VIEW v_products_unified AS
    SELECT 
        p.id, p.name, p.price,
        pd.description,
        i.quantity,
        p.supplier_id
    FROM products p
    JOIN product_details pd ON p.id = pd.product_id
    LEFT JOIN inventory i ON p.id = i.product_id;
 
-- Old queries against 'products' structure still work against view
 
-- SCENARIO 3: Data type migration
-- Old: status VARCHAR(10) with values 'active', 'inactive'
-- New: status_id INT with foreign key to statuses table
 
CREATE VIEW v_records_compat AS
    SELECT 
        r.id,
        r.name,
        s.code AS status,  -- Returns 'active'/'inactive' strings
        r.created_at
    FROM records r
    JOIN statuses s ON r.status_id = s.id;

The deprecation pattern:

Phase 1: Create new structure alongside old
Phase 2: Create compatibility views matching old interface  
Phase 3: Route legacy apps to compatibility views
Phase 4: Migrate apps to new structure (gradual)
Phase 5: Monitor compatibility view usage
Phase 6: Drop compatibility views when unused
Phase 7: Drop old structures

This pattern allows zero-downtime migrations with gradual transition, which is essential for large systems with many dependent applications.

Track Compatibility View Usage

Use database auditing or query logs to track which applications still query compatibility views. This data drives deprecation decisions and helps identify applications that haven't migrated.

Views in Reporting Scenarios

Reporting often involves complex aggregations over large datasets. Views play a crucial role in organizing reporting logic and, when materialized (covered later), providing dramatic performance improvements.

Standard views for reporting:

Even without materialization, views organize reporting queries:

Pre-defined aggregation levels (daily, weekly, monthly)
Consistent metric calculations across reports
Reusable report building blocks

reporting_views.sql
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
-- Reporting view hierarchy for sales data
 
-- Level 1: Base facts with computed fields
CREATE VIEW v_sales_base AS
    SELECT 
        s.id,
        s.sale_date,
        s.quantity,
        s.unit_price,
        s.quantity * s.unit_price AS line_total,
        s.product_id,
        p.category_id,
        s.customer_id,
        c.region_id
    FROM sales s
    JOIN products p ON s.product_id = p.id
    JOIN customers c ON s.customer_id = c.id;
 
-- Level 2: Daily aggregations
CREATE VIEW v_sales_daily AS
    SELECT 
        sale_date,
        region_id,
        category_id,
        COUNT(*) AS transaction_count,
        SUM(quantity) AS units_sold,
        SUM(line_total) AS revenue,
        AVG(line_total) AS avg_order_value
    FROM v_sales_base
    GROUP BY sale_date, region_id, category_id;
 
-- Level 3: Monthly rollup
CREATE VIEW v_sales_monthly AS
    SELECT 
        DATE_TRUNC('month', sale_date) AS month,
        region_id,
        category_id,
        SUM(transaction_count) AS transactions,
        SUM(units_sold) AS units,
        SUM(revenue) AS revenue,
        SUM(revenue) / NULLIF(SUM(units_sold), 0) AS avg_price
    FROM v_sales_daily
    GROUP BY DATE_TRUNC('month', sale_date), region_id, category_id;
 
-- Reports query the appropriate level
-- Daily dashboard:
SELECT * FROM v_sales_daily WHERE sale_date = CURRENT_DATE - 1;
 
-- Monthly trend:
SELECT month, SUM(revenue) FROM v_sales_monthly GROUP BY month ORDER BY month;

Standard Views Recompute Every Time

Standard views over large datasets recompute on every query. For heavy reporting loads, consider materialized views (next topic) which pre-compute and store results, trading freshness for speed.

Summary: Strategic View Benefits

Views are far more than query shortcuts—they're strategic tools that address fundamental challenges in database systems. Let's consolidate the key benefits:

Key Benefits Summary

•Security through subsetting — Row-level and column-level access control via filtered views.
•Schema insulation — Stable application interfaces despite underlying schema evolution.
•Query simplification — Encapsulate complex joins and logic into simple table references.
•Centralized business logic — Single source of truth for calculations and definitions.
•Data transformation — Present application-ready data directly from the database.
•Backward compatibility — Smooth migrations with compatibility views preserving old interfaces.
•Reporting organization — Layered views providing appropriate aggregation levels.

What's next:

Not all views can be modified—some are read-only while others support INSERT, UPDATE, and DELETE. The next page explores updatable views: what makes a view updatable, the restrictions that apply, and how to enable modifications on complex views.

Page Complete

You now understand the strategic value of views—security, abstraction, simplification, and maintainability. Next, we'll explore which views support data modification and how to work with updatable views.