Microsoft Access 2010 Bible

Chapter 3: Designing Bulletproof Databases

95

Although this data set is very simple, it’s typical of the type of data you might manage with an

Access database application, and it provides a valid demonstration of normalizing a set of data.

First normal form

The initial stage of normalization, called first normal form (abbreviated 1NF), requires that the

table conform to the following rule:

Each cell of a table must contain only a single value and the table must not contain repeat-

ing groups of data.

A table is meant to be a two-dimensional storage object, and storing multiple values within a field

or permitting repeating groups within the table implies a third dimension to the data. Figure 3.2

shows the first attempt (tblBookOrders1) at building a table to manage bookstore orders.

Notice that some bookstores have ordered more than one book. A value like 7 Cookie Magic in

the BookTitles field means that the contact has ordered seven copies of the cookbook titled

Cookie Magic. Storing both a quantity and the item’s name in the same cell is just one of several

ways that this table violates first normal form.

FIGURE 3.2

An unnormalized tblBookOrders table

The table in Figure 3.2 is typical of a flat-file approach to building a database. Data in a flat-file

database is stored in two dimensions (rows and columns) and neglects the third dimension (related

tables) possible in a relational database system such as Microsoft Access.

Notice how the table in Figure 3.2 violates the first rule of normalization. Many of the records in

this table contain multiple values in the BookTitle field. For example, the book titled Smokin’

Hams appears in records 7 and 8. There is no way for the database to handle this data easily — if

you want to cross-reference the books ordered by the bookstores, you’d have to parse the data con-

tained in the BookTitle field to determine which books have been ordered by which contacts.

A slightly better design is shown in Figure 3.3 (tblBookOrders2). The books’ quantities and

titles have been separated into individual columns. Each row still contains all the data for a single

order. This arrangement makes it somewhat easier to retrieve quantity and title information, but