Part I: Access Building Blocks
100
Notice also that the number of records in tblBookOrders4 has been reduced. This is one of sev-
eral advantages to using a relational database. Each table contains only as much data as is necessary
to represent the entity (in this case, a book order) described by the table. This is far more efficient
than adding duplicate field values (refer to Figure 3.2) for each new record added to a table.
Further optimization: Adding tables to the scheme
The design shown in Figure 3.5 is actually pretty good. Yet, I could still do more to optimize this
design. Consider the fact that the entire name of each customer is stored in tblBookOrders4.
Therefore, a customer’s name appears each time the customer has placed an order. Notice that
Uptown Books has placed two orders during the period covered by tblBookOrders4. If the
Uptown Books bookstore changed its name to Uptown Books and Periodicals, you’d have to go
back to this table and update every instance of Uptown Books to reflect the new name.
Overlooking an instance of the customer’s name during this process is called an update anomaly
and results in records that are inconsistent with the other records in the database. From the data-
base’s perspective, Uptown Books and Uptown Books and Periodicals are two completely different
organizations, even if I know that they’re the same store. A query to retrieve all the orders placed
by Uptown Books and Periodicals will miss any records that still have Uptown Books in the
Customer field because of the update anomaly.
Also, the table lacks specific information about the customers. No addresses, phone numbers, or
other customer contact information are contained in tblBookOrders4. Although you could use
a query to extract this information from a table named tblBookStores containing the addresses,
phone numbers, and other information about the bookstore customers, using the customer name
(a text field) as the search key is much slower than using a numeric key in the query.
Figure 3.6 shows the results of a refinement of the database design: tblBookOrders5 contains a
foreign key named CustomerID that relates to the CustID primary key field in the tblCus-
tomers table. This arrangement uses tblCustomers as a lookup table to provide customer-
related information to a form or report.
Part of the performance improvement is due to the fact that the CustomerID field in tblBoo-
kOrders5 is a long integer (4-byte) value instead of a text field. This means that Access has to
manipulate only 4 bytes of memory when looking for records in tblCustomers. The Customer
field in tblBookOrders4 was a text field with a width of 50 characters. This means that Access
might have to consider as many as 50 bytes of memory when searching for matching records in
tblCustomers.
A second advantage of removing the customer name from the orders table is that the name now
exists in only one location in the database. If Uptown Books changes its name to Uptown Books
and Periodicals, I now only have to change its entry in the tblBookStores table. This single
change is reflected throughout the database, including all forms and reports that use the customer
name information.
