Microsoft Access 2010 Bible

Part I: Access Building Blocks

96

the repeating groups for quantity and title continue to violate the first rule of normalization. (The

row height in Figure 3.3 has been adjusted to make it easier to see the table’s arrangement.)

FIGURE 3.3

Only a slight improvement over the previous design

The design in Figure 3.3 is still clumsy and difficult to work with. The columns to hold the book

quantities and titles are permanent features of the table. The developer must add enough columns

to accommodate the maximum number of books that could be purchased by a bookstore. For

example, let’s assume that the developer anticipates that no bookstore will ever order more than 50

books at a time. This means that 100 columns are added to the table (two columns — Quantity

and Title — are required for each book title ordered). If a bookstore orders a single book, 98

columns would sit empty in the table, a very wasteful and inefficient situation.

Based on the design shown in Figure 3.3, it would be exceedingly difficult to query tblBoo-

kOrders2 to get the sales figure for a particular book. The quantity sold for any book is scattered

all over the table, in different rows and different columns, making it very difficult to know where

to look for in a book’s sales data.

Also, if any book order exceeds 100 books, the table has to be redesigned to accommodate the two

additional columns needed by the order. Of course, the user might add a second row for the order,

making the data in the table more difficult to work with than intended.

Figure 3.4 shows tblBookOrders3, a new table created from the data in Figure 3.3 in first nor-

mal form. Instead of stacking multiple book orders within a single record, in tblBookOrders3

each record contains a single book ordered by a customer. More records are required, but the data

is handled much more easily. First normal form is much more efficient because the table contains

no unused fields. Every field is meaningful to the table’s purpose.