Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

What Are Views?

As you learned in Chapter 3, a view is a virtual table composed of fields from one or more tables in the database; it can also include fields from other views. The tables and views that comprise a given view are known as the view's base tables . A view is "virtual" because it draws data from base tables rather than storing data on its own. In fact, the only information about a view that is stored in the database is its structure; the RDBMS rebuilds and "repopulates" the view every time you access the view in some manner. Many major RDBMS programs support views, but some (such as Microsoft Access) refer to them as saved queries . Your specific RDBMS program will determine whether you refer to this object as a query or a view.

Note

Although every major database vendor supports the view I've just described, several vendors are now supporting what is known as an indexed (or materialized ) view. An indexed view is different from a regular view in that it does store data, and its fields can be indexed to improve the speed at which the RDBMS processes the view's data. A full discussion of indexed views is beyond the scope of this book because it is a vendor-specific implementation issue. However, you should research this topic further if you are working with a client/server or mainframe RDBMS program.

Views enable you to see the information in your database from many different aspects, providing you with a great amount of flexibility when you work with your data. You can create views in a variety of ways, and they are especially useful when you base them on multiple related tables.

There are several reasons why you should define and use views in your database.

• You can use them to work with data from multiple tables simultaneously . During the database-design process, you established relationships between various pairs of tables bearing one-to-many or many-to-many relationships to each other. (Recall that you resolved the many-to-many relationships via linking tables.) A view provides the mechanism that allows you to work with data from two or more related tables simultaneously.

• They reflect the most current information. Because the RDBMS rebuilds and repopulates the view every time you access it, the information displayed by the view exhibits the most recent changes to the data in its base tables.

• You can customize them to the specific needs of an individual or group of individuals. You can build a view to suit any set of requirements, such as providing the data for a particular report or providing a means of examining specific information that is common to several departments within an organization.

• You can use them to help enforce data integrity. You can define a validation view that works in the same manner as a validation tableits purpose is to provide a valid range of values for a given field in the database.

• You can use them for security or confidentiality purposes. You can determine what data is available to a particular user or group of users by defining a view on select fields from the view's base tables.

Define your views carefully and skillfully, and they will become a valuable asset after you've implemented the database within your RDBMS.