As you define field specific business rules, there will be instances in which a rule imposes a constraint that defines a distinct set of valid values for a given field's range of values. (This obviously affects the field's Range of Values element in its field specification.) This set of values commonly comprises a relatively fixed number of entries, and the values themselves will rarely change. If the number of entries is rather high, however, you might discover that it's going to be slightly difficult for you to implement this rule. For example, you'll probably run out of room very quickly when you attempt to enumerate each of the values within the Range of Values element on the Field Specifications sheet, and implementing the entire set of values within the RDBMS could prove to be somewhat complicated. You can avoid problems such as these by storing all of the values in a validation table.
As you learned in Chapter 3, a validation table (also known as a lookup table) stores data that you specifically use to implement data integrity. You won't often insert, update, or delete any records within the table once you populate the table with the data you require. Validation tables usually (but not always) comprise two fields: The first acts as the primary key and is what you'll use to help you enforce data integrity, and the second is simply a non-key field that stores a set of values required by some other field in the database. Figure 11.13 shows two examples of validation tables.
In this section, you'll learn how to use the primary key field to help enforce a business rule. You'll learn how to use the non-key field later in Chapter 12.
When a business rule limits a field's range of values, you can enforce the constraint by using a validation table; the field will then draw its values from an appropriate field in the validation table. Establishing this type of rule involves two steps: defining a relationship between the parent table of the field affected by the rule and the validation table and making a modification to the Range of Values element of the field specifications for the affected field in the parent table.
For example, assume you're working with the SUPPSTATE field of a SUPPLIERS table, and you've defined the following business rule:
Any supplier we use must be based in one of the 11 contiguous Western states, Alaska, or Hawaii.
You can see that this rule imposes a constraint on the SUPPSTATE field's range of values, limiting them to AK, AZ, CA, CO, HI, ID, MT, NM, NV, OR, UT, WA, and WY. (According to the rule, you can't use a supplier based in some other state.) The easiest and most efficient way to establish this rule is to store these values in a validation table called STATES and then use the validation table as the source of the SUPPSTATE field's range of values.
Consider the tables in Figure 11.14. (Note the new symbol that is used to represent a validation table.) The SUPPLIERS table stores all the requisite data on the SUPPLIERS engaged by the organization, and the STATES table is a new validation table that will store the names and abbreviations of the specified STATES.
Your first order of business (no pun intended) is to establish a relationship between these tables. As you can see, there is a one-to-many relationship between thema single record in STATES can be associated with one or more records in SUPPLIERS, but a single record in SUPPLIERS will be associated with only one record in STATES. You already know that you establish a one-to-many relationship by taking a copy of the parent table's primary key and incorporating it within the structure of the child table where it becomes a foreign key. Although the SUPPLIERS table already has a field named SUPPSTATE, you'll replace it with the STATE field from the STATES validation table. (This is a reasonable modification because it is in accordance with the Elements of the Ideal Field and is consistent with the manner in which you establish one-to-many relationships.) Figure 11.15 shows the new relationship diagram for these two tables.
Now that the STATE field is a foreign key in the SUPPLIERS table, make certain that it conforms to the Elements of a Foreign Key (as outlined in Chapter 10) and set its field specification in the appropriate manner. Then set the relationship's characteristics in this manner:
Deletion Rule. Define a Restrict deletion rule for this relationship. You do not want to delete a state in the STATES table that is being referenced by records in the SUPPLIERS table.
Type of Participation. Designate an Optional type of participation for the SUPPLIERS table and a Mandatory type of participation for the STATES table. Although it's unnecessary for the SUPPLIERS table to contain any records before you can enter a new record in the STATES table, there must be at least one record in the STATES table before you can enter records into the SUPPLIERS table.
Degree of Participation. Assign a (1,1) degree of participation for the STATES table; as you already know, there must be at least one record in the STATES table before you can enter records into the SUPPLIERS table. Assign a (0,N) degree of participation for the SUPPLIERS table; any number of records in this table can be associated with a particular record in the STATES table.
Next, modify the Range of Values element of the field specification for the STATE field in the SUPPLIERS table using a setting such as this:
Any value within the STATE field of the STATES table.
Figure 11.16 shows the settings you've made within the Logical Elements category of the Field Specifications sheet for this field.
Now you must decide which actions test the rule. When you use a validation table to enforce a business rule, you typically want to test the rule when a user attempts to insert a new value into the field or update an existing value within the field. In either case, a violation will occur when the user attempts to enter a value that does not exist in the validation table.
Finally, fill out a Business Rule Specifications sheet for the business rule you've just established. Be sure to indicate the modifications you've made to both the field and the new relationship. Figure 11.17 shows the completed Business Rule Specifications sheet for your new rule.