One of the most fertile grounds for disagreement between database professionals is the appropriate usage of views. Some analysts promote the liberal creation and usage of views, whereas others preach a more conservative approach. When properly implemented and managed, views can be fantastic tools that help to ease data access and simplify development. Although views are simple to create and implement, few organizations take a systematic and logical approach to view creation. And therein lies the controversy. A strategic and reasonable policy guiding the creation and maintenance of views is required to avoid a muddled and confused mish-mash of view usage. Basically, views are very useful when implemented wisely, but can be an administrative burden if implemented without planning.
All SQL access to relational tables results in another table. This is a requirement of the relational model. A view can be considered to be a logical table. A view is a "logical" representation of data that is "physically" stored in other tables (and perhaps other views as well). Views are defined using SQL and are represented internally to the DBMS by SELECT statements, not by stored data. Limitations on modification exist depending upon the type of view, though. Almost any SQL that can be issued natively can be coded into a view.
View Implementation Rules
After you understand view basics, you should develop guidelines for view creation in order to limit administrative burden. The following rules can be used to ensure that views are created in a responsible and useful manner at your shop. These rules were developed over a number of years as a result of reviewing implementations and working with views in many different environments. There are three basic view implementation rules:
- The View Usage Rule
- The Proliferation Avoidance Rule
- The View Synchronization Rule
The View Usage Rule: The first rule is simple: your view creation strategy should be goal-oriented. Views should be created only when they satisfy a specific application or business requirement. That requirement should be documented somewhere, preferably in a dictionary or repository. Although this rule may seem to be obvious, views are implemented at some shops without much thought as to how they will be used. This can cause the number of views that must be supported and maintained to continually expand until so many views exist that it is impossible to categorize their uses. And the time needed to maintain and administer the system increases as the number of views increase.
There are five basic uses for which views excel. These are: 1) to provide row and column level security, 2) to ensure efficient access paths, 3) to mask complexity from the user, 4) to ensure proper data derivation, and 5) to rename tables and/or columns.
The second rule is the proliferation avoidance rule. This rule is simple and to the point: do not needlessly create database objects that are not absolutely required. Whenever a database object is created additional entries are placed in the system catalog. Creating needless views causes "catalog clutter"...that is, entries in the catalog for objects which are not needed and/or are not used.
The proliferation avoidance rule is based on common sense. Why create something that is not needed? It just takes up space that could be used for something that is needed.
The third, and final view implementation rule is the view synchronization rule. The basic intention of this rule is to ensure that views are kept in sync with the base tables upon which they are based.
Whenever a change is made to a base table, all views that are dependent upon that base table should be analyzed to determine if the change impacts them. All views should remain logically pure. The view was created for a specific reason (see the View Usage Rule above). The view should therefore remain useful for that specific reason. This can only be accomplished by ensuring that all subsequent changes that are pertinent to a specified usage are made to all views that satisfy that usage.
For example, say a view was created to satisfy an access usage, such as a join between the employee table and the department table. If a column is added to the employee table specifying the employee's social security number, it should also be added to the EMP_DEPT view if it is pertinent to that view's specific use. Of course, the column can be added to the table immediately and to the view at the earliest convenience of the development team.
The synchronization rule requires that strict change impact analysis procedures be in place. Every change to a base table should trigger the usage of these procedures. Simple SQL queries can be created to assist in the change impact analysis. These queries should pinpoint QMF queries, application plans, and dynamic SQL users that could be using views affected by the specific changes to be implemented.
View synchronization is needed to support the view usage rule. By keeping views in sync with table changes the original purpose of the view is maintained.
Synopsis
Views are practical and helpful when implemented in a systematic and thoughtful manner. Hopefully this article has provided you with some food for thought pertaining to how views are implemented at your shop. And if you follow the guidelines contained in this article, in the end, all that will remain is a beautiful view!