Microsoft Access 2010 Bible

Chapter 3: Designing Bulletproof Databases

107

always be only one customer related to every sales record. That is, many sales can be associated

with a single customer. In this case, the Collectible Mini Cars system is actually using tblCus-

tomers as a lookup table.

Note
Relationships can be very confusing — they depend upon the focus of the system. For example, when working with
tblCustomers and tblSales, you can always create a query that has a one-to-many relationship to tblSales
from tblCustomers. Although the system is concerned with sales (invoices), sometimes you’ll want to produce
reports or views that are buyer related instead of invoice related. Because one buyer can have more than one sale,
there will always be one record in tblCustomers and at least one record in tblSales. In fact, there could be
many related records in tblSales. So Access knows to find only one record in the Customers table and to look for
any records in the Sales table (one or more) that have the same customer number.

One-to-one

A one-to-one relationship between tables means that for every record in the first table, one and

only one record exists in the second table. Figure 3.10 illustrates this concept.

FIGURE 3.10

A one-to-one relationship

Pure one-to-one relationships are not common in relational databases. In most cases, the data con-

tained in the second table is most often included in the first table. As a matter of fact, one-to-one

relationships are generally avoided because they violate the rules of normalization. Following the

rules of normalization, data should not be split into multiple tables if the data describes a single