User`s guide

In a one-to-one relationship between records in two linked tables, for every record in the primary table
there is only one matching record in the lookup table (based on the linked fields). For example, in the
Xtreme.mdb database, the Employee table can be linked to the Employee Addresses table based on
the Employee ID field in each table. The Employee table contains information about employees at the
company, the positions they hold, their salaries, hiring information, and so on. The Employee Addresses
table contains each employee's home address. There is only one record for each employee in each of
these tables. Therefore, if the Employee table is linked to the Employee Addresses table, only one
record will be found in the Employee Addresses table for each record in the Employee table. This is a
one-to-one relationship.
23.5.2.2 One-to-many relationships
In a one-to-many relationship between records in two linked tables, for every record in the primary table,
there may be more than one matching record in the lookup table, based on the linked fields. In the
Xtreme.mdb database, the Customer table can be linked to the Orders table based on the Customer
ID field in each table. The Customer table contains information about each customer that has placed
an order with the company. The Orders table contains information about orders that customers have
placed. Since customers can place more than one order, there may be more than one record in the
Orders table for each customer record in the Customers table. This is a one-to-many relationship.
23.5.3 Performance considerations in one-to-many links
The information provided in this section is intended to help you maximize processing speed and minimize
network traffic when you are running your reports. You will learn about the best ways to use selection
formulas and indexes in one-to-many situations to make your reporting more efficient. If you do not use
the information in this section, your reports may end up processing dozens or even thousands more
records than necessary.
When a one-to-many situation exists between two database tables and the program matches up records
from the tables, there are a number of factors that determine how many records the program reads and
evaluates.
The tables that follow show the effects of the different factors on the number of records the program
ultimately has to read. The charts are based on these assumptions:
• Table A contains 26 records (one for each letter in the alphabet).
• Table B contains 2600 records (100 matching records for every record in Table A).
• The scenario is to produce a report that finds two specific records in Table A and the 200 records
(100+100) in Table B that match those two records in Table A.
In a best case scenario, the program would only have to read about 200 records to accomplish the
task.
2012-03-14521
Understanding Databases