Chapter 3: Designing Bulletproof Databases
109
Without a doubt, one-to-many relationships are the most common type encountered in relational
database systems. Examples of one-to-many situations abound:l (^) Customers and orders: Each customer (the “one” side) has placed several orders (the
“many” side), but each order is sent to a single customer.
l (^) Teacher and student: Each teacher has many students, but each student has a single
teacher (within a particular class, of course).
l (^) Employees and paychecks: Each employee has received several paychecks, but each pay-
check is given to one and only one employee.
l (^) Patients and treatments: Each patient receives zero or more treatments for a disease, but
each treatment is given to multiple patients.
As I discuss in the “Creating relationships and enforcing referential integrity” section, later in this
chapter, Access makes it very easy to establish one-to-many relationships between tables. A one-to-
many relationship is illustrated in Figure 3.11. This figure, using tables from the Northwind
Traders database, clearly demonstrates how each record in the Customers table is related to sev-
eral different records in the Orders table. An order can be sent to only a single customer, so all
requirements of one-to-many relationships are fulfilled by this arrangement.
FIGURE 3.11
The Northwind Traders database contains many examples of one-to-many relationships.
Although the records on the “many” side of the relationship illustrated in Figure 3.11 are sorted by
the CustomerID field in alphabetical order, there is no requirement that the records in the many
table be arranged in any particular order.