Views in relational database
Should we use views in relational database?
What are views ?
Views are virtual tables in a relational database that are derived from two or more underlying tables or views. Views are not physically stored in the database but provide an alternative way to query in the database.
In the above diagram, there are three tables:
Customers Table: Stores customer-related information.
Orders Table: Stores order-related information.
Shipment Table: Stores shipment related to order information.
We want to get all shipments delivered to the customer so we will create a view to get the required result.
CREATE VIEW customer_shipment_view AS SELECT customers.customer_id, shipments.shipment_id, shipments.shipment_date, orders.order_id FROM customers INNER JOIN orders on customers.id = orders.customer_id INNER JOIN shipments on orders.order_id = shipments.order_id;
A view is defined by a query that selects specific columns and rows from the underlying tables or views based on certain conditions or criteria. The query can perform joins, filters, aggregations, and other operations to transform and present the data in a meaningful way.
Why to use views?
Data Abstraction: Views are a way to store complex queries so they represent a simplified and tailored view of data to the user. They hide the complex query, join, filters and aggregation hence creating an abstraction of the data represented to users. In the above example, we created a customer shipment view that represents all the shipments for a customer and abstracts the query to get all shipments for a customer.
Modularization and Encapsulation: Views encapsulated complex queries within themselves. We can define reusable views that encapsulate commonly used data transformations, filters or joins. This promotes code reusability and maintainability by centralizing complex logic into a single view.
Security and Access Control: Views can be used to enforce security and access control mechanisms. By granting users access to views instead of underlying tables, you can restrict their visibility and control what data they can see or modify. Views can also hide sensitive columns or rows from certain users or user roles. In the above example, we do not want to share the customer name with the users so we did not include it in the query, hence any users who have access to only this view would not be able to see the customer name.
Reduces Overhead: Views also serve as a way to materialize frequently used or resource-intensive queries, reducing the overhead of executing them repeatedly. In the above example, we have defined a customer shipment view which we can use with any other table to view. It reduces the overhead of writing the same query again and again.
Why not to use views?
With so many advantages of views, there are some disadvantages as well.
Performance Trade-offs: While views can improve query performance in some cases, there can also be performance trade-offs. Precomputing views for complex queries or frequently used aggregations may consume additional storage space, and maintaining up-to-date views can introduce overhead during data updates.
Complexity in Query Optimization: Optimizing queries involving views can be challenging for the database optimizer. The presence of multiple views or complex view hierarchies can complicate query optimization and lead to suboptimal query plans. Careful consideration and tuning may be required to ensure the efficient execution of queries involving views.
Conclusion
While views are powerful concept in relational database. We should carefully think before deciding to go with the views as they comes with many benefits as well as potential tradeoff. Views should be designed and managed thoughtfully to ensure they align with your database structure, performance goals, security needs, and data access patterns.
Let's Connect
Don't miss out on future articles like this one! Follow me on the socials listed below and Hashnode to stay updated.