Microsoft Access 2010 Bible

Part I: Access Building Blocks

94

which means that records are sometimes “lost” and never appear on forms and reports, even

though users added the data to the application, or the wrong data is returned by the application’s

queries. In either case, the database can’t be trusted because users are never sure that what they’re

seeing in forms and reports is correct.

Users tend to trust what they see on the screen and printed on paper. Imagine the problems that

would occur if a customer were never billed for a purchase, or inventory were incorrectly updated.

Nothing good can come from a weak database design. As database developers, we’re responsible

for making sure the applications we design are as strong and resilient as possible. Building bullet-

proof databases that resist data entry errors and always correctly return data as expected is the

theme of this chapter.

Data Normalization

The process of splitting data across multiple tables is called normalizing the data. There are several

stages of normalization; the first through the third stages are the easiest to understand and implement

and are generally sufficient for the majority of applications. Although higher levels of normalization

are possible, they’re usually ignored by all but the most experienced and fastidious developers.

To illustrate the normalization process, I’ll use a little database that a book wholesaler might use to

track book orders placed by small bookstores in the local area. This database must handle the fol-

lowing information:

l Book title

l (^) ISBN
l Author
l (^) Publisher
l Publisher address
l (^) Publisher city
l Publisher state
l (^) Publisher zip code
l Publisher phone number
l (^) Publisher fax
l Customer name
l (^) Customer address
l Customer city
l (^) Customer state
l Customer zip code
l (^) Customer phone number