Rules for Ensuring Beautiful Views

One of the most debated areas of relational database development has been when and how to create and use 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 useful for easing data access and simplifying development efforts. Although views are easy to create and use, few organizations take a systematic and logical approach to view creation. And therein lies the problem. A strategic and reasonable policy guiding the creation and maintenance of views is required to avoid a confused hodgepodge of dubious views. Basically, views are useful when implemented wisely, but can be an administrative burden if implemented without planning.

Let’s back up a minute and define what a view is.  First, remember that all SQL access to relational tables results in another table. This is called relational closure and it is a requirement of the relational model. A view can be considered a logical table. More completely, 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 (unless it is a materialized view, which is another thing altogether and beyond the scope of this particular post). Almost any SQL that can be issued natively can be coded into a view.

View Implementation Rules

After you understand the basics of views, you should develop guidelines for view creation. This will ensure a smooth implementation of views and minimize administrative overhead. The following rules should be used to ensure a responsible and useful view creation policy 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:

1. The View Usage Rule

2. The Proliferation Avoidance Rule

3. 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. Furthermore, the view purpose and DDL should be reviewed and approved by both the application team requesting the view and the DBA team that will support it.

Although this rule may seem to be an obvious one, some shops approach view creation without much forethought. This can cause the number of views that need to be supported and maintained to continually expand until so many views exist that it is impossible to categorize their purpose and current usage. Furthermore, the time needed to maintain and administer the system increases as the number of views increases.

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.

These are all reasonable uses for views

The second rule is the Proliferation Avoidance Rule. This rule is simple and to the point: do not needlessly create views (or, indeed, any 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 (if we followed 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 location, 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 any SQL in QMF queries, application packages, dynamic packages, and so on. Policies for informing users of the views to be impacted also need to be established before view changes can 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 post has provided you with some food for thought pertaining to how views are implemented at your shop. 

About craig@craigsmullins.com

I'm a data management strategist, researcher, and consultant with over three decades of experience in all facets of database systems development and implementation.
This entry was posted in views. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.