Designing Relational Databases - High-Performance Web Databases: Design, Development, and Deployment - page 402

tions frequently add complexity or detract from flexibility. Second, struc-
tural database changes, although implemented for tuning purposes, are
nevertheless visible to users. For example, if a column is duplicated in
another table to avoid joining the two tables, users and programmers must
know that the column appears in both tables and, specifically, that a join is
no longer necessary. Obviously, such tuning efforts will affect the com-
mand syntax for users and within programs.
These two issues are significant, and they discourage extensive struc-
tural modifications. It is imperative that such design changes are justifiable
and that data integrity is still adequately addressed (not unintentionally
sacrificed).
The most straightforward way to modify a structure is to add duplicate
columns or rows by adding duplicate data — step RDD11. Duplicate data
can take any of several forms. For example, additional columns can be
exact copies or derivations of other columns, repeating columns that rep-
resent multiple occurrences of the same attribute, or shorter contrived col-
umns that replace unwieldy primary or foreign keys. Finally, extra columns
or rows can facilitate such functional requirements as the outer join.
Suppose a frequent online query in Ron's Real Estate Business involves
retrieving the address of a rental property with the name and telephone
number of the current renter. This query requires a join of the RENTER and
RENTER-AGREEMENT tables. One option for eliminating the join, and
thereby improving the performance of this query, is to duplicate the
renter's PHONE-NUMBER in RENTAL-AGREEMENT. However, the cost asso-
ciated with the data redundancy is high. Extra storage is required to store
each PHONE-NUMBER once for the associated RENTER plus once for each
related RENTAL-AG REEMENT. Additional business rules are required to
ensure synchronization of the duplicate data whenever PHONE-NUMBER is
updated. Finally, user queries must know when accessing PHONE-NUMBER
from RENTAL-AGRFEMENT is more efficient than accessing PHONE-NUM-
BER from RENTER, and vice versa. The database designer must weigh
these costs against the performance benefits.
Tuning by Redefining the Relational Database Structure
There are two ways to redefine the original tables and columns:
• Redefining columns — step RDD12.
• Redefining tables — step RDD13.
Long textual columns are candidates for redefinition into one or more
shorter columns. Moreover, foreign keys are sometimes redefined to reference
alternate rather than primary keys. There are many motivations and tech-
niques for eliminating, duplicating, segmenting (or splitting), and combining