Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

Refining All Foreign Keys

You now know that a primary key becomes a foreign key when you use it to establish a relationship between a pair of tables in a one-to-one or one-to-many relationship. As with any other key that you've worked with so far, a foreign key must comply with a specific set of elements. These elements are collectively known as the Elements of a Foreign Key.

Elements of a Foreign Key

• It has the same name as the primary key from which it was copied . You should adhere to this rule unless there is an absolutely compelling reason not to do so. (Review the discussion of the Alias field specification element in Chapter 9. It provides an example of an occasion when you might decide to break this rule.) Consider the relationship diagram in Figure 10.51, and note that the foreign keys have different names than the primary keys to which they refer.

  Figure 10.51. Primary keys and foreign keys with mismatched names.

  The fact that the names are different poses a problem because you can't be sure that the foreign keys are truly valid and actually refer to the primary keys. Is E MP # truly equivalent to E MPLOYEE N UMBER ? Is "Emp" really a shortened version of "Employee," or does it mean something else? Why did someone choose to use C LIENT # in the ORDERS table instead of C USTOMER ID? Is there any difference between the two? Do they store the same type of data? These are questions you must answer before you can do anything else with these tables and their respective relationships.

  You could make a relatively reasonable argument that the names are close enough to assume that the foreign keys are indeed valid. If there's any doubt, you could test your assumption by loading the tables with sample data. You really shouldn't have to take the time to do this, however. Imagine having to do this for 15 or 20 relationships; the amount of wasted time adds up.

  You won't have to ask these questions or perform these tests at all when you adhere to this element. Figure 10.52 shows a revised version of the diagram that uses the proper foreign key names. In this case, there is no ambiguity and little doubt that the foreign keys are appropriate. You can examine this diagram nine months from now and, with a quick glance, confidently ascertain the type of relationships between the tables and how they're established.

  Figure 10.52. Foreign keys that comply with the first element of a foreign key.

  Note

  I encounter this issue quite often when I'm asked to analyze certain types of database problems. In many cases, the foreign keys are either completely inappropriate or manifest serious data-integrity and relationship-integrity problems. Once I identify the appropriate foreign keys (or revise the existing ones) and ensure that they comply with this particular element, a number of problems disappear.

  The only time I can justify and approve of using a different name for the foreign key field is when I establish a self-referencing relationship for a given table. This is reasonable because the primary key and foreign key both reside within the table (in most cases), and each must have a unique name.

• It uses a replica of the field specifications for the primary key from which it was copied. This supports the sixth element of an ideal field, which you learned in Chapter 7 ("It retains a majority of its properties when it appears in more than one table"). A foreign key, however, has a few settings in both the General Elements and Logical Elements categories that are slightly different from those of its parent primary key.

  There are four elements in the General Elements category that you will modify when you define a field specification for a foreign key.

  1. Specification Type. Because a foreign key is based on an existing primary key, it inherits a replica of the primary key's field specifications; therefore, you designate the foreign key's specification type as "Replica." This designation helps you ensure that your foreign key specifications are consistent, and reminds you to keep this specification synchronized with the primary key's specification.

  2. Parent Table. The name of the foreign key's parent table goes here.

  3. Source Specification. This is where you indicate the name of the parent primary key. (Make certain you include the name of the primary key's parent table as well; this will make it easier for you to find the primary key's specification should you want to compare it to the foreign key's specification.)

  4. Description. Compose a description that indicates the foreign key's purpose within the table.Figure 10.53 shows an example of these modifications for an E MPLOYEE ID N UMBER field serving as a foreign key in an ORDERS table. You'll also adjust five elements in the Logical Elements category for the foreign key field specification.

     Figure 10.53. General Elements for the E MPLOYEE ID N UMBER foriegn key field in the ORDERS table.

  You'll also adjust five elements in the Logical Elements category for the foreign key field specification.

  1. Key Type. Set this element to "Foreign." This is a rather obvious change, but one that you can accidentally overlook if you're not careful.

  2. Uniqueness. You designate this element as "Non-unique" because you want to be able to associate a single foreign key value with any number of records in the parent table. In terms of our example, you want to be able to associate a specific employee with any number of orders. If you set this to "Unique" instead, you could associate a given employee with one order only, which would greatly limit his or her sales potential! (In the case of a one-to-one relationship, however, you'll designate this element as "Unique" because you want to associate a single foreign key value in the child table with only one record in the parent table.)

  3. Values Entered By. Unlike the parent primary key, you (or a user) will enter values into the foreign key; therefore, you set this element to " User ."

  4. Range of Values. You must set this element in such a way that you (or a user) can enter only existing values from the parent primary key. (You'll learn more about this and see a good example in just a moment.)

  5. Edit Rule. You normally set this to "Enter Now, Edits Allowed," although there might be instances (such as when the foreign key comes from a validation table) when you can set this to "Enter Later, Edits Allowed." Allowing edits of foreign key values enables you to fix mistakes. For example, you might have mistakenly entered employee ID number "100" for a given order when you meant to enter "110."

     Figure 10.54 shows an example of these modifications for the E MPLOYEE ID N UMBER foreign key field. (Note the setting for the Range of Valuesthis is one good way to set this element.)

     Figure 10.54. Logical Elements for the E MPLOYEE ID N UMBER foreign key field in the ORDERS table.

     In order for you to see the significance of these modifications, Figure 10.55 shows the Logical Elements category from the Source Specification . (Recall that this element is in the General Elements category; see Figure 10.53.)

     Figure 10.55. Logical Elements for the E MPLOYEE ID N UMBER primary key field in the EMPLOYEES table.

• It draws its values from the primary key to which it refers. By definition, a foreign key's range of values is limited to existing values of the primary key to which it refers. For example, you cannot enter an invalid E MPLOYEE ID N UMBER into the ORDERS table. Any E MPLOYEE ID N UMBER you enter into the ORDERS table must first exist as an E MPLOYEE ID N UMBER in the EMPLOYEES table. This ensures consistency among the values of both fields in both tables and helps to establish relationship-level integrity.

Review the foreign keys in each table to make certain that they conform to the Elements of a Foreign Key, and make the appropriate modifications to those that fail to do so. You really shouldn't encounter any problems if you've been faithfully following the design process up to this point.