Part I: Access Building Blocks
102
customer’s record in tblBookstores, and use it as the default value for the Discount column
but permit the salesperson to override the discount value when a special arrangement has been
made with the customer.Third normal form
The last step of normalization, called third normal form (abbreviated 3NF), requires removing all
fields that can be derived from data contained in other fields in the table or other tables in the
database. For example, assume the sales manager insists that you add a field to contain the total
value of an order in the orders table. This information, of course, would be calculated from the
Quantity field in tblBookOrderDetails and the book unit price from the book information
table.It’s not really necessary to add the new OrderTotal field to the Orders table. Access easily cal-
culates this value from data that is available in the database. The only advantage of storing order
totals as part of the database is to save the few milliseconds required for Access to retrieve and cal-
culate the information when the calculated data is needed by a form or report.Removing calculated data has little to do with maintaining the database. The main benefits are sav-
ing disk space and memory, and reducing network traffic. Depending on the applications you
build, you might find good reasons to store calculated data in tables, particularly if performing the
calculations is a lengthy process, or if the stored value is necessary as an audit check on the calcu-
lated value printed on reports. It might be more efficient to perform the calculations during data
entry (when data is being handled one record at a time) instead of when printing reports (when
many thousands of records are manipulated to produce a single report).As you’ll read in the “Denormalization” section, later in this chapter, there are some good reasons
why you might choose to include calculated fields in a database table. As you’ll read in this section,
most often the decision to denormalize is based on a need to make sure the same calculated value
is stored in the database as is printed on a report.Tip
Although higher levels of normalization are possible, you’ll find that, for most database applications, third nor-
mal form is more than adequate. At the very least, you should always strive for first normal form in your tables
by moving redundant or repeating data to another table.
Denormalization
After hammering you with all the reasons why normalizing your databases is a good idea, let’s con-
sider when you might deliberately choose to denormalize tables or use unnormalized tables.Generally speaking, you normalize data in an attempt to improve the performance of your data-
base. For example, in spite of all your efforts, some lookups will be time-consuming. Even when
using carefully indexed and normalized tables, some lookups require quite a bit of time, especially
when the data being looked up is complicated or there’s a large amount of it.