Oracle8 Database Design Using Uml Object Modeling

Chapter 17: Implementing Business Rules

The automated support of business rules has been one of the hottest topics in the Oracle community for the last few years. The industry has tried using general business rule grammars (with no success), extending the Oracle Designer repository (with some success), and writing limited business rule generators (quite successfully).

Until recently, we were of the opinion that the quest for a general business rule generator was probably futile. There was no way that we would be able to get a reasonable set of business rules specified and generated. Business rules are too complex, and always require custom coding. After all, we already have a way of describing complex business rules; we call it PL/SQL.

At the same time, we have been building several limited business rule engines with great success. Each of these engines was more robust than the last, but none of them seemed to come anywhere close to something that we would want to use for trigger generation.

Implementing business rules within a database system is a challenging task. Traditionally, this has been handled by placing a small subset of the business rules into the database using a variety of techniques:

• Referential integrity
• Check constraints
• Datatypes

We have no illusion that all data-related business rules are easily supported within a data model. For example, there is no easy way to prevent loops in recursive structures when you are modeling hierarchical information. There is a whole other class of business rules that we traditionally handle through application logic. There has been much discussion of business rules in the last few years, butrelatively little discussion of actual implementations of these rules. This chapter will present a few real-world implementations of storing application logic as data. There have been attempts at developing a grammar for business rules. We have not found any of these grammars to be particularly helpful in developing a data model to support the types of business rules described in this book.

There are several underlying principles to the approach we used in implementing business rules:

• Business rules, components of these rules, connections between these rules, and their impact on other entities in the data model are all "things of interest" just as Employees or Departments are of interest to most organizations. You can use the same data modeling skills to model these as any other item of interest.
• If you are accustomed to the idea of generic modeling, including storing column information as data, overloading entities, etc., the style of modeling described here will be easier to understand. Modeling business rules involves the modeling of abstractions.
• Once the business rules are stored in the database, you can drive programs using data from the tables. However, if you use this method, you may end up with terrible performance due to the high number of database accesses. You may also need to utilize DBMS SQL frequently in your program logic. The ability of PL/SQL to support this type of data driver program is somewhat limited since there are no C++-style pointers and only limited indirect variable referencing through COPY and NAME-IN commands. Therefore, one of the possible strategies is to have all data in the system and write a program to take data and build PL/SQL packages (limited code generator).

Supporting Database Business Rules

At the Lancaster County Tax Collection Bureau of Pennsylvania (LCTCB) we created a Revenue Acquisition and Distribution System (RADS) requiring a complex business rules engine. RADS involves management and processing of documents associated with tax collection. There is a complex workflow associated with these documents involving data validation, corrections of errors in the documents, and processing and distribution of funds.

There are aspects of RADS that make it particularly appropriate for using object-oriented structures. First, there are a number of different types of documents, such as individual and employer tax returns. Second, the types of operations performed on all of the documents are very similar. We must count on being in an environment where we support new documents-that is, monthly rather than quarterly employer reports or entirely different documents because of tax reform.

As part of the RADS project, we designed a rules engine to support validation of tax returns (e.g., to make sure that line 1 + line 2 = Line 3 on your tax return). We built the code to parse rules and generate procedures. While writing up the system documentation for the engine design, it occurred to us that just as we could validate data in a document, we could validate data in a database.

We will not assert that the search for the ultimate business rule engine has ended. We are still only able to generate relatively simple business rules, though we can store and maintain the most complex rules in the system. We still don't think we can build the perfect business rule code generator, but we can get pretty close.

In the following sections, we will discuss the design of the Business Rule Information Generator (BRIG). First, the types of rules that we wanted to support will be discussed along with the other system requirements; then, the design will be described.

Rule Requirements

There are basically four different kinds of business rules that are commonly supported in systems that we design:

Rules to validate a single column, requiring no reference to any other column. Simple check constraints fall into this category. However, we also need to support complex formatting rules for things like postal codes. For example, Canadian postal codes alternate letters and numbers in a six-character field that is supported by traditional check constraints. Rules that involve more than one column in the table being validated. For example, START-DATE < END-DATE. Rules requiring a reference to another table. For example, each EVENT-DATE on a project must be between the START-DATE and END-DATE for that project. Rules that are only enforced under certain circumstances (conditional constraints). For example, assume we have a customer table that holds both individuals and organizations. If the type of the record is "PERSON" "ORG_NAME", then the LAST_NAME field must not be null; if the record type is "ORGANIZATION", then the field "ORG_NAME" must not be null.

• Redundant columns. These are columns that are defined as being functions of other data in the database.

Other System Requirements

There were several requirements for the system:

• We wanted to be able to store the rules in a repository that could be maintained by the user, which would then generate the appropriate database triggers. We wanted there to be a minimum of programmer effort.
• We wanted the enforcement of those rules not to grossly affect the system performance. · The system needed to be easy to use as there would potentially be hundreds of rules to maintain, and it could prove necessary to change the rules quickly.
• We needed the system to validate the syntax of the entered rules so that no invalid code would be generated.
• We didn't want to spend months building the system, so it needed to be relatively simple.
• The system needed to be able to support quite complex rules, and be able to grow over time. . . .