Полезная информация

next up previous contents index
Next: One-to-Many Joins Up: Joining Tables Previous: Additional Join Possibilities

Choosing a Join Key

The join key is the value used to link entries between tables. For example, in figure [*], 648 is a customer key, appearing in the customer table to uniquely identify the row, and in the salesorder table to refer to that customer row.

Some people may question whether an id number is needed. Should the customer name be used as a join key? Using the customer name as the join key is not good because:

In the statename table, the two-letter state code is probably a good join key because:

There are basically two choices for join keys, id numbers and short character codes. If an item is referred to repeatedly, it is best to use a short character code as a join key. You can display this key to users and allow them to refer to customers and employees using codes. Users prefer to identify items by short, fixed-length character strings containing numbers and letters. For example, customers can be identified by six-character codes, FLE001, employees by their initials, BAW, and parts by five-character codes, E7245. Codes are easy to use and remember. In many cases, users can choose the codes, as long as they are unique.

It is possible to allow users to enter short character codes and still use id numbers as join keys. This is done by adding a code column to the table. For the customer table, add a column called code and place the customer code in that column. When the user enters a customer code, your query can find the customer id assigned to the customer code, and use that customer id in joins with other tables. Figure [*] shows a query using a customer code to find all order numbers for that customer.

  
Figure: Using a customer code
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~~~~~~customer.customer\_id~=~salesorder.customer\_id\end{list}\par
\end{figure}

In some cases, id numbers are fine and codes unnecessary:

Defining codes for such values would be useless. It is better to allow the database to assign a unique number to each item. The next chapter covers database support for assigning unique id's.

There is no universal rule about when to choose codes or id numbers. U.S. states are clearly better keyed on codes, because there are only 50 U.S. states, the codes are short, unique, and well known by most users. At the other extreme, order numbers are best used without codes because there are too many of them and codes would be of little use.


next up previous contents index
Next: One-to-Many Joins Up: Joining Tables Previous: Additional Join Possibilities
Bruce Momjian
1999-11-21