Databases Reference
In-Depth Information
In truth, tackled in a direct and comprehensive manner this is not a problem that
human beings can grapple with, leading us to the possible conclusion that we should
probably just give up. Close this topic, turn on the TV, and leave database design for
someone else. But databases do get designed by human beings, and designed well. To do
this people use a number of strategies we will discuss. Very little has been formally pub-
lished on this topic until now, though there have been some loose reflections in various
articles in recent years. Automated design utilities, discussed in Chapter12, have had to
grapple with this problem as well. However, these automated utilities have the compu-
tational power of the database query engine and the full computational power of the
computer to explore combinations in ways impractical for human designers. Fortu-
nately database practices are not defined by published literature alone, and for years
practitioners have been getting by despite the interdependence problem using practical
strategies. Practical strategies are not always steeped in scientific analysis but much like
chicken soup for a bad cold, they have over time demonstrated effectiveness and gained
broad acceptance.
9.1
Strong and Weak Dependency Analysis
In a technical paper, Zilio et al. [2004] discuss automated database design in which they
distinguish between strong and weak design relationships. Zilio et al.'s assumption is
that although all design attributes are related to each other, some are more related than
others. They define a matrix of strong and weak relationships, shown below in Table
9.1. Weak relationships are those where the design choices can be made in sequence.
This does not mean that the choices are independent, but rather that designing the
attributes one after another will not lead to a grossly suboptimal design. An example of
a weak relationship is the design of B+tree indexes followed by the design of MDC
(block indexes). Although the design of MDC will result in block indexes that can obvi-
ate the use of an existing B+tree index, it is unlikely to result in the need for different
B+tree indexes. Therefore, it is generally reasonable to design B+tree indexes, followed
by MDC, and subsequently determine which B+tree indexes are no longer required.
An example of a strong relationship is one where the design attributes are so tightly
coupled that sequential modeling and design will often lead to significantly suboptimal
database designs. An example of this is the design of materialized views and indexes.
Materialized views are often large enough to require indexes themselves. Designing
materialized views without the corresponding indexes at the same time will often lead to
design choices that appear to be useless by every normal analysis (examining object
access, plan selection, resource modeling, etc.).
Notice in Table 9.1 that partitioning appears to be weakly related to other design
attributes. This is extremely fortunate since it allows the database designer to select parti-
tioning for shared-nothing databases after most of the other attributes have been designed
using a default (basic) partitioning scheme in the interim. Recall from the discussion in
