Databases Reference
In-Depth Information
table shows that salesperson number 137's name is Baker four times, his com-
mission percentage is 10 four times, and his year of hire was 1995 four times.
The added storage requirement would normally be avoided unless it can be jus-
tified by the performance improvement.
Adjusting Table Attributes
You may find it necessary to make changes to your initial set of entity attributes
and table columns. For instance, as mentioned before, sometimes a table simply
does not have a single, unique attribute that can serve as its primary key. A two-
attribute primary key might be okay, but there are circumstances in which the
primary key of a table might consist of two, three, or more attributes, with the
combination leading to unacceptable performance. For one thing, having to use
the multi-attribute key as a foreign key in the other tables could be unaccept-
ably complex.
The solution is similar to what you would do if there were no columns
that could be used at the primary key. You can 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. An identity column, like that discussed in the earlier
example, is once again an appropriate solution. 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 most cases, this new attribute will be in addition to the multiple values
rather than a replacement for them. The values likely contain valid, perhaps crit-
ical, business information and therefore cannot be removed from the table.
Instead, you simply choose not to use them as your primary key columns.
The CUSTOMER EMPLOYEE table in Figure 5-5 has a two-column primary
key, a composite key, based on Customer Number and Employee Number (the
customer employee number). You could create a new attribute, Customer
Employee Number, which will be its own set of employee numbers for these
people that will be unique across all of the customer companies. This is shown
in Figure 5-10. If the Customer Number, Employee Number combination had
been placed in other tables in the database as a foreign key, the two-attribute
combination would need to be replaced by this new, single attribute.
Figure 5-10
CUSTOMER EMPLOYEE
Customer
Employee
Number
Customer
Name
Employee
Number
Employee
Name
Title
Restructured CUSTOMER EMPLOYEE table.
