Databases Reference
In-Depth Information
Figure 5-8
CUSTOMER
Customer
Number
Customer
Name
Salesperson
Number
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of
Hire
HQ City
Denormalized CUSTOMER table.
Both of these are examples of
denormalization.
To optimize data retrieval
performance, it may be necessary to take pairs of related, third normal form
tables and to combine them, introducing possibly massive data redundancy. If
all else has been done to improve performance and response times and
throughput are still unsatisfactory for the business environment, eliminating
run-time joining by recombining tables may mean the difference between suc-
cess and failure. If designers go this route, they must put procedures in place
to manage the redundant data as updates are made to the data over time.
Figure 5-8 shows the denormalized CUSTOMER table containing data from
the SALESPERSON table. When combining tables in this manner, the surviving
table in the one-to-many relationship will always be the table on the “many side”
of the relationship. That's because you can attach one set of salesperson data to
a customer record, but you cannot attach many sets of customer data to a sin-
gle salesperson record without creating an even worse mess.
Figure 5-9 shows the denormalized data as stored in the combined table.
Since a salesperson can have several customers, each salesperson's data will be
repeated for each customer for which he is responsible. In this sample data, the
Figure 5-9
CUSTOMER
Customer
Number
Customer
Name
Salesperson
Number
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of
Hire
HQ City
0121
Main St.
Hardware
Jane's Stores
ABC Home
Stores
Acme
Hardware
Store
Fred's Tool
Stores
City Hardware
137
New York
137
Baker
10
1995
0839
0933
186
137
Chicago
Los Angeles
186
137
Adams
Baker
15
10
2001
1995
1047
137
Los Angeles
137
Baker
10
1995
1525
361
Atlanta
361
Carlyle
20
2001
1826
137
New York
137
Baker
10
1995
Denormalized salesperson and customer data.
