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

Substituting Foreign Keys Consider the SALESPERSON and CUSTOMER tables
of Figure 8.21. We know that Salesperson Number is a unique attribute and serves
as the primary key of the SALESPERSON table. Say, for the sake of argument,
that the Salesperson Name attribute is also unique, meaning that both Salesperson
Number and Salesperson Name are candidate keys of the SALESPERSON table.
Salesperson Number has been chosen to be the primary key and Salesperson Name
is an alternate key.
Now, assume that there is a frequent need to retrieve data about customers,
including the name of the salesperson responsible for that customer. The
CUSTOMER table contains the number of the Salesperson who is responsible
for a customer but not the name. By now, we know that solving this problem
requires a join of the two tables, based on the common Salesperson Number
attribute. But, if this is a frequent or critical query that requires high speed, we
can improve the performance by substituting Salesperson Name for Salesperson
Number as the foreign key in the CUSTOMER table, as shown in Figure 8.25.
With Salesperson Name now contained in the CUSTOMER table, we can retrieve
customer data, including the name of the responsible salesperson, without having to
do a performance-slowing join . Finally, since Salesperson Name is a candidate key
of the SALESPERSON table, using it as a foreign key in the CUSTOMER table
still retains the ability to join the two tables when this is required for other queries.

Adding Attributes to a Table
Another means of improving database performance entails modifying the logical
design by adding attributes to tables. Here are two ways to do this.

Creating New Primary Keys Sometimes a table simply does not have a single unique
attribute that can serve as its primary key. A two-attribute primary key, such as
the combination of state and city names, might be OK. But in some circumstances
the primary key of a table might consist of two, three, or more attributes and the
performance implications of this may well be unacceptable. For one thing, indexing
a multi-attribute key would likely be clumsy and slow. For another, having to use
the multi-attribute key as a foreign key in the other tables in which such a foreign
key would be necessary would probably also be unacceptably complex.
The solution is to invent a new primary key for the table that consists of a
single new attribute. The new attribute will be a unique serial number attribute, with
an arbitrary unique value assigned to each record of the table. This new attribute will
then also be used as the foreign key in the other tables in which such a foreign key
is required. In the General Hardware database of Figure 8.21, recall that the two-
attribute primary key of the CUSTOMER EMPLOYEE table, Customer Number
and Employee Number, is necessary because customer numbers are unique only
within each customer company. Suppose that General Hardware decides to invent a
new attribute, Customer Employee Number, which will be its own set of employee

CUSTOMER

F I G U R E 8.25
Substituting another candidate key for a
foreign key

Customer Customer Salesperson

Number Name Name HQ City