Alberto Méndez

Database views, what are they and how to use them?

Feb 15, 2025

This article is part of the series:

Databases

As databases grow larger and more complex, managing and accessing data efficiently becomes increasingly challenging. This is where database views come into play. A view acts as a “window” into the data stored in your database. Think of it as a predefined query that displays a subset of data from one or more tables. Unlike traditional tables, views don’t physically store data; they simply serve as an abstraction layer.

What are database views?

Picture an e-commerce database with multiple tables: customers, orders, products, payments, etc. If a business analyst needs to see a summary of customer purchases without dealing with the complex database structure, a view can streamline this task by presenting the information in a clear, accessible format.

Use cases

Database views are powerful tools that excel in various scenarios:

  • Simplifying Complex Queries: Instead of writing lengthy SQL queries repeatedly, you can create a view that encapsulates the logic and makes it reusable.
  • Security and Access Control: Views can restrict access to sensitive data, showing only necessary information without exposing confidential internal data.
  • Application Compatibility: When an application depends on a specific data structure, views can act as a “bridge” between old and new structures without modifying the application code.
  • Performance Optimization: While regular views don’t store data, some database engines offer materialized views that cache query results for improved performance in intensive use cases.

Despite their advantages, views aren’t a silver bullet. They can impact performance if misused, especially when running on heavy queries or large datasets. Additionally, some views don’t allow direct data modifications, which might be limiting in certain scenarios.

To put these concepts into practice, let’s work with a realistic dataset. We’ll create a database with a typical e-commerce structure and use views to extract useful information efficiently.

Our goal is to demonstrate how views can simplify queries, optimize data access, and facilitate managing key information like order status, best-selling products, and payment tracking. Here’s our database structure:

  • customers – Customer information
  • orders – Customer orders
  • order_items – Products included in each order
  • products – Available store products
  • payments – Order payments
E-commerce database entity-relationship diagram showing customers, orders, order_items, and payments tables with their relationships. The orders table links to customers via customer_id, while order_items and payments relate to orders through order_id.
E-commerce database entity-relationship diagram showing customers, orders, order_items, and payments tables with their relationships. The orders table links to customers via customer_id, while order_items and payments relate to orders through order_id.

Setting up a PostgreSQL database

Let’s start by spinning up a PostgreSQL database using Docker:


    docker run -d \
      --name ecommerce_db \
      -e POSTGRES_USER=admin \
      -e POSTGRES_PASSWORD=admin \
      -e POSTGRES_DB=ecommerce \
      -p 5432:5432 \
      -v pgdata:/var/lib/postgresql/data \
      postgres:latest

You can connect to it using your preferred method, whether it’s a GUI tool like DBeaver or the command line. I personally prefer graphical interfaces when they offer the essential features, so we’ll use DBeaver. Simply add a new PostgreSQL connection with these details:

  • Host: localhost
  • Port: 5432
  • Database: ecommerce
  • User: admin
  • Password: admin

    -- Create the customers table
    CREATE TABLE customers (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL
    );
    
    -- Create the orders table
    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        customer_id INT REFERENCES customers(id) ON DELETE CASCADE,
        total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    
    -- Create the products table
    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(150) NOT NULL,
        price DECIMAL(10,2) NOT NULL,
        stock INT NOT NULL DEFAULT 0
    );
    
    -- Create the order items table
    CREATE TABLE order_items (
        id SERIAL PRIMARY KEY,
        order_id INT REFERENCES orders(id) ON DELETE CASCADE,
        product_id INT REFERENCES products(id) ON DELETE CASCADE,
        quantity INT NOT NULL CHECK (quantity > 0)
    );
    
    -- Create the payments table
    CREATE TABLE payments (
        id SERIAL PRIMARY KEY,
        order_id INT REFERENCES orders(id) ON DELETE CASCADE,
        amount DECIMAL(10,2) NOT NULL,
        payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

Populating the database

We can use any AI tool to help us generate sample data INSERT statements. I used ChatGPT for this purpose, and here’s what it suggested:


    -- Insert customers (50 records)
    
      INSERT INTO customers (name, email)
        SELECT 'Customer ' || i,
               'customer' || i || '@email.com'
        FROM generate_series(1, 50) AS i;
    
    -- Insert products (30 records)
    
      INSERT INTO products (name, price, stock)
        SELECT 'Product ' || i,
               ROUND((RANDOM() * 90 + 10):: NUMERIC, 2), -- Price between 10 and 100
               (RANDOM() * 100)::INT  -- Stock between 0 and 100
        FROM generate_series(1, 30) AS i;
    
    -- Insert orders (100 records, distributed among customers)
    
      INSERT INTO orders (customer_id, total, order_date)
        SELECT (RANDOM() * 49 + 1)::INT,  -- Random customer between 1 and 50
               0.00,  -- Will be updated later with the actual total
               NOW() - INTERVAL '1 day' * (RANDOM() * 60)::INT -- Dates within the last 60 days
        FROM generate_series(1, 100);
    
    -- Insert order items (200 records, randomly assigned to products and orders)
    
      INSERT INTO order_items (order_id, product_id, quantity)
        SELECT (RANDOM() * 99 + 1)::INT,  -- Random order between 1 and 100
               (RANDOM() * 29 + 1)::INT,  -- Random product between 1 and 30
               (RANDOM() * 5 + 1)::INT    -- Quantity between 1 and 5
        FROM generate_series(1, 200);
    
    -- Calculate the total for each order and update it
    
      UPDATE orders
        SET total = (SELECT COALESCE(SUM(p.price * oi.quantity), 0)
           FROM order_items oi
                  JOIN products p ON oi.product_id = p.id
           WHERE oi.order_id = orders.id);
    
    -- Insert payments (80 random payments, some orders will remain unpaid)
    
      INSERT INTO payments (order_id, amount, payment_date)
        SELECT (RANDOM() * 99 + 1)::INT,  -- Random order between 1 and 100
               (RANDOM() * 200 + 20)::NUMERIC(10,2),  -- Amount between 20 and 220
               NOW() - INTERVAL '1 day' * (RANDOM() * 60)::INT -- Dates within the last 60 days
        FROM generate_series(1, 80);

Creating PostgreSQL views

Now that our database is populated with sample data, let’s create some useful views. One common e-commerce requirement is getting order information along with customer details without writing multiple JOINs every time.

Customer orders view


  CREATE VIEW customer_orders AS
  SELECT o.id    AS order_id,
         c.name  AS customer_name,
         c.email AS customer_email,
         o.total,
         o.order_date
  FROM orders o
           JOIN customers c ON o.customer_id = c.id;

This view, named customer_orders, combines relevant order information with associated customer data. Instead of writing a complex query each time we need this information, we can simply query the view:

    
    SELECT * FROM customer_orders;

We might also want to analyze our store’s most popular products by creating a view showing total units sold per product.

Top selling products view


  CREATE VIEW top_selling_products AS
  SELECT 
      p.id AS product_id,
      p.name AS product_name,
      SUM(oi.quantity) AS total_sold
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  GROUP BY p.id, p.name
  ORDER BY total_sold DESC;

    SELECT * FROM top_selling_products;

To track our store’s financial performance, let’s create a view for monthly revenue analysis.

Monthly revenue view


  CREATE VIEW monthly_revenue AS
  SELECT 
      TO_CHAR(payment_date, 'YYYY-MM') AS month,
      SUM(amount) AS total_revenue
  FROM payments
  GROUP BY month
  ORDER BY month DESC;

    SELECT * FROM monthly_revenue;

Some orders might be created without associated payments. This view helps identify unpaid orders.

Unpaid orders view


  CREATE VIEW unpaid_orders AS
  SELECT 
      o.id AS order_id,
      c.name AS customer_name,
      o.total,
      o.order_date
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  LEFT JOIN payments p ON o.id = p.order_id
  WHERE p.id IS NULL;

  SELECT * FROM unpaid_orders;

Finally, let’s create a comprehensive view that shows detailed information about unpaid orders including customer data:

Detailed unpaid orders view


  CREATE VIEW detailed_unpaid_orders AS
  SELECT 
      o.id AS order_id,
      c.name AS customer_name,
      c.email AS customer_email,
      o.order_date,
      CURRENT_DATE - o.order_date AS days_since_order,
      o.total AS order_total,
      COALESCE(SUM(p.amount), 0) AS total_paid,
      o.total - COALESCE(SUM(p.amount), 0) AS amount_due,
      MAX(p.payment_date) AS last_payment_date,
      CASE 
          WHEN SUM(p.amount) IS NULL THEN 'Pendiente'
          WHEN SUM(p.amount) < o.total THEN 'Parcialmente Pagado'
          ELSE 'Pagado'
      END AS order_status
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  LEFT JOIN payments p ON o.id = p.order_id
  GROUP BY o.id, c.name, c.email, o.order_date;

A simple query to the detailed_unpaid_orders view will give us all the necessary information about unpaid orders:

    
    SELECT * FROM detailed_unpaid_orders;
DBeaver showing the ecommerce database structure. The SELECT * FROM unpaid_orders query execution displays a list of unpaid orders with customer names, total amounts, and order dates. The left panel shows the database explorer with customers, orders, order_items, payments, and products tables.
DBeaver showing the ecommerce database structure. The SELECT * FROM unpaid_orders query execution displays a list of unpaid orders with customer names, total amounts, and order dates. The left panel shows the database explorer with customers, orders, order_items, payments, and products tables.

This approach not only simplifies database queries but also serves as a template for generating reports in our application. Sometimes, it’s more efficient to let the database handle data processing rather than complicating application logic.

As mentioned earlier, not everything should be handled through views, and not all processing should happen in the database. Depending on your application’s needs and circumstances, you’ll need to choose the most appropriate approach considering all factors involved.

Conclusion

Whether you’re new to database views or just needed a refresher, I hope this article has helped clarify their purpose and usage. I encourage you to explore this topic further and share your experiences. Above all, Happy Coding!

Alberto Méndez

Related posts

That may interest you

Our work

Concept to value

Stay up to date with the latest technology news and our weekly podcast. BetaZetaNews
Communicate effortlessly in multiple languages with a single button thanks to BuddyLingo. BuddyLingo
AI-generated adventures where your choices will create endless possibilities. LAIA
Create amazing stories with every roll, your imagination is your only guide! Spark
Keep track of the hours dedicated to your job and/or projects. Own your time WorkIO