Physical Database Design - High-Performance Web Databases: Design, Development, and Deployment - page 342

During the schema refinement stage in the database
lifecycle, the DBA examines the database tables to determine if they should
be vertically partitioned into two or more tables to avoid update anoma-
lies. This process, called normalization, can result in multiple tables replac-
ing a single table. If two tables are frequently joined in the workload, the
DBA might decide to denormalize them. Denormalization is the process of
replacing multiple tables with a single table in the relational schema. Dur-
ing the initial physical database design stage, the DBA reexamines the deci-
sion to normalize and weighs the benefits of normalization, which avoids
update anomalies, against the cost of performing frequent joins among the
normalized tables.

Denormalization.

As a general rule, if users always access two tables together, the tables
should be denormalized by joining them into a single table. For example,
suppose a DBA decides that a zip code functionally determines the city and
state: given a value of zip code, there is one and only one possible value for
city and one and only one possible value for state. In the schema refine-
ment stage, the DBA normalizes the address table into two tables: one table
containing the street address and zip code, and the second table contain-
ing the city, state, and zip code. However, the user always accesses the
street address, city, state, and zip code at the same time. There should
never be a record in the table containing the street address and zip code
without a corresponding record in the table containing the street address,
city, state, and zip code. In this case, the DBA should denormalize the two
tables during step 2 of the initial physical database design phase.

Table A is dependent on table B if table A is accessed
only when table B is accessed. For example, suppose there is a third table,
EmpDependent, that contains information about an employee's depen-
dents. The EmpDependent table is dependent on the Employee table
because no query ever accesses the EmpDependent table without also
accessing the Employee table. Note that some employees may have no
dependents, while others may have multiple dependents. The DBA deter-
mines that the Employee and EmpDependent tables should not be denor-
malized because there are many queries that access the Employee table
without accessing the EmpDependent table.

Hierarchical Files.

Dependent tables are candidates for representation by hierarchical file
structures. Exhibit 9 illustrates a hierarchical file structure that facilitates
the joining of the Employee and EmpDependent tables. Each Employee
record contains pointers to the records of dependents supported by the
employee. The DBMS joins the Employee and EmpDependent records by
accessing the Employee records one at a time. For each Employee record,
the DBMS follows the pointers to retrieve the records of the employee's
dependents. This results in an efficient join operation.