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
• Data types

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, but relatively 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. The most commonly cited is the Ross Method as described in The Business Rule Book 2^nd Edition, (Ronald G. Ross, Database Research Group, Inc., 1997). I 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 paper.

There are several underlying principles to the approach that 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. 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 many database accesses. You may also need to utilize DBMS sequel 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).

At one client, LCTCB, we created a system (RADS) requiring a complex business rules engine. The RADS system 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. If we built this system in a traditional fashion, any of these modifications would require significant restructuring of the system.

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.

Then, as part of the RADS project, we designed yet another limited engine to support validation of tax returns (to make sure that line1 + line2 = Line3 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 quest for the ultimate business rule engine has been achieved. 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 I think we can get pretty close.

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

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’, 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.

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.

The first debate occurred about whether or not to store our rules in a repository outside of Oracle Designer. By storing the rules in a separate repository, there were some trade-offs. On the positive side, rules could be stored in a complex structure not easily supported through user extensions. (We considered extending the repository to store the rules, but we wanted to have the system in production for a while before we made that decision.) We were interested in writing very complex code against repository extensions for a first version. On the negative side, we would be storing many of our business rules outside of the Oracle Designer Repository. This would put our business rules in two places.

We decided to build a separate rules repository. The rationale was that our projects are not built to simply support a set of rules; rather, they are built to support a class of rules. That is to say, the flexible business rules are part of the implementation, not the design.

BRIG has had a profound impact on our design process. Now, business requirements are placed directly in the repository late in the analysis phase. This tends to blur the line between analysis and development. As soon as rules are placed in the repository, they can be generated. No additional coding time is required.

The system has made us much more secure in our design process. If we decide that rules are too restrictive, they can be regenerated very quickly. Using BRIG, we can support a much larger percentage of our business rules without custom coding.

We made several key design decisions:

• Our solution for the system involved building a small repository to hold our rules with some simple forms to support rule maintenance.
• Rather than enforce the rules through lookups into the repository, we elected to use the repository to generate database triggers.

We noticed that if we supported rules that used custom functions that we could support arbitrarily complex rules through hand-coding some functions. Eventually, a function generator could be added to support generation of types of complex functions.

• We decided to build rules in two steps. First we would build elementary conditions, such as START_DATE < END_DATE, and then combine them using Boolean operators. Then we could simply refer to "RULE1 and RULE2".

The structure to hold the rules was rather complex, designed for non-intuitive data entry. To simplify this for users, we allowed them to enter the rules using normal programming syntax. We then wrote a parser to place their rules into the repository. (In practice, users wrote their rules in English and junior programmers entered the actual rules.)

We made an addition to the standard Boolean operators to support easier entry of rules. Specifically, we added an IF THEN ELSE structure to support conditional constraints (described above). "RULE1 => RULE2 else RULE3" would be interpreted as "(RULE1 and RULE2) or (not RULE1 and RULE3)". Similarly, "RULE1 => RULE2" would be interpreted as "(RULE1 and RULE2) or (not RULE1)".

We noted that rules fell into four categories for implementation:

1. Simple check constraints
2. Conditional triggers
3. Redundant columns
4. Recursive constraints

We had to support each type of constraint differently. Check constraints were limited to lists of values and ranges. Conditional triggers supported most complex rules. Redundant columns were supported with triggers (avoiding mutating tables was a significant problem). Recursive rules required a little different thinking.

It is probably not obvious to the reader how rules are supported in this structure. Note that we are storing rules using a parse tree. For example, the rule (Col1 + Col2)/2 < Col3 is represented in Figure 1.

Similarly, compound rules are stored in a separate (but similar) structure. The difference is that instead of combining parameters through operators, we are combining rules through Boolean operators. For example, the compound rule "(RULE1 and RULE2) or (not RULE3)" would be represented as shown in Figure 2.

Note that for rules, each element is either a function or a column. For compound rules, each element is either a Boolean operator or a rule.

The first test we set for BRIG was to see if it could support the complex data-related business rules for BRIG’s own data structure. Although it is only comprised of a few tables, the BRIG repository is quite complex. The following business rules could not be supported through referential integrity and check constraints:

• In Compound Rule Details, there is a strict logical hierarchy in the way ANDs, ORs, and Compound Rules nest. There is nothing in the data model to enforce the structural syntax of a correct Boolean statement. The Rule Component structure has the same problem.
• There is nothing in the model to enforce that the columns associated with the rule component must be from the same table that the rule is associated with.
• We cannot enforce that the Function Detail on a Rule Component is consistent with its parent function in the Rule Component recursion.
• When a Column is used in conjunction with a Function Detail, there is nothing to enforce that the data types are consistent.

Such rules can be entered into the system and generated. However, attempting to generate these rules points out the limitations in the rule engine. Each of these rules requires that we write a specific function to look up the correct data to make sure that model consistency is enforced. Some common functions such as summing up the details and storing them in a master (such as for a purchase order) can be classified and generated, but most other complex rules (such as ALL the complex rules for this system) still need custom coding. In applying the BRIG to a standard OLTP, the results are somewhat better. However, most complex rules require the writing of a custom function. BRIG works perfectly for almost all simple rules and helps in the maintenance of complex rules (though they still require some custom coding).

Our opinion hasn’t changed that a generalized business rule engine that would support all rules can never be built. If someone can build a grammar that will allow generation of rules such as the ones required by the BRIG data structure itself, then we will gladly retire BRIG to the scrap heap. Until then, BRIG stands as a pretty good attempt at a generalized rule engine that generates 100 percent of the code for simple rules and helps to maintain complex rules.

LCTCB has a very large volume of items to be processed. Currently, there are 250,000 tax returns processed annually. This, in itself, is not a very large volume. What makes LCTCB’s situation unusual is that 90 percent of this workflow volume occurs in a 2–4 week period.

Because of the irregular periodicity of workflow volume, we need to have a flexible system that can accommodate low, medium, and high workflow volumes at different times. Certain specific portions of this process will still need to either be done manually or automated with lower precision. Since we are trying to maximize revenue, we may want to adjust the criteria for rejecting a tax return so that manual processing is required. These criteria may need to be changed on the fly. In order to evaluate all of these changes, we will need a sophisticated, flexible reporting system to measure the performance and costs of various events.

There were many processes at LCTCB that were being performed manually that could have been automated. For example, all judgments regarding the detection of where errors occur in a return can and should be automated. In effect, we created an "Expert System." However, from all of the work done in building Expert Systems, we know that first versions are often notoriously inefficient. In addition, in the case of RADS, we were providing automated functionality that did not exist in the legacy system.

The process that was used at LCTCB to process tax returns was not stable. No matter how well expert requirements are gathered, they will change in the new system. Therefore, RADS needed to be flexible in the way that the rules built into it are processed.

What we envisioned for the new system was to flexibly define documents on the fly; store instances of these generic, created on-the-fly documents; and process these documents using complex workflow-related rules. These rules will govern not only the tasks performed but also store, as data, what these tasks are and what impact they have on the documents in the system. In RADS, we effectively constructed an Expert System to replicate the behavior of an expert tax return processor. The only behavior that we were unable to automate was the manual verification that the raw documents submitted (W-2s, letters, etc.) support the numbers on the tax return documents.

A great deal of careful analysis was needed for this system. Many portions of RADS evolved over time as our understanding of the LCTCB workflow and business rules deepened. The model we created was not the result of one short meeting. We first built one section that allowed us to think about how other portions of the system should be constructed. Early on, we recognized the need for generic documents requiring a structure such as the one shown in Figure 3.

In the Document Type and Document Variable bubbles, we define the document. For example, to define a W-2 tax form, we would create an object (W-2) in the Document Type class. Every line on the form would be represented as a separate variable in the Document Variable class. When a person files a W-2 and it is received by the system, we create one object in the Document table to represent the actual physical document and each value on the document becomes an object in the Document Value table. Each document value must be for a predefined variable in the Document Variable class.

The recursions of Document and Document Type are used in order to support repeating groups within documents. For example, on a W-2 form, you might have a list of business expenses and their associated amounts. We modeled this as if it were an embedded document; hence the recursion.

We also recognize that we needed a relatively sophisticated workflow system. We had built workflow systems in the past that were more basic, but this system required the implementation of full state transition diagrams.

For this system, the vision we had was that a document is always in a particular state. Based upon this state, there are one or more tasks that must be accomplished such as "Check a rule," "Execute a database procedure," or "Perform a manual task." As a result of performing these tasks, there will be an outcome. The outcome returns the document to another state. Diagrammatically, this process is shown in Figure 4.

This diagram conveys what we were trying to accomplish with our rules engine. In actually performing these operations, there can be many more tasks associated with a given state. The large boxes represent the states, with the tasks enclosed in the boxes. Lines represent the outcomes of the state boxes. Modeling these state transition diagrams in a relational database has gone through a number of iterations. The final result is shown in Figure 5.

In this diagram, a state is associated with a single task, which can be used by multiple states. It is the TASK that has various outcomes. Each of these outcomes results in a new state. With further analysis of this model, we discovered that the model didn’t make sense. Notice that tasks have associated outcomes, each of which takes us to a new state. This is the definition of a state.

For this system, we needed to support rules in the tax documents. Simple rules included ones such as "Line 15 (Total Gross Income) – Line 16 (Total Expenses) = Line 17 (Net Income)." However, other rules are more complex, requiring lookups to tax tables. Therefore, it was necessary to provide the ability to store any mathematical conditional statement that PL/SQL supports. We used a similar structure to the generalized business rule system discussed above.

The next requirement was to support assignment statements. Based on the detection of an arithmetic mistake, we might wish to assign that the value of one line on the form was equal to an arithmetic combination of other lines on the form. We noticed that the structure would also support assignment statements by merely restricting the rules for the assignment of equality operators. Therefore, the left-hand side of the rule must be a simple document variable, not a constant or a function. The right-hand side could be any mathematical expression. By including this requirement in the design, we were able to not only specify validation rules, but also assignment statements.

As part of RADS, we wanted to be able to base an outcome on a Boolean combination of rules; that is, "If Rule 1 and Rule 2 are true, but not Rule 3, this is the outcome." The structure of a compound rule is such that it uses rules as the components of the compound rule, but the structure looks like that of a rule. For rules, we used functions to combine elements. For compound rules, we used Boolean operators to combine structures. Because of the similarity in structure, we contemplated trying to incorporate rules and compound rules into the same structure. However, this made the model unworkable. Instead, we extended the model in the same way we did in the previous Business Rules example.

The final model supports all of the rules engine requirements. Role and Required Role entities were added to this version of the model to control who is allowed to perform which manual tasks.

We made several very important observations about the completed model:

• First, we had generalized the system to such an extent that it became not simply a model to support validation of tax returns, but data validation of any document (tests, questionnaires, etc.).
• Second, we noticed that we had essentially built an Expert System engine that would support the capture of the expert knowledge of tax return processors.
• Third, this model gave us the flexibility to change the rules whereby returns are processed very quickly and easily. For example, processing strategies could be changed and the system would include the ability to assess the impact of the change on the operation.

Most of the application development we used to support the RADS model is quite straightforward. There are two exceptions:

• Inputting rules
• Inputting compound rules

In both cases, forcing data input in a way that is consistent with the data model makes for a counterintuitive and user-hostile interface. To handle this, we built a parser to allow users to type in their business rules in a large text box. The parser takes a string such as "Line 1 + Line 2 = Line 3" as input, performs a syntax check, and automatically places that string into the structure. Then, within the structure, further validation is done to make sure that data types are consistent.

This example involves the automatic setting of record status based upon various values in the database. The goal was to assign status to an audit report based upon the value of five different variables in the report itself.

In performing analysis, the users originally perceived the problems as a tree search, finding the value of the first variable, possibly looking at the second variable, etc., sequentially. This represented a very complex flow, making it difficult to code. Rather than trying to implement the entire Beyesian decision tree, we recognized that we merely needed to regard the flow as a function with five input values and one output value. Fortunately, in this case, each of the values was categorical and the number of values was very small. There were approximately 100 possible combinations. Therefore, we decided to drive the rules from a table.

We created a simple table with six columns in it—one for each of the independent variables and one for the dependent status variable. The actual data model is shown in Figure 6.

The table for the status rule model is as shown in Table 1.

In order to determine status, there are three categories, four different classes, and the _YN indicator variables for a total of 96 different independent variable combinations. However, we still did not want to enter 96 lines of data if we could avoid it. In this case, if the category were "CLOSED," then the status would automatically be "CLOSED," thus making the status of the other variables irrelevant. This rule was enforced as "Category Closed = all other variables null." The null values indicate wild cards (any value is valid for that rule).

Using the wild-card notation meant that we had to be careful that the rules would never be inconsistent. For example, if we added the rule "(Category = closed, class = small, all else = null) = status PENDING," this would be inconsistent with our first rule, which says that class is irrelevant if "Category = Closed." We needed to put a BEFORE_INSERT or BEFORE_UPDATE trigger on the rule table to prevent inconsistent rule specification.

The START_DT and END_DT fields serve to keep track of how the rules change over time. The ACTIV_YN indicator is a redundant column so that the active rules can be found without an inefficient search for a null end date.

In this particular system, rules did not change very often. We put a single index on all six of the independent variables so that when we needed to update the status of an audit report, it only required an indexed search of the database. This method provided adequate performance. We contemplated writing a program to generate a PL/SQL package that would assign the status. However, creating a code generator would have taken more time. Since this was unnecessary for performance reasons, we did not elect to do so. We did encapsulate a function that automatically set the status and stored it as a function in a PL/SQL package in the server. This is useful in that if we choose to rewrite the engine at a later time, the code will only be in one place.

As we have described in this paper, the RADS system should be able to automate all processing of a tax return with the exception of validating that numbers were entered correctly from the supporting documents. This is an excellent example of the power and efficiency of using generic structures coupled with an object-oriented approach. By applying object-oriented thinking to a very complex problem, we were able to represent the entire complex workflow of an organization and create a model flexible enough so that, over time, an organization can continually re-articulate its business functions without having to make substantial changes to the underlying data model.

^© 1998 Dulcian, Inc.