Microsoft Access 2010 Bible

Chapter 3: Designing Bulletproof Databases

97

FIGURE 3.4

First normal form at last!

The table in Figure 3.4 contain the same data as shown in Figure 3.2 and Figure 3.3. The new

arrangement, however, makes it much easier to work with the data. For example, queries are easily

constructed to return the total number of a particular book ordered by contacts, or to determine

which titles have been ordered by a particular bookstore.

Tip
Your tables should always be in first normal form. Make sure each cell of the table contains a single value,
don’t mix values within a cell, and don’t have repeating groups (as you saw in Figure 3.3).

The table design optimization is not complete at this point, however. Much remains to be done

with the BookOrders data and the other tables in this application. In particular, the table shown

in Figure 3.4 contains a lot of redundant information. The book titles are repeated each time cus-

tomers order the same book, and the order number and order date are repeated for all the rows for

an order.

A more subtle issue is the fact that the OrderID can no longer be used as the table’s primary key.

Because the OrderID is duplicated for each book title in an order, it can’t be used to identify indi-

vidual records in the table. Instead, the OrderID field is now a key field for the table and can be

used to locate all the records relevant to a particular order. The next step of optimization corrects

this situation.

Second normal form

A more efficient design results from splitting the data in tblBookOrders into two different tables

to achieve second normal form (2NF). The first table contains the order information (for example,

the OrderID, OrderDate, and Customer ), while the second table contains the order details

( Quantity and Title). This process is based on the second rule of normalization:

Data not directly dependent on the table’s primary key is moved into another table.