A relationship exists between two tables when a key field from one table is matched to a key field in another table. The fields in both tables usually have the same name, data type, and size. Relationships are a necessary by-product of the data normalization process. Data normalization is covered in Chapter 1, "Introduction to Access Development." It is the process of eliminating duplicate information from your system by splitting information into several tables, each containing a unique value. Although data normalization brings many benefits, you need to relate the tables in your system so that your users can view the data in the system as a single entity. After you have defined relationships between tables, you can build queries, forms, and reports that combine information from multiple tables. In this way, you can reap all the benefits of data normalization while ensuring that your system provides users with all the information they need.
Three types of relationships can exist between tables in a database: One-to-Many, One-to-One, and Many-to-Many. Setting up the proper type of relationship between two tables in your database is imperative. The right type of relationship between two tables ensures
The reasons behind these benefits are covered throughout this chapter. Before you can understand the benefits of relationships, you must understand the types of relationships available.
A One-to-Many relationship is by far the most common type of relationship. In a One-to-Many relationship, a record in one table can have many related records in another table. A common example is a relationship set up between a Customers table and an Orders table. For each customer in the Customers table, you want more than one order in the Orders table. On the other hand, each order in the Orders table can belong to only one customer. The Customers table is on the one side of the relationship, and the Orders table is on the many side. For this relationship to be implemented, the field joining the two tables on the one side of the relationship must be unique. In the example of customers and orders, the CustomerID field that joins the two tables must be unique within the Customers table. If more than one customer in the Customers table has the same Customer ID, it is not clear which customer belongs to an order in the Orders table. For this reason, the field that joins the two tables on the one side of the One-to-Many relationship must either be a primary key or have a unique index. In almost all cases, the field relating the two tables is the primary key of the table on the one side of the relationship. The field relating the two tables on the many side of the relationship is called a foreign key.
In a One-to-One relationship, each record in the table on the one side of the relationship can have only one matching record in the table on the many side of the relationship. This relationship is not common and is used only in special circumstances. Usually, if you have set up a One-to-One relationship, you should have combined the fields in both tables into one table. You should create a One-to-One relationship for one of the following reasons:
The maximum number of fields allowed in an Access table is 255. There are very few reasons why a table should ever have more than 255 fields. In fact, before you even get close to 255 fields, you should take a close look at the design of your system. On the rare occasion that having more than 255 fields is appropriate, you can simulate a single table by moving some of the fields to a second table and creating a One-to-One relationship between the two tables.
The second reason to separate into two tables data that logically would belong in the same table involves security. An example would be a table containing employee information. Certain information such as employee name, address, city, state, ZIP code, home phone, and office extension might need to be accessible by many users of the system. Other fields, including the hire date, salary, birth date, and salary grade, might be highly confidential. Field-level security is not available in Access. You can simulate field-level security using a special attribute of queries called Run with Owners Permissions. This feature is covered in Chapter 12, "Advanced Query Concepts." The alternative to this method is to place all the fields that can be accessed by all users in one table and the highly confidential fields in another. Only certain users are given access to the table containing the confidential fields.
The last reason to define One-to-One relationships is when certain fields in a table will be utilized for only a relatively small subset of records. An example would be an Employee table and a Vesting table. Certain fields would be required only for employees who are vested. A small percentage of the company's employees might be vested. It would not be efficient in terms of performance or disk space to place all the fields containing information about vesting in the Employee table. This would be especially true if the vesting information required a large volume of fields. By breaking the information into two tables and creating a One-to-One relationship between them, you can reduce disk-space requirements and improve performance. This improvement is particularly pronounced if the Employee table happens to be large.
In a Many-to-Many relationship, records in both tables have matching records in the other table. A Many-to-Many relationship cannot be defined in Access; you must develop this type of relationship by adding a table called a Junction table. The Junction table is related to each of the two tables as One-to-Many relationships. An example is an Orders table and a Products table. Each order probably contains multiple products, and each product is found on many different orders. The solution is to create a third table called Order Details. The Order Details table is related to the Orders table in a One-to-Many relationship based on the OrderID field. It is related to the Products table in a One-To-Many relationship based on the ProductID field.
Relationships between Access tables are established in the Relationships window (see Figure 4.1). To open the Relationships window, click Relationships on the toolbar with the Database window active. Looking at the Relationships window, you can see the type of relationship that exists for each table. All the One-to-Many relationships defined in a database are represented with a join line. If referential integrity has been enforced between the tables involved in a One-to-Many relationship, the join line between the tables appears with the number 1 on the one side of the relationship and an infinity symbol on the many side of the relationship.
Figure 4.1. The Relationships window allows you to view, add, modify, and remove relationships between tables.
To establish a relationship between two tables, follow these steps:
Figure 4.2. The Relationships dialog allows you to view and modify the relationships between the tables in a database.
You need to remember a few important things when establishing relationships. If you are not aware of these important "gotchas," you could find yourself in some pretty hairy situations.
[ic:example]Create a new database and add a table called tblCustomers, another called tblOrders, and another called tblOrderDetails. Each table should have the following fields:
tblCustomers: CustomerID, CompanyName, Address, City, State, ZipCode
tblOrders: OrderID, CustomerID, OrderDate, ShipVIA
tblOrderDetails: OrderID, LineNumber, ItemID, Quantity, Price
In the tblCustomers table, set the CustomerID field as the primary key. Set the size of the field to 5. All other fields can be left with their default properties.
In the tblOrders table, set OrderID to the AutoNumber field type. Make the OrderID the Primary Key field. Set the length of the CustomerID field to 5. Add an index to this field. Set the field type of the OrderDate field to Date.
In the tblOrderDetails table, set the field type of the OrderID field to Number, and make sure that the size is Long Integer. Add an index to this field. Set the type of the LineNumber field to Number with a size of Long Integer. The primary key of the table should be based on the combination of the OrderID and LineNumber fields. The ItemID and Quantity fields should be Number type with a size of Long Integer. The Price field should be Currency type.
To open the Relationships window, click Relationships on the toolbar with the Database window active. With the tblCustomers table in the Show Table dialog selected, hold down your Shift key and click to select the tblOrderDetails table. Click Add. All three tables should be added to the Relationships window. Click Close. Click and drag from the CustomerID field in the tblCustomers table to the CustomerID field in the tblOrders table. When the Relationships dialog appears, click Create. Repeat the process, clicking and dragging the OrderID field from the tblOrders table to the OrderID field in the tblOrderDetails table.
Modifying an existing relationship is easy. Access gives you the ability to delete an existing relationship or to simply modify the nature of the relationship. To permanently remove a relationship between two tables, follow these steps:
You will often want to modify the nature of a relationship rather than remove it. To modify a relationship, follow these steps:
As you can see, establishing a relationship is quite easy. Establishing the right kind of relationship is a little more difficult. When you attempt to establish a relationship between two tables, Access makes some decisions based on a few predefined factors:
Referential integrity is a series of rules that are applied by the Access Jet database engine to ensure that the relationships between tables are properly maintained. At its most basic level, referential integrity rules prevent the creation of orphan records in the table on the many side of the One-to-Many relationship. For example, after establishing a relationship between a Customers table and an Orders table, all orders in the Orders table must be related to a particular customer in the Customers table. Before you can establish referential integrity between two tables, the following conditions must be met:
After referential integrity has been established between two tables, the following rules are applied:
If any of the preceding three rules are violated and referential integrity is being enforced between the tables, an appropriate error message is displayed (see Figure 4.3).
Figure 4.3. This error message appears when you attempt to add an order for a customer that doesn't exist.
Access's default behavior is to prohibit the deletion of parent records that have associated child records and to prohibit the change of a primary key value of a parent record when that parent has associated child records. You can override these restrictions by using the two check boxes that are available in the Relationships dialog when you establish or modify a relationship.
[ic:example]To open the Relationships window, select the database window and click Relationships on the toolbar. Double-click on the join line between tblCustomers and tblOrders. Click on the Enforce Referential Integrity check box. Click OK. Repeat the process for the relationship between tblOrders and tblOrderDetails.
Go into tblCustomer and add a couple of records. Take note of the CustomerIDs. Go into tblOrders. Add a couple of records, taking care to assign CustomerIDs of customers that exist in the tblCustomers table. Now try to add an order for a customer whose CustomerID does not exist in tblCustomers. You should get an error message.
Attempt to delete from tblCustomers a customer who does not have any orders. You should get a warning message, but you should be allowed to complete the process. Now try to delete a customer who has orders. You should be prohibited from deleting the customer. Attempt to change the CustomerID of a customer who has orders. You should not be able to do this.
The Cascade Update Related Fields option is available only if referential integrity has been established between the tables. With this option selected, the user is not prohibited from changing the primary key value of the record on the one side of the relationship. Instead, when an attempt is made to modify the field joining the two tables on the one side of the relationship, the change is cascaded down to the Foreign Key field on the many side of the relationship.
Figure 4.4. An orphan record with Null in the Foreign Key field.
The Cascade Delete Related Records option is available only if referential integrity has been established between the tables. With this option selected, the user can delete a record on the one side of a One-to-Many relationship, even if related records exist in the table on the many side of the relationship. For example, a customer can be deleted even if the customer has existing orders. Referential integrity is maintained between the tables because Access automatically deletes all related records in the child table.
If you attempt to delete a record from the table on the one side of a One-to-Many relationship, you get the usual warning message (see Figure 4.5). One the other hand, if you attempt to delete a record from the table on the one side of a One-to-Many relationship and related records exist in the child table, you are warned that you are about to delete the record from the parent table and any related records in the child table (see Figure 4.6).
Figure 4.5. This message appears when the user attempts to delete a parent record without related child records.
Figure 4.6. This message appears when the user attempts to delete a parent record with related child records.
[ic:example]Modify the relationship between tblCustomers and tblOrders. Click on the Cascade Update Related Fields check box. Modify the relationship between tblOrders and tblOrderDetails. Click on the Cascade Delete Related Records check box. There is no need to Cascade Update Related Fields because the OrderID field in tblOrders is an AutoNumber field.
Attempt to delete a customer who has orders. You should still be prohibited from doing this because you did not select Cascade Delete Related Records. Change the Customer ID of a Customer in tblCustomers who has orders. This change should be allowed. Take a look at the tblOrders table. The CustomerID of all corresponding records in the table should now be updated to reflect the change in the parent record.
Add some order details to the tblOrderDetails table. Try to delete any order that has detail within the tblOrderDetails table. You should receive a warning, but you should be allowed to complete the process.
The primary benefit of relationships is the data integrity that they provide. Without the establishment of relationships, users are free to add records to child tables without regard to entering required parent information. After referential integrity is established, you can select Cascade Update Related Fields or Cascade Delete Related Records, as appropriate, which saves you quite a bit of code in maintaining the integrity of the data in your system. Most relational database management systems require that you write the code to delete related records when a parent record is deleted or to update the foreign key in related records when the primary key of the parent is modified. By selecting the Cascade Update and Cascade Delete options, you are sheltered from having to write a single line of code to accomplish these common tasks.
Relationships are automatically carried into your queries. This means that each time you build a new query, the relationships between the tables within it are automatically established based on the relationships that you have set up in the Relationships window. Furthermore, each time you build a form or report, relationships between the tables included on the form or report are used to assist with the design process. Whether you delete or update data using a datasheet or a form, all referential integrity rules automatically apply, even if the relationship is established after the form is built.
The field that joins two tables on the one side of a One-to-Many relationship must be a Primary Key field or must have a unique index so that referential integrity can be maintained. If the index on the one side of the relationship is not unique, there is no way to determine to which parent a child record belongs.
An index on the field on the many side of the One-to-Many relationship is optional. It serves to improve the performance of any processing involving the relationship. Make sure that you set the index to Yes (Duplicates OK); otherwise, you have a One-to-One rather than a One-to-Many relationship.
Let's establish the relationships that you need to set up for the tables included in the time and billing database.
Relationships enable you to normalize your database. Using relationships, you can divide your data into separate tables and combine the data again at runtime. This chapter began by describing the types of relationships that can be defined. It then covered the details of establishing and modifying relationships between tables and described all of the important aspects of establishing relationships.
The ability to easily establish and maintain referential integrity between tables is an important strength of Microsoft Access. This chapter described the referential integrity options and highlighted when each option is appropriate. Finally, this chapter summarized the benefits of relationships.