Introduction to Database View

Summary: in this tutorial, you will learn about a new database object called database view. We will discuss about the advantages and disadvantages of using database views.

A database view is a virtual table or logical table which is defined as a SQL SELECT query with joins. Because a database view is similar to a database table, which consists of rows and columns, so you can query data against it. Most database management systems, including MySQL, allows you to update data in the underlying tables through the database view with some prerequisites.

A database view is dynamic because it is not related to the physical schema. The database systemstores database views as a SQL SELECT statement with joins. When the data of the tables changes, the view reflects that changes as well.

Advantages of database view

The following are advantages of using database views.

• A database view allows you to simplify complex queries: a database view is defined by an SQL statement that associates with many underlying tables. You can use database view to hide the complexity of underlying tables to the end-users and external applications. Through a database view, you only have to use simple SQL statements instead of complex ones with many joins.
• A database view helps limit data access to specific users. You may not want a subset of sensitive data can be queryable by all users. You can use database views to expose only non-sensitive data to a specific group of users.
• A database view provides extra security layer. Security is a vital part of any relational database management system. Database views provides extra security for a database management system. A database view allows you to create only read-only view to expose read-only data to specific users. Users can only retrieve data in read-only view but cannot update it.
• A database view enables computed columns. A database table should not have calculated columns however a database view should. Suppose in the orderDetails table you havequantityOrder (the number of ordered products) and priceEach (price per product item) columns. However the orderDetails table does not have computed column to store total sales for each line item of the order. If it has, the database schema would not be a good design. In this case, you can create a computed column named total, which is a product of quantityOrderand priceEach to store the computed result. When you query data from the database view, the data of the computed column is calculated on fly.
• Database view enables backward compatibility. Suppose you have a central database, which many applications are using it. One day you decided to redesign the database to adapt with the new business requirements. You remove some tables and create several new tables, and you don’t want the changes affect other applications. In this scenario, you can create database views with the same schema as the legacy tables that you’ve removed.

Disadvantages of database view

Besides the advantages above, there are several disadvantages of using database views:

• Performance: querying data from a database view can be slow especially if the view is created based on other views.
• Tables dependency: you create view based on underlying tables of the a database. Whenever you change the structure of those tables that view associates with, you have to change the view as well.