Microsoft Access 2010 Bible

(Rick Simeone) #1

Chapter 3: Designing Bulletproof Databases


101


FIGURE 3.6

The numeric CustomerID field results in faster retrievals from tblCustomers.


In some situations, you might want the database to keep track of the names of its customers, par-
ticularly if the names change frequently. If this is the case, it’s a relatively simple task to add
another table containing a CustomerID field pointing to tblCustomers, a text field to hold the
old customer name, and, perhaps, a Date/Time field showing when the old name was retired and
the new name went into effect (the new name, of course, is kept in tblCustomers). History
tables are quite common in many database applications and are usually quite easy to implement.

In Figure 3.6, tblBookOrders5 should contain all the order-specific information, including the
shipping and payment methods, the salesperson identity, and any other information you want to
track for orders. Also, given the CustomerID in tblBookStores, it’s quite easy to add tables to
the application for tracking promotions, preferences, and other information related to individual
customers.

Breaking the rules
From time to time, you might find it necessary to break the rules. For example, let’s assume the
bookstores are entitled to discounts based on the volume of purchases over the last year. Strictly
following the rules of normalization, the discount percentage should be included in the tblBook-
stores table. After all, the discount is dependent on the customer, not on the order.

But maybe the discount applied to each order is somewhat arbitrary. Maybe the book wholesaler
permits the salespeople to cut special deals for valued customers. In this case, you might want to
include a Discount column in the table containing book orders information, even if it means
duplicating information in many records. You could store the traditional discount as part of the
Free download pdf