Chapter 3: Designing Bulletproof Databases
103
Similarly, some calculated values may take a long time to evaluate. You may find it more expedient
to simply store a calculated value than to evaluate the expression on the fly. This is particularly
true when the user base is working on older, memory-constrained, or slow computers.Be aware that most steps to denormalize a database schema result in additional programming time
required to protect the data and user from the problems caused by an unnormalized design. For
example, in the case of the calculated OrderTotal field, you must insert code that calculates and
updates this field whenever the data in the fields underlying this value change. This extra program-
ming, of course, takes time to implement and time to process at runtime.Caution
Make sure that denormalizing the design does not cause other problems. If you know you’ve deliberately
denormalized a database design and you’re having trouble making everything work (particularly if you begin to
encounter any of the anomalies discussed in the previous section), look for workarounds that permit you to
work with a fully normalized design.
This business about update anomalies is important to keep in mind. The whole purpose of normalizing
the tables in your databases is to achieve maximum performance with minimum maintenance effort.Three types of errors can occur from an unnormalized database design. Following the rules outlined in
this chapter will help you avoid the following pitfalls:l (^) Insertion anomaly: An error occurs in a related table when a new record is added to another
table. For example, let’s say you’ve added the OrderTotal field described in the previous
section. After the order has been processed, the customer calls and changes the number of
books ordered or adds a new book title to the same order. Unless you’ve carefully designed
the database to automatically update the calculated OrderTotal field, the data in that field
will be in error as the new data is inserted into the table.
If insertion anomalies are a problem in your applications, you may be able to utilize data
macros (see Chapter 15) to help synchronize the data in your tables when changes are made.
l (^) Deletion anomaly: A deletion anomaly causes the accidental loss of data when a record is
deleted from a table. Let’s assume that the tblBookOrders3 table contains the name,
address, and other contact information for each bookstore. Deleting the last remaining record
containing a particular customer’s order causes the customer’s contact information to be
unintentionally lost. Keeping the customer contact information in a separate table preserves
and protects that data from accidental loss. Avoiding deletion anomalies is one good reason
not to use cascading deletes in your tables. (See the “Table Relationships” section, later in this
chapter, for more on cascading deletes.)
l (^) Update anomaly: Storing data that is not dependent on the table’s primary key causes you to
have to update multiple rows anytime the independent information changes. Keeping the
independent data (such as the bookstore information) in its own table means that only a sin-
gle instance of the information needs to be updated. (For more on update anomalies, see
“Further optimization: Adding tables to the scheme” earlier in this chapter.)
More on anomalies