Databases Reference
In-Depth Information
Table 15.1
Comparison of Denormalization Options for Query 15.2
option is to leave the two original tables unchanged. Let's summarize the tradeoffs for
these three options based on Query 15.2 (see Table 15.1).
•
Option 1: Consolidate into one table, emp2.
•
Option 2: Consolidate into one table, emp2, and retain department as redun-
dant.
•
Option 3: No change to emp and department.
The analysis of these three options goes as follows:
•
Option 1 (emp2):
This is pure denormalization. It optimizes the query time and
usually improves the update times. Storage space can be higher or lower,
depending on the relative sizes of the department and emp tables. The normal-
ization definitely is less, leaving a potential delete anomaly and loss of integrity
if the last record containing a particular deptNum and deptName combination
is deleted. If this is not an issue, then denormalization is definitely a winning
strategy. Query and update times are usually more important than storage
space.
•
Option 2 (emp2 and department):
This is denormalization with redundancy to
prevent the delete anomaly between deptNum and deptName. This strategy
should only be used if the loss of integrity is a real issue here. Like pure denor-
malization, it greatly improves query time at the expense of update time and
storage space.
•
Option 3 (emp and department):
This is the original database schema, which
should be kept if the query and update times are acceptable. To denormalize
