Chapter 3: Designing Bulletproof Databases
111
As indicated in Figure 3.12, many-to-many relationships are somewhat more difficult to under-
stand because they can’t be directly modeled in relational database systems like Access. Instead, the
many-to-many relationship is broken into two separate one-to-many relationships, joined through
a linking table (called a join table). The join table has one-to-many relationships with both of the
tables involved in the many-to-many relationship. This principle can be a bit confusing at first, but
close examination of Figure 3.12 soon reveals the beauty of this arrangement.
In Figure 3.12, you can see that student ID 2 (Michael Barde) belongs to the music club (Club ID = 7),
while student ID 12 (Jeffrey Wilson) is a member of the horticulture club (Club ID = 2). Both Michael
Barde and Jeffrey Wilson belong to the photography club (Club ID = 3). Each student belongs to multi-
ple clubs, and each club contains multiple members.
Because of the additional complication of the join table, many-to-many relationships are often con-
sidered more difficult to establish and maintain. Fortunately, Access makes such relationships
quite easy to establish, if a few rules are followed. These rules are explained in various places in
this book. For example, in order to update either side of a many-to-many relationship (for exam-
ple, to change club membership for a student), the join table must contain the primary keys of
both tables joined by the relationship.
Many-to-many relationships are quite common in business environments:
l Lawyers to clients (or doctors to patients): Each lawyer may be involved in several
cases, while each client may be represented by more than one lawyer on each case.
l Patients and insurance coverage: Many people are covered by more than one insurance
policy. For example, if you and your spouse are both provided medical insurance by your
employers, you have multiple coverage.l (^) Video rentals and customers: Over a year’s time, each video is rented by several people,
while each customer rents more than one video during the year.
l (^) Magazine subscriptions: Most magazines have circulations measured in the thousands or
millions. Most people subscribe to more than one magazine at a time.
The Collectible Mini Cars database has a many-to-many relationship between tblCustomers
and tblSalesPayments, linked through tblSales. Each customer might have purchased
more than one item, and each item might be paid for through multiple payments. In addition to
joining contacts and sales payments, tblSales contains other information, such as the sale date
and invoice number. The join table in a many-to-many relationship often contains information
regarding the joined data.
Given how complicated many-to-many joins can be to construct, it’s fortunate that many-to-many
relationships are quite a bit less common than straightforward one-to-many situations.
Although Figure 3.12 shows a join table with just two fields — StudentID and ClubID — there
is no reason that the join table can’t contain other information. For example, the tblStudent-
ToClubJoin table might include fields to indicate membership dues collected from the student
for each club.