Working with Relations, Key Fields, and Indexes

Your final tasks before adding records to the HRActions table are to determine the relationship between HRActions and an existing table in the database, assign a primary-key field, and add indexes to your table.

Establishing Relationships Between Tables

Many records in the HRActions table apply to a single employee whose record appears in the Employees table. A record is created in HRActions when the employee is hired, and a record is created for each quarterly and yearly performance review. Also, any changes made to bonuses or commissions other than as the result of a performance review are added, and employees might be terminated. Over time, the number of records in the HRActions table is likely to be greater by a factor of 10 or more than the number of records in the Employees table. Thus, the records in the new Personnel table have a many-to-one relationship with the records in the Employees table. Establishing the relationships between new and existing tables when you create a new table enables Access to enforce the relationship when you use the tables in queries, forms, pages, and reports.

Access requires that the two fields participating in the relationship have exactly the same data type. In the case of the Number field data type, the Field Size property of the two fields must be identical. You cannot, for example, create a relationship between an AutoNumber type field (which uses a Long Integer data type) and a field containing Byte, Integer, Single, Double, or Currency data. (You can create a relationship between fields having AutoNumber and Long Integer data types only.) On the other hand, Access lets you relate two tables by text fields of different lengths. Such a relationship, if created, can lead to strange behavior when you create queries, which is the subject of Part III, "Transforming Data with Queries and PivotTables." As a rule, the relationships between text fields should use fields of the same length.

Access uses a graphical Relationships window to display and create the relationships among tables in a database. To establish the relationships between two tables with Access's Relationships window, using the Employees and HRActions tables as an example, follow these steps:

1. Close the Employees and HRActions tables, and click the Relationships button of the main Access toolbar or choose Tools, Relationships to open the Relationships window (see Figure 5.24).

   Figure 5.24. The Relationships window for the Northwind.mdb database displays lines representing the one-to-many relationships between the original sample tables. The 1 symbol indicates the "one" side and the infinity () symbol indicates the "many" side of one-to-many relationships. Bold type identifies primary-key fields.

   

2. Click the Show Table button of the toolbar or choose Relationships, Show Table to open the Show Table dialog (see Figure 5.25).

   Figure 5.25. The Tables page of the Show Table dialog displays a list of all tables in the database. If you added the CustContacts and SuppContacts tables with the Table Wizard earlier in the chapter, these tables also appear in the list.

   

3. For this example, add the HRActions table to the Relationships window by double-clicking the HRActions entry in the Tables list, or by clicking the entry to select it and then clicking the Add button. Click the Close button.

4. Drag the bottom of the Relationships window to make room to display the fields of the HRActions table. Move the HRActions table object under the Products table object, and drag the bottom of the HRActions table object to expose all its fields.

5. The relationship of the HRActions table to the Employees table is based on the HRActions table's EmployeeID field (the foreign key) and the Employees table's EmployeeID field (the primary key). Click the Employees table's EmployeeID field and, holding the left mouse button down, drag it to the HRActions table's EmployeeID field. Release the mouse button to drop the field symbol on the EmployeeID field. The Edit Relationships dialog opens (see Figure 5.26).

   Figure 5.26. Establishing a relationship by dragging a field symbol from one table object to another opens the Edit Relationships dialog. By default, the name of the table with a primary-key field appears in the Table/Query list and the other table appears in the Related Table/Query list. In this case, Access automatically detects a one-to-many relationship.

   

6. Click the Join Type button to display the Join Properties dialog shown in Figure 5.27. You are creating a one-to-many join between the Employees table's EmployeeID field (the one side) and the HRActions table's EmployeeID field (the many side). You want to display all Employee records, even if one or more of these records don't have a corresponding record in HRActions. To do so, select option 2 in the Join Properties dialog. Click OK to close the dialog and return to the Relationships dialog.

   Figure 5.27. The Join Properties dialog lets you specify one of three types of one-to-many joins for the relationship. Option 1 is called an INNER JOIN by SQL, 2 is a LEFT OUTER JOIN, and 3 is a RIGHT OUTER JOIN.

   

7. The Edit Relationships dialog offers the Enforce Referential Integrity check box so that you can specify that Access perform validation testing and accept entries in the EmployeeID field that correspond only to values present in the Employees table's EmployeeID field. This process is called enforcing (or maintaining) referential integrity. (The following section discusses referential integrity.) The relationship between these two tables requires enforced referential integrity, so make sure to select this check box (see Figure 5.28).

   Figure 5.28. Marking the Enforce Referential Integrity check box ensures that values you enter in the HRActions table's EmployeeID field have corresponding values in the EmployeeID field of the Employees table.

   

   Note

   Access automatically maintains referential integrity of tables by providing check boxes you can mark to cause cascading updates to, and cascade deletions of, related records when the primary table changes. The following section discusses cascading updates and deletions. Access enables the cascade check boxes only if you elect to enforce referential integrity.

8. Click the Create button to accept the new relationship and display it in the Relationships window (see Figure 5.29).

   Figure 5.29. The Relationships window displays the newly added one-to-many relationship between the Employees and HRActions table.

   

9. Close the Relationships window and return to the Database window. Click Yes when asked to confirm that you want to save the layout changes to the Relationships diagram.

Access uses the relationship that you've created when you design queries and design forms, pages, and reports that use data in the HRActions table. Access 2000 introduced the File, Print Relationships menu command, which gives you a convenient means of printing the relationships. In Access 97, the Print Relationships command was provided as an add-in.

Cascading Updates and Deletions

Access's cascading deletion and cascading update options for tables with enforced referential integrity makes maintaining referential integrity easy: Just mark the Cascade Update Related Fields and Cascade Delete Related Records check boxes. In this case, marking the Cascade Update Related Fields check box is unnecessary, because you can't change the value of the AutoNumber EmployeeID field of the Employees table. You can delete records in the Employees table, so marking the Cascade Delete Related Records check box prevents orphan records records without a corresponding record from appearing in the Employees table in the HRActions table.

Note

Automatically enforcing referential integrity is usually, but not always, good database design practice. An example of where you would not want to employ cascade deletions is between the EmployeeID fields of the Orders and Employee tables. If you terminate an employee and then attempt to delete the employee's record, you might accidentally choose to delete the dependent records in the Orders table. Deleting records in the Orders table could have serious consequences from a marketing and accounting standpoint. (In practice, however, you probably would not delete a terminated employee's record.)

Selecting a Primary Key

You don't need to designate a primary-key field for a table that is never used as a primary table. A primary table is the term used in this book to designate a table that contains information representing an object, such as a person or an invoice, and only one record uniquely associated with that object. The HRActions table can qualify as a primary table because it identifies an object in this case, the equivalent of a paper form representing the outcome of two actions: initiation and approval. HRActions, however, probably wouldn't be used as a primary table in a relationship with another table.

Using a primary key field is a simple method of preventing the duplication of records in a table. Access requires that you specify a primary key if you want to create a one-to-one relationship or to update two or more tables at the same time. (Chapter 11, "Creating Multitable and Crosstab Queries," covers this subject.)

The primary table participating in relationships that you set with the Relationships window must have a primary key. Access considers a table without a primary-key field an oddity; therefore, when you make changes to the table and return to Design view, you might see a message stating that you haven't created a key field. (Access 2000 and later asks only once whether you want to add a primary-key field.) Related tables can have primary-key fields and often do. A primary-key field is useful for preventing the accidental addition of duplicate records.

You can create primary keys on more than one field. In the case of the HRActions table, a primary key that prevents duplicate records must consist of more than one field. If you establish the rule that no more than one type of personnel action for an employee can be scheduled for the same date, you can create a primary key that consists of the EmployeeID, ActionType, and ScheduledDate fields. When you create a primary key, Access creates an index based on the primary key.

To create a multiple-field primary key, called a composite primary key, and a primary-key index for the HRActions table, follow these steps:

1. Open the HRActions table in Design view.

2. Click the selection button for the EmployeeID field.

3. Ctrl+click the selection button for the ActionType field. In most instances, when you Ctrl+click a selection button, you can make multiple selections.

4. Ctrl+click the selection button for the ScheduledDate field.

5. Click the Primary Key toolbar button. Symbols of keys appear in each previously selected field, indicating their inclusion in the primary key.

6. To verify the sequence of the fields in the primary key, click the toolbar's Index button to display the Indexes dialog shown in Figure 5.30. Access automatically added the first EmployeeID index, which duplicates the Primary Key index on the EmployeeID field.

   Figure 5.30. The three fields of the HRActions table's composite primary key have indexes. You can delete the extra EmployeeID index.

   

7. To delete the extra EmployeeID index, select the index row and press Delete.

8. Close the Indexes dialog, and press Ctrl+S to save your table design changes.

You now have a multiple-field primary key and a corresponding index to the HRActions table that precludes the addition of records that duplicate records with the same primary key value.

Adding Indexes to Tables

Although Access creates an index on the primary key, you might want to create an index on some other field or fields in the table. Indexes speed searches for records that contain specific types of data. For example, you might want to find all HRActions that occurred in a given period and all quarterly reviews for all employees in ScheduledDate sequence. If you have many records in the table, an index speeds up the searching process. A disadvantage of multiple indexes is that data-entry operations are slowed by the time it takes to update the additional indexes. You can create as many as 32 indexes for each Jet table, and 5 of those can be of the multiple-field type. Each multiple-field index can include as many as 10 fields.

Tip

You should add only indexes you need to improve search performance. Each index you add slows the addition of new records, because adding a new record requires an addition to each index. Similarly, editing indexed fields is slower, because the edit updates the record and the index. When you create relationships between tables, Access automatically creates a hidden index on the related fields, if the index doesn't already exist. Hidden indexes count against the 32-index limit of each table. If an extra index appears in the Indexes dialog, as occurred for the HRActions table, see the "Extra Indexes Added by Access" item in the "Troubleshooting" section near the end of this chapter.

To create a single-field index for the HRActions table based on the EffectiveDate field, and a multiple-field index based on the ActionType and the ScheduledDate fields, follow these steps:

1. Select the EffectiveDate field by clicking its selection button.

2. Select the Indexed text box in the Field Properties window.

3. Open the Indexed drop-down list by clicking the arrow button or pressing Alt+down arrow (see Figure 5.31).

   Figure 5.31. You can add an index on a single field by setting the value of the Indexed property to Yes (Duplicates OK) or Yes (No Duplicates).

   

4. In this case, duplicate entries on the same date are likely, so select Yes (Duplicates OK) and close the list. You can create only a single-field index with this method.

5. Click the Indexes button. The Primary Key and EffectiveDate indexes already created appear in the list boxes. Type ActionTypeEffDate as the name of the composite index, and then select ActionType in the Field Name drop-down list. Move the caret to the next row of the Field Name column and select ScheduledDate to create a multiple-field index on these two fields (see Figure 5.32).

   Figure 5.32. You add multiple-field indexes in the Indexes dialog.

   

6. In the Ignore Nulls row of the Index Properties pane for the EffectiveDate field, select Yes so that records without an EffectiveDate value aren't included in the index.

7. Click the Datasheet View button, and click Yes to save your design changes.

You now have three indexes for the Primary Key table: the index automatically created for the primary key, the single-key index on EffectiveDate, and the multiple-key index on ActionType and ScheduledDate.