Read an Excerpt
Chapter 11: Business Rule
Reviewing the Business Rule Specification Sheets
Once you've established the Business Rules you believe to be appropriate, review their specification sheets. Carefully examine the specification sheet and make certain that the rule has been properly established and that all the appropriate areas on the sheet are clearly marked. If you find an error, make the necessary modifications and review it once more. Repeat this process until you've reviewed every Business Rule.
Business Rules are an important component of the database. Along with contributing to overall data integrity, Business Rules impose integrity constraints that are specific to the organization. As you've seen, these rules help to ensure the validity and consistency of the data within the context of the manner in which the organization functions or conducts its business. Furthermore, these rules will affect the manner in which the database is implemented in an RDBMS and how it works with the application program used to work with the database.
It's important to understand that you will revisit these rules quite often. As you review the final structure, for example, you may determine that additional Business Rules are necessary. You may discover that several of the rules will not provide the results you had initially envisioned, and thus they need to be modified. It's also possible to determine that some of the rules aren't necessary after all. (In this instance, be absolutely sure to examine the rules carefully before you remove them.)
Keep in mind that the Business Rules you define now are bound to require modifications in the future; you will most likely need to add Business Rules in due course because of changes in the way the organization functions or conducts its business. The need to modify existing Business Rules or develop new ones comes up naturally-the organization inevitably grows and matures, and so does the manner in which it acts upon or reacts to external forces. These forces affect the manner in which the organization perceives and uses its data, which, in turn, changes the nature of the organization's requirements in terms of Business Rules.
The task of defining and establishing Business Rules is-as are so many other tasks within the database design process-ongoing. Don't be discouraged if you have to perform this task several times. Your efforts will pay great dividends in the long run.
Now it's time to establish Business Rules for Mike's database. You schedule a meeting with Mike and his staff to review the tables and relationships in their database. The first order of business is to define and establish field-specific Business Rules.
You begin the process by reviewing the PRODUCTS table. As you begin to examine each field, you try to determine whether any constraints are required. Upon reviewing the CATEGORY field, you remember that there was some question as to the values that could be entered into the field. (Refer to the Case Study in Chapter 9.) After some discussion with Mike and his staff, you compile a list of categories. Then Mike decides that the values for the CATEGORY field should be limited to those on this list to make certain that the staff does not arbitrarily invent new categories. Because Mike wants to limit the values that can be entered in the CATEGORY field, you define an appropriate Business Rule to properly establish the constraint. The new rule is stated as follows:
These are the settings you've made for the table relationship characteristics:
- There is a restrict deletion rule for the relationship.
- The type of participation for the CATEGORIES table is set to "Mandatory."
- The type of participation for the PRODUCTS table is set to "Optional."
- The degree of participation for CATEGORIES is set to "(1,1)."
- The degree of participation for PRODUCTS is set to "(O,N)."
Remember that by establishing this relationship, you've replaced the existing CATEGORY field in the PRODUCTS table with a copy of the CATEGORY ID field from the new CATEGORIES table. Because the CATEGORY ID field in the PRODUCTS table is a Foreign key, you now make certain that it conforms to the Elements of a Foreign key and make the appropriate modifications to its Field Specifications. Then you modify the Range of Values element to read "Any value within the CATEGORY ID field in the CATEGORIES table." Figure 11-19 shows the Logical Elements settings of the Field Specifications for the CATEGORY ID field in the PRODUCTS table.
Now you must decide when the rule should be tested. As you already know, you typically want to test a rule established with a validation table if the user attempts to insert a value into the field or update an existing value within the field.
Finally, you complete a Business Rule Specification sheet for this new Business Rule. This specification sheet will reflect the modifications you've made to the Field Specifications for the CATEGORY ID field as well as the characteristics of the relationship between the CATEGORIES and PRODUCTS tables...