PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 232

CUSTOMER

Customer Customer Salesperson Salesperson Salesperson Commission Year of

Number Name Number HQ City Number Name Percentage Hire

F I G U R E 8.30
The denormalized SALESPERSON and CUSTOMER tables as the new CUSTOMER table

Figure 8.30 shows the denormalized SALESPERSON and CUSTOMER tables
combined into one. The surviving table of the two in the one-to-many relationship
will always be the table on the '' many side'' of the relationship. You can attach
one set of salesperson data to a customer record; you cannot attach many sets of
customer data to a single salesperson record without creating an even worse mess.
The sample salesperson and customer data from Figure 5.14 is denormalized in
Figure 8.31. (Figure 8.31 is identical to Figure 3.8. We used it in Chapter 3 to
make a point about data redundancy when we were exploring that subject.) Since
a salesperson can have several customers, a particular salesperson's data will be
repeated for each customer he has. Thus, the table shows that salesperson number
137's name is Baker four times , his commission percentage is 10 four times ,and
his year of hire was 1995 four times . The performance improvement had better be
worth it, because the integrity exposure is definitely there.

Adding New Tables
Finally, there is the concept of simply duplicating data. Sometimes the final
performance issue is that trying to maintain response time and throughput with
the number of applications and users trying to share the same data is beyond
the capabilities of the hardware, the software, and all the other physical design
techniques. At the risk of overt data redundancy (which hopefully you will attempt
to managed), the only recourse is to duplicate the data.

CUSTOMER

Customer Customer Salesperson Salesperson Salesperson Commission Year of

Number Name Number HQ City Number Name Percentage Hire

0121 Main St. Hardware 137 New York 137 Baker 10 1995

0839 Jane's Stores 186 Chicago 186 Adams 15 2001

0933 ABC Home Stores 137 Los Angeles 137 Baker 10 1995

1047 Acme Hardware Store 137 Los Angeles 137 Baker 10 1995

1525 Fred's Tool Stores 361 Atlanta 361 Carlyle 20 2001

1700 XYZ Stores 361 Washington 361 Carlyle 20 2001

1826 City Hardware 137 New York 137 Baker 10 1995

2198 Western Hardware 204 New York 204 Dickens 10 1998

2267 Central Stores 186 New York 186 Adams 15 2001

F I G U R E 8.31
The denormalized salesperson and customer data from Figure 5.12