Business Rules - Database Design for Mere Mortals - page 357

You start the process by reviewing the PRODUCTS table. As you examine each field, you
determine whether it requires any constraints. When you come upon the C ATEGORY field,
you remember that there was some question regarding its range of values. (Refer to the
Case Study in Chapter 9 , “ Field Specifications . ”) You discuss this issue once again with
Mikeandhisstaff,andyoufinallycometoaconsensusonadistinctlistofcategories.Mike
then decides that the values for the C ATEGORY field should be limited to those on this list
to make certain that the staff does not arbitrarily invent new categories. Based on Mike's
decision, you define an appropriate business rule to establish the constraint:
Invalid product categories are not allowed.
There are a number of items in the list of possible categories, so you decide that the
best way to establish this rule is to use a validation table. You create a new table called
CATEGORIES and then establish a relationship between it and the PRODUCTS table.
Next,youdiagramtherelationshipandsettherelationship'scharacteristicsintheappropri-
ate manner. Figure 11.18 shows the results of your work.
Figure 11.18. The relationship diagram for the PRODUCTS and CATEGORIES
tables
Here are the settings you used for the relationship's characteristics.
• There is a Restrict deletion rule for the relationship.
• The CATEGORIES table has a mandatory type of participation.
• The PRODUCTS table has an optional type of participation.
• The CATEGORIES table has a (1,1) degree of participation.
• The PRODUCTS table has a (0,N) degree of participation.
Remember that by establishing this relationship, you've replaced the existing C ATEGORY
field in the PRODUCTS table with a copy of the C ATEGORY ID field from the new