Part I: Access Building Blocks
108
entity. Because a person has one and only one birth date, the birth date should be included in the
table containing a person’s other data.
There are times, however, when it’s not advisable to store certain data along with other data in the
table. For example, consider the situation illustrated in Figure 3.10. The data contained in tblSe-
curity is confidential. Normally, you wouldn’t want anyone with access to the public customer
information (name, address, and so on) to have access to the confidential security code that the
customer uses for purchasing or billing purposes. If necessary, tblSecurity could be located on
a different disk somewhere on the network, or even maintained on removable media to protect it
from unauthorized access.
Another instance of a one-to-one relationship is a situation in which the data in a table exceeds the
255-field limit imposed by Access. Although rare, there could be cases in which you might have
too many fields to be contained within a single table. The easiest solution is simply to split the data
into multiple tables and connect the tables in a 1:1 relationship through the primary key (using the
same key value, of course, in each table).
Although situations where normalized data exceeds 255 columns, there are times when perfectly
normalized data exceeds 255 columns. Consider an engineering study on a new car engine. The
engineers might want to capture test measurements (temperatures, pressures, vibration, and so on)
from several dozen sensors during the test. Each sensor might generate several hundred measure-
ments in a few minutes’ time. Although there are many ways to store the data, it would be possible
to capture the data as a series of tables in a 1:1 relationship with one another, each table row repre-
senting one sensor, and each column containing the measurement at a particular time.
Yet another situation is one in which data is being transferred or shared among databases. Perhaps
the shipping clerk in an organization doesn’t need to see all of a customer’s data. Instead of includ-
ing irrelevant information such as job titles, birth dates, alternate phone numbers, and e-mail
addresses, the shipping clerk’s database contains only the customer’s name, address, and other
shipping information. A record in the customer table in the shipping clerk’s database has a one-to-
one relationship with the corresponding record in the master customer table located on the central
computer somewhere within the organization. Although the data is contained within separate
.accdb files, the links between the tables can be live (meaning that changes to the master record
are immediately reflected in the shipping clerk’s .accdb file).
Tables joined in a one-to-one relationship will almost always have the same primary key — for
example, OrderID or EmployeeNumber. There are very few reasons you would create a separate
key field for the second table in a one-to-one relationship.
One-to-many
A far more common relationship between tables in a relational database is the one-to-many. In
one-to-many relationships, each record in the first table (the parent) is related to one or more
records in the second table (the child). Each record in the second table is related to one and only
one record in the first table.
