Until recently, I was 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, I 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 I would want to use for trigger generation.

Then it happened. I 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 struck me that just as I could validate data in a document, I could validate data in a database.

I am not going to 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. I still don’t think we can build the perfect business rule code generator, but I think we can get pretty close.

In this paper, I will discuss the design of the Business Rule Integrated Database Generator (BRIG). This was developed for the Lancaster County Tax Collection Bureau (LCTCB) in Pennsylvania. Their vision and willingness to try new ideas made this project possible. Special thanks must go to Tom Baum and Marta Jo Cochran of LCTCB and Dave Heinaman, a senior consultant on the project. First I will discuss the types of rules that we wanted to support along with the other system requirements, then I will describe the design.

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

1. 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.
2. Rules that involve more than one column in the table being validated. For example, START_DATE < END_DATE.
3. 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.
4. 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.
5. 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 users, 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 is required.

The system had 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:

1. Our solution for the system involved building a small repository to hold our rules with some simple forms to support rule maintenance.
2. Rather than enforce the rules through lookups into the repository, we elected to use the repository to generate database triggers.
3. 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.
4. 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".
5. 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.)
6. 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.

Our models frequently use recursive structures to simplify the data model so enforcing recursive structures was key to the project’s success. We noted that a recursive structure can support several different data types of data. Each required its own rule. The recursive alternatives were:

Multi-Network Any element can be attached to any other element. Parents can have multiple children, children can have multiple parents. More than one independent structure can be stored in the table. Rule: No restriction on elements.

1 network Any element can be attached to any other element. Parents can have multiple children, children can have multiple parents. Only one network structure can be stored in the table. Rule: No restriction on elements. Number of elements connected to any individual element = number of rows in the table (must only issue a warning).

Multi tree Only hierarchical structures allowed. Parents can have more than one child but each child can have only one parent. A tree must have a top (an element with no parent). More than one independent tree is allowed. Rule: All CONNECT BY queries succeed, start with inserted element. (You can use CONNECT BY to detect loops in recursive tables. If there is a loop involving the START WITH element, the query returns an error.)

1 tree Parents can have more than one child but each child can have only one parent. A tree must have a top (an element with no parent). More than one independent tree is allowed. Rule: All CONNECT BY queries succeed, start with inserted element.

To enforce a single tree, there can only be one parent element (note: this can only issue a warning or you would never be able to change the root of a tree).

• Multi-Linked list Parents can only have one child, each child can only have one parent. Multiple independent linked lists are allowed. There must be a beginning to each list (no loops). Rule: No duplicate values are allowed on the recursive foreign key column. A CONNECT BY query succeeds.
• One linked list Parents can only have one child, each child can only have one parent. Only one linked list is allowed. There must be a beginning to each list (no loops). Rule: No duplicate values are allowed on the recursive foreign key column. A CONNECT BY query succeeds. Length of list = size of table (warning only).
• Multi Cycle Parents can only have one child, each child can only have one parent. Multiple independent linked lists are allowed. Only closed loops are allowed. Rule: No duplicate values are allowed on the recursive foreign key column. A CONNECT BY query fails(warning only).
• One cycle Parents can only have one child, each child can only have one parent. Only one linked list is allowed. Only a closed loop is allowed. Rule: No duplicate values are allowed on the recursive foreign key column. A CONNECT BY query fails(warning only). Size of cycle = size of table (warning only).

Either lists or cycles can be stored.

For all other combinations, you can use the less restrictive rule set. Note that all recursive cases are listed in increasing order of how restrictive they are (with the obvious exception of the last two cases).

It is probably not obvious to the reader how rules are supported in a database 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.

If you are familiar with generic modeling techniques, the data model for BRIG is relatively straightforward. Triggers are related to tables and columns. Both simple data rules and compound rules are simple master detail and recursive structures as shown in Figure 3.

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.

We have designed a generalized business rule generator (the BRIG system). It works perfectly for almost all simple rules and helps in the maintenance of complex rules (though they still require some custom coding).

My 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 I 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.

Dr. Paul Dorsey is the President of Dulcian, Inc., an Oracle consulting firm that specializes in data warehousing, systems development and products that support the Oracle environment. Paul is co-author with Peter Koletzke of Oracle Press’ Oracle Designer Handbook and with Joseph Hudicka of Oracle Press’ Oracle8 Design Using UML Object Modeling. Paul can be contacted at pdorsey@dulcian.com or through Dulcian’s Website at www.dulcian.com.