Database Design for Mere Mortals

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Chapter 12: Views

Michael J. Hernandez
DataTex Consulting Group

November 2002

Applies to:
    Microsoft® Access 2002

Summary: This article presents an excerpt from the book Database Design for Mere Mortals, by Michael J. Hernandez, published by Sybex.

Buy this book

Contents

What Are Views?
Anatomy of a View
Determining and Establishing Views
Case Study
Summary

—Fyodor Mikhaylovich Dostoyevsky

What Are Views?

As you learned in Chapter 3, a view is a virtual table that comprises the fields of one or more tables in the database; it can also include fields from other views. The reason a view is called a virtual table is because it does not store or contain data—it draws its data from the tables or views upon which it is based. Because a view does not store data, only its structure is saved in the database, and it is re-created each time it is accessed.

Note   The manner in which views are implemented is particular to each RDBMS, and thus it is beyond the scope of this book.

Views are valuable for the following reasons:

They can be used to work with data from two or more tables. During the database design process, you established a relationship between a particular pair of tables because the subjects they represent bear some connection to one another. A view provides the mechanism that allows you to work with the data from both tables simultaneously. Views also allow you to work with multiple tables, as in the case of a relationship that includes a linking table.

They reflect the most up-to-date information. Because a view does not store or contain its own data, it is re-created every time it is accessed. This means that the data displayed by the view exhibits the most current changes to the data in its base tables.

They can be fitted to the specific needs of an individual or group of individuals. Views can be set up to suit any set of requirements, such as providing the data for a particular report or providing a means of viewing specific data that is common to several departments within an organization.

They can be used to help enforce data integrity. You can define a validation view that works in the same manner as a validation table—its purpose is to provide a valid range of values for another field in the database.

They can be used for security or confidentiality purposes. You can determine which fields are available to a particular user or group of users by defining a view that excludes certain fields of the tables upon which the view is based.

If views are skillfully defined, they are an invaluable asset to a database application program once the database has been implemented in an RDBMS software program. Views can help you control the manner in which data is entered, modified, retrieved, and displayed.

Anatomy of a View

Views can be based on a single table, multiple tables, other views, or a combination of tables and views, and they can use the fields from the structures they comprise. The structures that compose the view are referred to as "base tables" or "base views."

Note   In order to avoid confusion or ambiguity, tables and views that are used to define a view will be referred to collectively as "base tables." The diagram illustrating the structure of a view will use the appropriate symbols to distinguish an actual table from a view.

There are three categories of views: data views, aggregate views, and validation views.

Data Views

Data views are used to examine and manipulate data from base tables. Depending on the purpose of the view, certain fields from the base tables are used to create the view's structure. For example, say you wanted a list of employee names and phone numbers to be available to everyone in the organization. You can construct an Employee Phone List view based on the Employees table using just the Employee ID, EmpFirst Name,EmpLast Name, and EmpPhone Number fields. Figure 1 shows a diagram of such a view. (Note the new symbol used to indicate a view.)

Figure 1. The Employee Phone List view

Every time the Employee Phone List view is accessed, it will be re-created and will reflect the latest changes made to the data in the Employees table. Figure 2 shows how the data for the view might be displayed.

Figure 2. Data displayed by the Employee Phone List view

Data in a single-table data view can be modified at any time; the changes to the data are passed through the view and into the base table. But you cannot modify the value of a field if such a change is disallowed by a particular field specification element or constrained by a business rule. For example, you cannot delete a last name from the data displayed by the view if the Null Support element of the Field Specification for the EmpLast Name field is set to "No Nulls."

You can create a data view that is based on two or more tables as well. The only requirement is that the tables used to create the view bear a relationship to each other. For example, say you want to create a view called "Class Roster" that shows the name of each class and the students that are currently registered to attend it. In order to create this view, you must use the tables shown in Figure 3. The tables are part of a database used to operate a local school.

Figure 3. Tables needed to create the Class Roster view

These tables contain the fields necessary to create the Class Roster view. They also fulfill the requirement that the tables used to create a multitable view must bear some relationship to each other. Create the Class Roster view by using the Class Name field from the Classes table and the StudFirst Name and StudLast Name fields from the Students table. The appropriate student names will appear for each class because Classes and Students are related (and therefore connected) through the Student Classes linking table. Figure 4 shows a view diagram for the Class Roster view. Note that no changes have been made to either of the base tables.

Figure 4. A view diagram for the Class Roster view

Every time the Class Roster view is accessed, it will be re-created and will draw the most current data from its base tables. A sample of the data displayed by this view is shown in Figure 5.

Figure 5. Data displayed by the Class Roster view

Although you can modify the values of most fields in a multitable view, you cannot modify the primary key field of any table comprised by the view. As in the case of a single-table view, modifications to the value of a field are governed by its field specifications and appropriate business rules.

You should have noticed that there is redundant data displayed in the Class Roster view. This is the result of merging a record from the Classes table with a record from the Students table; the number of times a particular Class Name is displayed is equal to the number of students that are registered to attend that class. This apparent redundancy is acceptable because the data is not being physically stored—rather, it is being drawn from the base tables where it is stored in accordance with the rules of proper database design. It is common for data from multitable views to be displayed in this fashion.

Another point you may have noted is that a view does not contain a primary key. It lacks a primary key because it is not a table; true tables store data and require a primary key to serve as a unique identifier for each of their records. Nonetheless, a primary key from a base table can be used in a view, and it retains its characteristics regardless of how it is being accessed. Therefore, if a primary key of a base table is included in the view, you must make certain that you treat it as you would a primary key within a table—its value cannot be Null, and its value must be unique. Make certain you do not have any primary key indicators within the view symbol when you diagram a view.

Aggregate Views

The purpose of this type of view is to display information that is the result of aggregating a particular set of data in a specific manner. An aggregate view includes one or more calculated fields that incorporate the functions that aggregate the data, and one or more data fields (drawn from the view's base tables) to group the aggregated data. The most common aggregate functions executed against a set of data include sum,average (arithmetic mean), minimum,maximum, and count.

For example, say you wanted to know how many students are registered for each class. Using the tables from the school example shown in Figure 3, you can create an aggregate view called Class Registration that provides this information. Use the Class Name field from the Classes table and create a calculated field called Total Students Registered, which counts the number of students per class. (When you work with a calculated field, make certain that you give it a name that is meaningful and will distinguish it from other calculated fields in the view.) The calculated field will use a count function to count the number of Student ID's in the Student Classes table that are associated with each Class ID in the Student Classes table. (Later, you'll learn how to document a view and record the expression used by a calculated field.) Figure 6 shows a diagram for the new Class Registration view.

Figure 6. View diagram for the Class Registration view

Every time the Class Registration view is accessed, it will be re-created and will access the most current data from the Classes and Student Classes tables. A sample of the data displayed by the view is shown in Figure 7.

Figure 7. Data displayed by the Class Registration view

Data cannot be modified in an aggregate view for two reasons: Calculated fields are noneditable, and the data fields are used for grouping purposes only. This type of view is best used as the basis of a report or as a means of providing various types of statistical information. You'll learn later that you can apply filtering criteria to this (or any) view in order to control and restrict the data displayed by the view.

Validation Views

Validation views are similar to validation tables in that they are used to implement data integrity. When the range of values of a particular field is limited by a business rule, you can enforce the constraint just as easily with a validation view as you can with a validation table. The difference between the two lies in their construction—you must explicitly define a validation table because it will store its own data, whereas a validation view is constructed with fields from base tables and draws its data from those tables as well. As with any other view, you can create a validation view from a single table, multiple tables, other views, or a combination of both tables and views. However, it's common that validation views are built from a single table and comprise only two or three fields, just as their validation table counterparts do.

As an example, say you are designing a database for a small contractor and you're working with the tables shown in Figure 8.

Figure 8. Tables from a Contractors database

Although there is a relationship between the Sub-Contractors table and Project Sub-Contractors table, you want to restrict the access users have to the data in the Sub-Contractors table. The only fields you believe users should have access to are SubContractor ID, SCName, SCPhone Number, and SCFAX Number fields. So you create an Approved Sub-Contractors view to provide the range of values for the SubContractor ID field in the Project Sub-Contractors table. A diagram of the tables, including the new view, is shown in Figure 9.

Figure 9. Revised Contractors database structure

The Approved Sub-Contractors view now provides the appropriate values for the SubContractor ID field in the Project Sub-Contractors table, while restricting users' access to the data in the Sub-Contractors table. Also, the relationship characteristics that exist for the Sub-Contractors table are still enforced through the Approved Sub-Contractors view.

Determining and Establishing Views

As you can see, views can be a substantial asset to the database. During this stage of the database design process, you'll define a fundamental set of views for the database. Your definition of views won't stop here—you'll create more views when you implement the database in an RDBMS and create a database application program. In these instances, views are used as a tool to support particular aspects of the implementation or application program. However, the views you create during the database design process will focus strictly on data-access and information-retrieval issues.

Working with Users and Management

You'll work once again with the organization's representative group of users and management to identify the types of views required by the organization. After identifying the views you will establish and document them, and then you and the group will review the views to make certain that they were properly defined.

Before you conduct your first meeting with the group, review the notes you've taken throughout the entire design process. Your objective is to get an idea of the types of views the organization might need. Because an organization spends a large amount of time producing and reading reports, focus on that aspect of your notes. You should also review the report samples you assembled during the analysis process.

When you and the group meet, consider the following points to help you identify view requirements:

Review your notes with the group. In many instances, an idea for a new or required view will be sparked by a topic currently under discussion. For example, someone may realize a need for a view during a discussion of mission objectives.

Review the data-entry, report, and presentation samples you gathered during the early stages of the design process. Examining these samples, especially summary-style reports, could easily illuminate the need for certain types of views.

Examine the tables and the subjects they represent. Some individuals in the group may identify the need for a view based solely on a specific subject. If someone mentions a subject such as employees, it may cause someone else to say: "We definitely need a view that restricts certain employee data for confidentiality reasons."

Analyze the table relationships. You'll most likely identify a number of multitable views that should be created for many of the relationships. Several of these views will coincide with views you identified for the report samples.

Study the business rules. As you already know, a rule that imposes a constraint on the range of values for a particular field can be enforced with a validation view.

You and the group should be able to identify a number of views by going over the items on this list. After you've identified as many of the required views as possible, your next task is to construct them.

Establishing Views

Create each view that you've identified using the appropriate tables and fields. Review the relationship diagrams to identify which tables and fields will be included in the view structure. When you've determined what you need, establish the view and record it in a view diagram.

For example, say you've determined that you can use a view for the report shown in Figure 10; the name of the new view will be Customer Call List.

Figure 10. Report sample requiring a view

The notes you've taken throughout the design process become useful once again. You reviewed this report during the analysis stage of the design process, and you've noted that this report represents information about customers and their orders; it is from the order data that you can determine when the last purchase was made by a particular customer. Now review the relationship diagram for the Customers and Orders tables; you'll use fields from these tables to create the Customer Call List view. The relationship diagram for these tables is shown in Figure 11.

Figure 11. Relationship diagram for the Customers and Orders tables

After examining the diagram, you determine you need to use five fields to build this view: CustFirst Name, CustLast Name, CustPhone Number, and CustCity from the Customers table, and Order Date from the Orders table. Now establish the Customer Call List view by assigning the fields to the view and then recording them in a view diagram. Your diagram should look like the one in Figure 12.

Figure 12. View diagram for the Customer Call List view

Using calculated fields where appropriate

Earlier in the database design process, you learned that tables cannot contain calculated fields for a number of good reasons. But one of the characteristics of a view that makes it so useful is that it can contain calculated fields. Remember that calculated fields will display the result of a concatenation, expression, or aggregate function; this makes them an extremely flexible structure to include in a view.

For example, consider the new Customer Call List view. Although you have the fields you need for the view, you'll have to make a minor modification in order for the view to display the appropriate data. As required in the example, this view must display the date of the last purchase made by each customer. In order to retrieve the proper date, you'll have to add a calculated field to the view. The calculation will be based on the Order Date field, and it will use the Maximum function (commonly known as Max()) to retrieve the correct date. Name the new field Last Purchase Date and add it to the Customer Call List view diagram. The expression that is used to retrieve the appropriate date is:

Max(Order Date)

Later in this section, you'll learn where and how to record this expression.

Another calculated field you might use in this view is one that concatenates CustFirst Name and CustLast Name so that the complete customer name is displayed as "Hernandez, Michael." Create a calculated field called Customer Name and add it to the Customer Call List view diagram. "'CustLast Name & ', ' & CustFirst Name'" is the expression you'll use to display the name in the desired manner, and you'll soon properly record the concatenation as well. Because you're using the Customer Name calculated field to display the customer name, remove the CustFirst Name and CustLast Name fields from the view.

When you've completed the changes to the view diagram, it should look like the one shown in Figure 13.

Figure 13. Revised view diagram for the Customer Call List view

As you've just learned, calculated fields can be used to enhance the information presented by a view, so they are quite an asset. Earlier in this chapter, you also learned that calculated fields are particularly crucial in aggregate views. A good rule of thumb to follow with calculated fields is to use them if they will provide pertinent and meaningful information, or if they will enhance the manner in which the data is used by the view.

One of the best sources for calculated fields that can be useful in a view is the calculated field list you compiled earlier in the database design process. Refer to Chapter 6. You'll find that many of the fields on this list can be used in the views you create for the database.

Imposing criteria to filter the data

Views have another characteristic that makes them extremely useful: You can impose criteria against one or more fields in the view to filter the records it displays. For example, say that the Customer Call List view included the CustState field. Although the view would continue to display the set of records it did before, you would see the state each customer lives in as well. However, assume you wanted the view to show a very specific set of records—for instance, just the records of those customers who live in the state of Washington. You could set a criterion against the CustState field that would filter the data so that only records of customers from Washington would be displayed by the view.

In database work, the word "criterion" refers to an expression that is tested against the value in a particular field. A record is included in the view if the value of the field meets the criterion. Continuing the example, you would use the following expression to filter the records for the Customer Call List view:

CustState = "WA"

Now the view will display only customers from Washington. If you wanted to filter the records further to show only those customers who live in specific cities, add a criterion such as

CustCity In ("Bellevue," "Olympia," "Redmond," "Seattle," 
      "Spokane," "Tacoma")

The view will now display Washington State customers who live in the cities specified in the expression. You may wonder why both criteria are necessary—the criterion for the CustCity field should retrieve the appropriate records by itself. The trouble is that many cities are named for other cities, so that cities in two or three different states could have the same name. For example, there is a "Portland, Oregon," and a "Portland, Maine," both named after Portland, England. The point to remember is that you must use your best judgment when establishing criteria for a view—use the minimal criteria that will cause the view to display the records you require.

When you use a criterion in a view, you must make certain that the field being tested in the criterion is included in the view's structure. If the field is not included in the view, you have no way of imposing the criterion. This is an important point to remember because it is true when you logically define a view as well as when you implement the view in an RDBMS.

The one problem with applying a filter to a view is that there is no way to indicate it on a view diagram. Therefore you must record it on a View Specifications sheet.

Using a View Specifications sheet to record the view

Each view diagram you create must be accompanied by a View Specifications sheet. It is on this sheet that you will record the characteristics of the view. The View Specifications sheet contains the following items:

Name. Here is where you indicate the name of the view. Before you record the name, however, test it against the guidelines for creating table names you learned in Chapter 7. These guidelines govern the naming of views as well, with one exception: The name of a view can implicitly or explicitly identify more than one subject. This is due to the fact that views can be constructed from two or more tables, so they do, indeed, represent more than one subject.

Type. Indicate here whether the view type is data, aggregate, or validation.

Base Tables. For this item you indicate the names of the tables used to construct the view. Although the tables are shown on the view diagram, they are also included here as a matter of convenience. Field names are not included in the View Specifications sheet because they are better recorded and displayed in the view diagram.

Calculated Field Expressions. Here is where you record the expressions for the calculated fields you included in the view. As you record the name of the calculated field, test it against the guidelines for creating field names you learned in Chapter 7. Calculated field names are governed by these guidelines with two exceptions: You can implicitly or explicitly identify more than one characteristic in a name, and you can use the plural form of the name. But when you name a calculated field, it is still desirable to use the singular form of the name if possible.

Filters. This item is used to record the criteria that will be used to filter the records displayed by the view. Here you'll record both the field being tested and the expression used to test it.

Note   When you fill out the Calculated Field Expressions and Filters sections of a View Specifications sheet, use the expressions with which you are most familiar. You'll modify them as necessary when you implement the database in an RDBMS.

Fill out a View Specifications sheet for each view you create, and attach the sheet to the proper view diagram. Both of these items will serve to fully document the view. Figure 14 shows a completed View Specifications sheet for the Customer Call List view. (Keep in mind that the view has been updated to include the CustState field.)

Figure 14. Completed View Specifications sheet for the Customer Call List view

Reviewing the Documentation for Each View

Once you've completed the task of establishing and documenting each view, review your views once more—ensuring the quality of the information provided by each view is well worth the effort. As you review each view, keep the following points in mind:

Make certain that the view is properly constructed. Think about the information the view should provide. Are you establishing the correct type of view for the required information? Are the appropriate base tables used to construct the view? Are all the necessary fields included in the view's structure?

Make certain that the calculated fields you've created are suitable for the view. Do they provide pertinent and meaningful information? Do they serve to enhance the manner in which the data is displayed?

Make certain that the filters will retrieve the required records. First of all, do you need a filter for this view? If the answer is yes, do you know exactly which records you want the view to display? Does the filter work correctly?

Above all, make certain that you have a view diagram and View Specifications sheet for each view. This documentation will be very useful when you finally implement the database in an RDBMS.

Case Study

Your work on Mike's database is finally nearing an end. You meet with Mike and his staff to determine whether there is a need to establish views for the database. The agenda you've set up for the meeting involves the following steps:

1. Review the notes you've compiled during the design process.
2. Review each of the various samples you gathered during the early stages of the design process.
3. Examine the subjects represented by the tables in the database.
4. Analyze the table relationships.
5. Review and study the business rules.

As the meeting progresses, you identify several views that you need to construct, including a Preferred Customers view and a Vendor Product Count view. The first view will provide the name and phone number of each customer who has a "Preferred" status, and the second view will provide information on the total number of different products each vendor supplies.

You use the Customers table to establish the Preferred Customers view. The only fields you need for the view are CustomerID, CustFirst Name, CustLast Name, CustHome Phone, and Status. But before you construct the view, Mike asks if there's any way to display the first name and last name together. You respond that it can be done, so you create a calculated field called Customer Name that concatenates both of the fields together. This field replaces the CustFirst Name and CustLast Name fields. Figure 15 shows the view diagram for the Preferred Customers view.

Figure 15. View diagram for the Preferred Customers view

After you create the view diagram, you make note of the expression that will be used to filter the data for the view, which is

Status = "Preferred."

Then you complete the View Specifications sheet for the Preferred Customers view, as shown in Figure 16.

Figure 16. The View Specifications sheet for the Preferred Customers view

Now you create the Vendor Product Count view using the Vendors and Products tables as the base tables. You use the Vendor Name field from the Vendors table to display the names of the vendors. Next you create a calculated field called Product Count to display the total number of products each vendor supplies; your calculated field uses this expression to calculate the total:

Count(Product Name).

You then create a diagram for the view, which is shown in Figure 17.

Figure 17. View diagram for the Vendor Product Count view

After determining that a filter is unnecessary for this view, you finish documenting the view by completing the View Specifications sheet shown in Figure 18.

Figure 18. View Specifications sheet for the Vendor Product Count view

You then repeat this process for every view you've identified for Mike's database.

Summary

We began this chapter with a definition of a view, and you learned that it is a virtual table that does not contain or store data. Views are useful for several reasons, such as that they let you work with data from multiple tables, help enforce data integrity, and keep data secure or confidential.

We then discussed the three types of views: data, aggregate, and validation. You learned that each type of view can be based on one or more tables, other views, or a combination of both. Views are re-created every time they are accessed and will display the most current data from the base tables. As you now know, there must be relationships between tables in a multitable view, and the characteristics of those relationships are carried forth through the view. Data can be modified in most views, and all modifications to data are passed through the view to the base tables. You also learned that validation views work in the same manner as validation tables do, but they have distinct advantages over validation tables; for instance, they can incorporate data from multiple tables.

The chapter then continues with a discussion on determining and establishing views for the database. Here you learned several specific points to keep in mind while you work with users and management to identify the organization's view requirements. Next we discussed how to establish a view, and you learned how to create a view diagram to document the view. Now you know how to select fields from the base tables and assign them to the view.

We then discussed how to use calculated fields in a view. You learned that they are used to provide pertinent information and enhance how the data is displayed in the view. You also learned that calculated fields are especially crucial in aggregate views. Each calculated field uses an expression to derive the value it displays. Next you saw how to apply a filter to a view in order to retrieve a specific set of records. Records will be displayed by the view only if they meet the criterion imposed against one or more fields in the view. Each criterion is framed as an expression and is used to test the values of a particular field.

The chapter closes with a discussion of the View Specifications sheet. Here you learned how to document the characteristics of the view, such as its name and type. You also learned about the items that compose the view specifications sheet and how they are used to record the view's characteristics.