_edited_edited.png)
By Naveena Chandran
What are database views and why should we have them in our database?
A database view is a virtual table based on the result-set of a query statement. A view contains rows and columns, just like a real table. They don't store the data themselves but provide a way to represent the result of a query as if it were a table. Views are stored in a database as named queries and are used for frequently used complex queries. The availability of a view makes it possible to reuse the code providing a simplified and abstracted representation of the underlying data. The fields in a view are fields from one or more real tables in the database. We have to run a query first to get a view. This makes querying a view slower than querying directly from a table.
So naturally the question arises, Why don’t we use queries on just the tables? When should we decide to create a view in our database? Let us look at what are the advantages of using views in applications and reports
The figure below is a representation of a view created from the two tables A and B. When we need to use a representation of the combined data from these tables multiple times, creating a view comes handy for encapsulating complex queries
In certain situations views can provide many advantages over tables.
Views can represent a subset of the data contained in a table. Views can limit the degree of exposure of the underlying tables to the outer world. This is particularly useful when we are handling sensitive data and need to limit the exposure to different users. A given user may have permission to query the view, while denied access to the rest of the database table. Views are used commonly in representing data for reporting purposes. We know that since we have to run a query first to get the view, querying from a view is slower and that is a price we pay.
Let us look at some of the disadvantages of using views and then compare them with advantages
When the underlying query is complex or involves heavy computations. In some cases, using a view might result in slower query execution compared to directly querying the underlying tables. Views are dependent on the underlying tables. If the structure of the underlying tables changes, it might require modifications to the views as well. This can introduce maintenance challenges, especially in systems with frequent schema changes.
When dealing with nested or chained views, the complexity of the SQL queries can increase. This can make debugging and understanding the flow of data more challenging. It's important to document views and their relationships thoroughly. In situations where real-time data is not critical, using materialized views (which store the result set physically) might be more appropriate. However, materialized views come with their own set of considerations, such as maintenance and the need to refresh the data periodically.
But even with those above mentioned disadvantages, in some cases it is a good idea to use views.
When should we use Views?
Here are a few common use cases where views are generally used. The advantages in these scenarios are worth so as we can forgo the slight reduction in speed of performance
Manage permissions – Hide some of the columns or rows from a table. Views can be very helpful if you want a college grades app to only read data about your class students (“where subject=’AB001’) and only expose columns that do not include identifiable information (select grades and not student_id , first_name,last_name etc.). When we want to restrict the full access to table data and customize exposure of selected data. This increases the security of data.
Simplified Reporting: Views can be used to create simplified and aggregated representations of data for reporting purposes. This allows users to work with summarized data without having to understand the complexity of the underlying database structure.
Simplify complex queries. If you need to join several tables and deploy some business logic in a query, and then reuse these joins and business logic across multiple queries. As an example join your sales, profit, products, stores and staff tables to flatten your structure in a view and read from this view. In some cases, views can be optimized by precomputing certain aggregations or joins. This can result in improved query performance, as the view is stored temporarily in memory, reducing the need to execute complex queries repeatedly.
Mask connections to remote databases. In some environments we need to read from remote databases like Oracle database links or Microsoft SQL linked servers. A view can mask this from users/developers who won’t even know they are reading from a remote source.
Customize or beautify table and column names. Some systems have table and columns names that are hard to remember and use. You may want to create views that read the same data but rename column into nicer, readable names.
Union / Union all between different tables with a similar structure. This is a specific case of simplifying complex queries – if you are reading from multiple tables with the same structure, union and union all can help simplify your query and allow you to read from one view instead of several tables.
Compatibility: Views can help maintain compatibility with existing applications. When there are changes in the database schema, views can act as a buffer, allowing you to modify the underlying tables without impacting the existing queries.
In summary, when we need to limit the exposure of data or customize data for different users where speed is not the top priority database views are generally used.