Relationships Between Tables

In many databases, the information of one table can be dependent on the information of another table.  A relationship exists between the tables in question.  In the sample database for these pages, the table PRO SHOP ORDERS is a prime example.  It contains fields that are dependent on the other three tables in the database, including the linked table.  We can use these relationships to safeguard our table against errors.  The Relationships button allows us to link the tables together.    However, we have to define the relationship.  There are two main types of relationships:

• One-to-One:  This is a relationship where one record in the child (related) table has a corresponding record in the parent (primary) table.  An example of this may be an employee phone list and an employee payroll table.
  
• One-to-Many:  This is a relationship where several records in the child (related) table has a corresponding SINGLE record in the parent (primary) table.  

For this database, we will use one-to-many relationships.  Each member is unique in the MEMBERS table is unique.  However, a member may make more than one purchase in the pro shop.  The same can be said for the PRO SHOP ITEMS table, as each product is unique but can be ordered more than once.  This same philosophy applies for the EMPLOYEES table.

When we click on the Relationships button, we then add the tables we want in the relationship.  All 4 tables will be added in this case.  Here is what the screen now looks like.

To link the tables, drag the common field from the primary table to the corresponding field in the related table.  In the case of the MEMBERS table, we'll drag the Member Number field to the Member ID field in PRO SHOP ORDERS.

The computer automatically detects the correct Relationship Type at the bottom.   An additional feature is the Enforce Referential Integrity check box.  If this is checked, this will prevent someone from entering a member number in the PRO SHOP ORDERS table if it does not exist in the MEMBERS table.  (NOTE:  The check boxes for Cascade Update Related Fields and Cascade Delete Related Records are available if you want to update or delete the records in the related table if similar changes are made in the primary table.)  Here are the relationships once we have joined the other tables.

Notice the relationship link between the EMPLOYEE LIST table and PRO SHOP ORDERS. It is different from the others, because you CANNOT establish a one-to-many or one-to-one relationship on a related table.

last modified 3/25/99 pjm