Chapter 3: Designing Bulletproof Databases .................................................................................
93
FIGURE 3.1An Access table containing customer and orders data
Such an arrangement has several problems:l (^) The table quickly becomes unmanageably large. The Northwind Traders Contacts table
contains 11 different fields, while the Orders table contains 14 more. One field —
OrderID — overlaps both tables. Each time an order is placed, all 24 data fields in the
combined table would be added for each record added to the table, including a lot of data
(such as the Contact Name and Contact Title) not directly relevant to an order.
l Data are difficult to maintain and update. Making simple changes to the data in the
large table — for example, changing a contact’s phone or fax number — involves search-
ing through all records in the table, changing every occurrence of the phone number. It’s
easy to make an erroneous entry or miss one or more instances. The fewer records need-
ing changes, the better off the user will be.
l (^) A monolithic table design is wasteful of disk space and other resources. Because the
combined table contains a huge amount of redundant data (for example, a contact’s
address is repeated for every sale), a large amount of hard disk space is consumed by the
redundant information. In addition to wasted disk space, network traffic, computer mem-
ory, and other resources would be poorly utilized.
A much better design — the relational design — moves the repeated data into a separate table,
leaving a field in the first table to serve as a reference to the data in the second table. The additional
field required by the relational model is a small price to pay for the efficiencies gained by moving
redundant data out of the table.
A second huge advantage of normalizing data, and applying strict database design rules to Access
applications is that the data becomes virtually bulletproof. In an appropriately designed and man-
aged database, users are ensured that the information displayed on forms and reports truly reflects
the data stored in the underlying tables. Poorly designed databases are prone to data corruption,