Views are a useful tool in the database trade. Essentially, a view is nothing more than a SELECT statement returning data from the DBMS. The property of relational closure dictates that the result of a SELECT statement is logically, more-or-less, a table. A view is special in that it has a database-known name and, in being named, a view can be referenced within the database.

With a few exceptions, anything one can do in a SELECT statement one can also do within a view. An end-user writes a SELECT statement to retrieve data from the view as if the view were an actual table inside the database. The SQL written to return data from a view versus the SQL written against an actual table is indistinguishable. Therefore, as far as coding SQL SELECT statements go, there is no difference between a table and a view. When a query is executed against a view, the DBMS integrates the logic of the view’s SELECT statement with the end-user’s query. The integrated result from this merger determines the retrieval of data from the actual tables.

Helpful Views

Views are extraordinarily helpful when the database must support frequently used queries that contain complex logic. By formalizing the complexity of the join and restriction logic within a view, end-users need not worry about correctly re-coding the same logic over and over again. Also, if such logic must be altered, the change can be accomplished in a single place, the view, and all users of that view will automatically be using the updated logic. Some organizations have a practice that all end-user access is managed via views.

Why might limiting end-user access to views be a good thing? Two main reasons exist for establishing such limitations. First, by creating views that are used as the access layer for end-users, one establishes an environment that effectively moves closer to the original ANSI SPARC three-layer database architecture in which the views are the logical model seen by data users. Second - and the actual intention of the three-layer architecture - a greater level of independence is created between the physical tables and the logical application/user needs. Changes can be made to tables, but as long as a view can be defined that provides a consistent result set, all users are protected from having to change as the tables change. While many tool vendors attempt to train people to consider the logical and physical model as being one and the same thing (which is very effective at confusing anyone new to the industry), such attempts are only self-serving for those vendors. When the logical model and physical model are considered a single merged schema, the resulting limited data perspective suffers from the limitations of individual DBMS’ physical file management and optimizer peccadilloes.

While some rationale for limiting all end-user access to views does exist, such an approach may not be well-suited for everyone. In an environment that limits access to views, the IT group of the involved organization must be mature enough to support the management of all the necessary views. This means that many views will reflect the original version of the established base tables. And the organization must be able to effectively manage change to all such views, so that view alterations do not cause a bottleneck to application development or solution deployment.

Generally, the ability to provide effective view administration is channeled through an active data repository that automates some aspects of the view’s lifecycle. The organization committed to this kind of view-based approach is also committed to maintaining metadata and a significant level of data administration. Thoughts held by some individuals, about views being a performance problem simply by their existence, should be seriously questioned. Views are best used to ease and enhance the flexibility of database usage. When views are an administrative nightmare, then either the choice of view use or the existing procedures for managing database objects need close scrutiny.

About the Author

Todd Schraml is senior data architect and manager of ETL at Innovative Health Strategies, Inc. He can be reached at tschraml@ihsiq.com.