Understanding the Purpose of Creating a View in a Database

1. Overview

Databases offer various ways to organize and access data efficiently, even when dealing with complex data structures and relationships. The basic mechanism for working with database information is through tables, but that has some limitations. Often, this isn’t always the most efficient or secure approach. One concept that aligns well with both performance optimization and data abstraction is the database view.

In this tutorial, we’ll explore the main purposes and benefits of creating views in a database. We’ll use the University schema for code examples throughout the tutorial.

2. What Is a View?

Before looking into the reason for creating views, let’s clarify what a view is. Simply put, a view is a named query that retrieves data from one or more tables. In particular, it acts as a virtual table that enables users to interact with it as if it were a regular table. However, views don’t store any data themselves; instead, they derive their data from one or more underlying tables.

We have three main types of views:

• simple views: based on a single table, they typically do the job of filtering or rearranging data
• complex views: combine data from multiple tables and often involve using joins and aggregations
• materialized views: precomputed views that store the query results on disk with some improvement in performance but require time-to-time refreshing

Notably, the syntax for creating views is generally the same across popular database management systems like MySQL, PostgreSQL, and SQL Server. An exception is materialized views which we can use in database management systems like PostgreSQL and Oracle.

3. Purposes of Views

Creating views serves several important purposes in database management.

3.1. Simplifying Complex Queries

One of the primary purposes of creating views is to simplify complex queries. This means that views can help encapsulate complex queries to make them more readable and maintainable. We hide the complexities of the underlying tables and joins under views thereby providing a cleaner interface for users to interact with the data. This is particularly useful when dealing with large databases.

For example, let’s say we want to retrieve the names and enrollment dates of students in the Computer Science department. Thus, we can create a view to replace writing a complex join query each time we want to do this:

CREATE VIEW cs_students AS
SELECT s.name, s.enrollment_date
FROM Student s
WHERE s.id IN (
    SELECT r.student_id
    FROM Registration r
    JOIN Course c ON r.course_id = c.id
    WHERE c.department_id = (
        SELECT id FROM Department WHERE name = 'Computer Science'
    )
);

With this view in place, we can simply query the cs_students view to access the information in it anytime we need it:

SELECT * FROM cs_students;
+-----------------+-----------------+
| name            | enrollment_date |
+-----------------+-----------------+
| John Liu        | 2020-01-15      |
| Rita Ora        | 2020-01-15      |
| Philip Lose     | 2020-01-15      |
| Samantha Prabhu | 2020-01-15      |
| Rose Rit        | 2020-01-15      |
| Phellum Luis    | 2020-01-15      |
| Peter Liu       | 2021-01-15      |
| Ritu Raj        | 2021-01-15      |
| Piu Liu         | 2021-01-15      |
| Roni Roto       | 2021-01-15      |
| Julia Roberts   | 2022-01-15      |
| Pollards Grey   | 2022-01-15      |
+-----------------+-----------------+
12 rows in set (0.01 sec)

Thus, we simplify complex queries and enable users who may not have any interest or reason to have in-depth knowledge of the database schema to access the query.

3.2. Enhancing Security

Another important purpose of creating views is to enhance security. When we decide to restrict access to sensitive data, we can achieve this by using views and presenting only a subset of the available information. By doing this as an administrator, we can control what data can be seen by different user groups.

For instance, we can create a view that shows only the non-sensitive columns of the Student table:

CREATE VIEW student_info AS
SELECT id, name, enrollment_date, graduation_date
FROM Student;

With this view, users with access to the student_info view won’t be able to see columns like national_id or birth_date:

SELECT * FROM student_info;
+------+-----------------+-----------------+-----------------+
| id   | name            | enrollment_date | graduation_date |
+------+-----------------+-----------------+-----------------+
| 1001 | John Liu        | 2020-01-15      | 2024-06-15      |
| 1003 | Rita Ora        | 2020-01-15      | 2024-06-15      |
| 1007 | Philip Lose     | 2020-01-15      | 2024-06-15      |
| 1010 | Samantha Prabhu | 2020-01-15      | 2024-06-15      |
| 1011 | Vikas Jain      | 2020-01-15      | NULL            |
| 1101 | Jia Grey        | 2020-01-15      | 2024-06-15      |
| 1103 | Rose Rit        | 2020-01-15      | NULL            |
| 1107 | Phellum Luis    | 2020-01-15      | 2024-06-15      |
| 1110 | Albert Decosta  | 2020-01-15      | 2024-06-15      |
| 1111 | Vikram Kohli    | 2020-01-15      | 2024-06-15      |
| 1607 | Peter Liu       | 2021-01-15      | 2025-06-15      |
| 1610 | Ritu Raj        | 2021-01-15      | 2025-06-15      |
| 1617 | Philip Mohan    | 2021-01-15      | 2025-06-15      |
| 1619 | Sam Roberts     | 2021-01-15      | 2025-06-15      |
...
| 2008 | Julia Roberts   | 2022-01-15      | 2025-06-15      |
| 2009 | Pollards Grey   | 2022-01-15      | 2025-06-15      |
| 2017 | Potu Singh      | 2022-01-15      | NULL            |
+------+-----------------+-----------------+-----------------+
25 rows in set (0.00 sec)

By applying such security measures, we can reduce the risk of data breaches or unauthorized access.

3.3. Providing Data Abstraction

Views also serve the purpose of providing data abstraction. In this case, what we mean by data abstraction is hiding the complexity of the database schema by presenting data in a more friendly format. Doing this might enable users to focus on the information they need without worrying about the underlying table structure. This can make it easier for users to understand and work with the data.

As an example, let’s create a view that combines student and course information:

CREATE VIEW student_courses AS
SELECT s.name AS student_name, c.name AS course_name, c.credits
FROM Student s
JOIN Registration r ON s.id = r.student_id
JOIN Course c ON r.course_id = c.id;

SELECT * FROM student_courses;
+-----------------+---------------------------------------------+---------+
| student_name    | course_name                                 | credits |
+-----------------+---------------------------------------------+---------+
| John Liu        | Introduction to Operating Systems           |       7 |
| John Liu        | Introduction to Databases                   |       7 |
| John Liu        | Relational Databases                        |       7 |
| Rita Ora        | Introduction to Databases                   |       7 |
| Rita Ora        | Relational Databases                        |       7 |
| Rita Ora        | Introduction to Structured Query Language   |       7 |
| Philip Lose     | Introduction to Data Structures             |       7 |
| Philip Lose     | Introduction to Algorithms                  |       7 |
| Philip Lose     | Introduction to Databases                   |       7 |
| Samantha Prabhu | Statistical Machine Learning                |       7 |
| Samantha Prabhu | Theory of Machine Learning                  |       7 |
| Samantha Prabhu | Introduction to Data Structures             |       7 |
...
+-----------------+---------------------------------------------+---------+
147 rows in set (0.00 sec)

As a result, the student_courses view provides a simplified interface for users to access student and course data without needing to understand the relationships between the tables.

3.4. Ensuring Data Consistency

Views can also help ensure data consistency by centralizing data transformations and calculations. When we define these operations in a view, we reduce the risk of errors and inconsistencies that may arise when multiple users perform the same transformations independently. Usually, this is particularly useful when dealing with complex calculations or business logic that need to be applied consistently across the database.

For example, let’s create a view that calculates the average GPA of students in each department:

CREATE VIEW department_avg_gpa AS
SELECT d.name AS department_name, AVG(s.gpa) AS avg_gpa
FROM Student s
JOIN Registration r ON s.id = r.student_id
JOIN Course c ON r.course_id = c.id
JOIN Department d ON c.department_id = d.id
GROUP BY d.name;

With this view, users can access the average GPA for each department at any point without having to write the calculations separately:

SELECT * FROM department_avg_gpa;
+--------------------------------+--------------------+
| department_name                | avg_gpa            |
+--------------------------------+--------------------+
| Computer Science               |  4.072586187000932 |
| Electronics and Communications |  4.350000023841858 |
| Mechanical Engineering         | 3.8986666997273765 |
| Civil Engineering              | 3.8711764531977035 |
| Mathematics                    | 3.8841935588467504 |
+--------------------------------+--------------------+

If we centralize data transformations and calculations in views this way, we ensure that the same logic is applied every time across the database and reduce the risk of errors and inconsistencies.

4. Creating Views in SQL

Now that we understand the purposes of creating views, let’s look at the syntax of creating it:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition
WHERE condition;

Since we’ve been looking at other types of views in previous sections of the article, we can give an example of using a materialized view in PostgreSQL:

CREATE MATERIALIZED VIEW department_student_count AS
SELECT d.name AS department_name, COUNT(s.id) AS student_count
FROM Department d
LEFT JOIN Course c ON d.id = c.department_id
LEFT JOIN Registration r ON c.id = r.course_id
LEFT JOIN Student s ON r.student_id = s.id
GROUP BY d.name;

Notably, this view encapsulates the number of students in each department.

Now, let’s retrieve the department with more than 10 students as an example of running a query using this view:

SELECT department_name
FROM department_student_count
WHERE student_count > 10;

      department_name       
---------------------------
 Computer Science
 Mechanical Engineering
 Mathematics
 Civil Engineering
 Electronics and Communications
(5 rows)

It’s important to note that the data in a materialized view isn’t automatically updated when the underlying tables change. Therefore, we would refresh the data manually when necessary:

REFRESH MATERIALIZED VIEW department_student_count;

We use the REFRESH MATERIALIZED VIEW to refresh the data manually.

5. Conclusion

In this article, we looked into the benefits of database views in SQL and explored how to use materialized views in PostgreSQL.

In conclusion, database views simplify complex queries, enhance security, provide data abstraction, and can potentially improve performance.