Now that you've created all the tables and columns within the database, you are ready to begin creating the relationships. Remember, relationships are what make the database efficient and keep you from duplicating data within individual tables. To begin establishing relationships between tables, follow these steps: -
From the Tools menu, select Relationships. -
When you are prompted with the Show Table dialog box, add every table by selecting the first table, holding down the Shift key, and then selecting the last table. Click Add. -
Choose Close. You are now ready to establish relationships between the tables. First, establish a relationship for the customers and orders tables. Notice that the customers table contains a CustomerID Primary Key and the orders table contains a CustomerID Foreign Key. The reason is that one customer can have many orders, so a one-to-many relationship must be created. The only way that can be accomplished is through the use of a foreign key. Access does not allow direct insertion of foreign keys; they are given when establishing a relationship between a primary key in one table and a field with the same data type and value in another table. To create the relationship, simply drag the CustomerID field from the customers table into the CustomerID field in the orders table. You will be presented with the Edit Relationships dialog box, as shown in Figure 26.17. Figure 26.17. The Relationships Editor allows quick modification for relationships between tables. Notice that the two fields are displayed directly underneath their corresponding tables. Because this is a one-to-many relationship, you need to select Enforce Referential Integrity along with Cascade Update Related Fields and Cascade Delete Related Fields. This is done for referential integrity. If a customer is deleted from the Web store database, you would want all their orders deleted as well; otherwise, the database would be cluttered with redundant and, at times, unnecessary data. Notice the Relationship Type states that the relationship is indeed a one-to-many relationship. Select OK. The table that follows describes the relationships between tables: Tables | Keys | Referential Integrity | Customers/Orders | CustomerID | Yes | Customers/CreditCards | CustomerID | Yes | Customers/Transactions | CustomerID | No | Orders/Transactions | ProductID | No | Orders/Products | ProductID | No | Products/Inventory | ProductID | Yes | When all the relationships have been established, the diagram should look similar to Figure 26.18. Figure 26.18. The Relationships window should look much like a diagram with connected lines showing relationships between tables. A few points to consider after you have finished creating all of the relationships are the following: Remember that orders and credit cards will have a many relationship to the one customer. This is so because one customer can have many orders as well as credit cards on file. Also, referential integrity is enforced because if you delete a customer from the Web store database, you also want that customer's orders and credit cards on file to be deleted as well. A one-to-one relationship exists from customers to transactions. This is the case because the transactions table serves as a simple history of all transactions. Nothing should ever be deleted from this table. The products table has a one-to-one relationship with the transactions table as well as the orders table because both the orders and the transactions tables share information that the product table contains. A one-to-one relationship exists between the products table and the inventory table because a product can only have one quantity associated with it. You enforce referential integrity because if you delete a product, you also want the quantity from the inventory table deleted. NOTE Keep in mind that one-to-many relationships have icons at the end points of the relationship lines. A small "1" will appear next to the table with the one relationship and a small infinity symbol will appear, symbolizing the table with the many relationship. |