Part I: Access Building Blocks
110
Note
Although parent-child is the most common expression used to explain the relationship between tables related
in a one-to-many relationship, you may hear other expressions used, such as master-detail applied to this
design. The important thing to keep in mind is that the intent of referential integrity is to prevent lost records
on the “many” side of the relationship. Referential integrity guarantees that there will never be an orphan (a
child record without a matching parent record). As you work with related tables, it’s important to keep in mind
which table is on the “one” side and which is on the “many” side.
Notice how difficult it would be to record all the orders for a customer if a separate table were not
used to store the order’s information. The flat-file alternative discussed earlier in this section,
requires much more updating than the one-to-many arrangement shown in Figure 3.11. Each time
a customer places an order with Northwind Traders, a new record is added to the Orders table.
Only the CustomerID (for example, AROUT) is added to the Orders table as the foreign key
back to the Customers table. Keeping the customer information is relatively trivial because each
customer record appears only once in the Customers table.Many-to-many
You’ll come across many-to-many situations from time to time. In a many-to-many arrangement,
each record in both tables can be related to zero, one, or many records in the other table. An
example is shown in Figure 3.12. Each student in tblStudents can belong to more than one
club, while each club in tblClubs has more than one member.FIGURE 3.12A database of students and the clubs they belong to is an example of a many-to-many relationship.