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
Numbers are less likely to be entered incorrectly.
Two customers with the same name would be impossible to distinguish in a join.
If the customer name changes, all references to that name would have to change.
Numeric joins are more efficient than long character string joins.
Numbers require less storage then characters strings.
In the statename table, the two-letter state code is probably a good
join key because:
Two letter codes are easy for users to remember and enter.
State codes are always unique.
State codes do not change.
Short two-letter codes are not significantly slower than integers in joins.
Two-letter codes do not require significantly more storage than integers.
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.
Using a customer code
In some cases, id numbers are fine and codes unnecessary:
Items with short lifespans, i.e. order numbers
Items without appropriate codes, i.e. payroll batch numbers
Items used internally and not referenced by users
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.