Microsoft Access 2010 Bible

Chapter 3: Designing Bulletproof Databases

105

The problems with flat-file databases should be immediately apparent from viewing Figure 3.7.

Notice that the employee information is duplicated in multiple rows of the worksheet. Each time a

payroll check is issued to an employee, a new row is added to the worksheet. Obviously, this

worksheet would rapidly become unmanageably large and unwieldy.

Consider the amount of work required to make relatively simple changes to the data in Figure 3.7.

For example, changing an employee’s title requires searching through numerous records and edit-

ing the data contained within individual cells, creating many opportunities for errors.

Through clever programming in the Excel VBA language, it would be possible to link the data in

the worksheet shown in Figure 3.7 with another worksheet containing the order detail informa-

tion. It would also be possible to programmatically change data in individual rows. But such

Herculean efforts are needless when you harness the power of a relational database such as

Microsoft Access.

Connecting the data

A table’s primary key uniquely identifies the records in a table. In a table of employee data, the

employee’s Social Security number, a combination of first and last names, or an employee ID might

be used as the primary key. Let’s assume the employee ID is selected as the primary key for the

employees table. When the relationship to the payroll table is formed, the EmployeeID field is

used to connect the tables together. Figure 3.8 shows this sort of arrangement (see the “One-to-

many” section, later in this chapter).

Cross-Reference
Some of the issues related to using natural keys (such as Social Security number) are discussed in the section
titled “Natural versus surrogate primary keys,” later in this chapter.

FIGURE 3.8

The relationship between the tblEmployees and tblPayroll tables is an example of a typical one-to-
many relationship.

Related records