Microsoft Access 2010 Bible

Part I: Access Building Blocks

104

Finally, always document whatever you’ve done to denormalize the design. It’s entirely possible

that you or someone else will be called in to provide maintenance or to add new features to the

application. If you’ve left design elements that seem to violate the rules of normalization, your care-

fully considered work may be undone by another developer in an effort to “optimize” the design.

The developer doing the maintenance, of course, has the best of intentions, but he may inadver-

tently reestablish a performance problem that was resolved through subtle denormalization.

One thing to keep in mind is that denormalization is almost always done for reporting purposes,

rather than simply to maintain data in tables. Consider a situation in which a customer has been

given a special discount that doesn’t correspond to his traditional discount. It may be very useful

to store the actual amount invoiced to the customer, instead of relying on the database to calculate

the discount each time the report is printed. Storing the actual amount ensures that the report

always reflects the amount invoiced to the customer, instead of reporting a value that depends on

other fields in the database that may change over time.

Table Relationships

Many people start out using a spreadsheet application like Excel or Lotus 1-2-3 to build a data-

base. Unfortunately, a spreadsheet stores data as a two-dimensional worksheet (rows and columns)

with no easy way to connect individual worksheets together. You must manually connect each cell

of the worksheet to the corresponding cells in other worksheets — a tedious process at best.

Two-dimensional storage objects like worksheets are called flat-file databases because they lack the

three-dimensional quality of relational databases. Figure 3.7 shows an Excel worksheet used as a

flat-file database.

FIGURE 3.7

An Excel worksheet used as a flat-file database