Creating Relationships

Welcome to part seven of the Building an Access Database From the Ground Up series. Over the past six articles, we’ve studied the Patrick’s Widgets scenario, designed an appropriate relational model, created an Access database and populated that database with the tables necessary to implement our relational model. In this article, we’ll look at how the power of relational databases allows us to create relationships between our tables.
Relational databases support a number of different types of relationships between tables, all designed to enforce the concept of referential integrity. Access supports three different types of relationships between tables. For the purposes of our example, we’ll consider two tables: TableA and TableB, where TableA has a to TableB

• One-to-one relationships occur when there is exactly one record in TableA that corresponds to exactly one record in TableB.
• One-to-many relationships occur when each record in TableA may have many linked records in TableB but each record in TableB may have only one corresponding record in TableA.
• Many-to-many relationships occur when each record in TableA may have many linked records in TableB and vice-versa.

Let’s think back to the Patrick’s Widgets scenario and determine the types of relationships necessary for our situation. We’ll need to create the following relationships:

• A one-to-many relationship between the CustomerID in the Customers table and the CustomerID in the Orders table. This relationship indicates that each customer may be associated with multiple orders, but each order may only be associated with one customer.
• A one-to-many relationship between the OrderID in the Orders table and the OrderID in the OrderedItems table. This relationship indicates that each order may contain multiple items, but each OrderedItem record may only correspond to a single order.
• A one-to-many relationship between the ItemID in the Items table and the ItemID in the Ordered Items table. This relationship indicates that each item ordered corresponds to a single inventory record, but each item in inventory may be associated with many orders.

On the next page of this installment, we take a look at how to implement these relationships in Microsoft Access.