Repository-Based
Dr. Paul Dorsey, Dulcian, Inc.
Introduction
Ensuring that data is valid is one of the core requirements of most information systems. Many data validation requirements are easily supported by the database or application through simple properties. Properties such as data type or length are so simple that it is tempting to say that these rules should not even be independently handled, but simply a part of the data structure. However, this is often not the case. For example, in the situation where customer information is periodically accepted from an external data source, the raw data should be entered into a table for manipulation and validation, even if it violates rules. The mechanism handled by SQL Loader which takes the rejected records and writes them to a separate file is neither convenient nor user-friendly. It is much easier to allow the data file to enter into the system with no validation rules whatsoever and then apply the validation rules to the records now comfortably stored in the database. At that point, it is possible to identify which business rules have been violated by the entire logical record.
Many business rules, especially complex rules involving multiple records, cannot be practically enforced by the database. Even if it is possible to enforce these rules in the database, it is often not desirable to do so. It is frequently better to allow the invalid data to be entered into the system so that it can be corrected and made valid at a later time. There is another reason for handling validation rules independently. The validation of information often does not take place until a particular point in a logical object’s process flow. For example, a merchandise order needs to be valid before it is processed. Sometimes particular validation rules are contingent and may only apply to an object under specific circumstances. Numbers on a budget may be considered valid if revenues “are close to expenses” whereas in an accounting system, for each journal entry, debits must equal credits to the penny.
Validation rules are often very complex and span multiple database tables as demonstrated in the following examples:
· Ensuring that the dates where a person resided in a specific place are later than that person’s date of birth involves comparing information that resides in multiple tables.
These are just a few examples demonstrating that validation business rules are not simply properties in the database and cannot be easily handled through database triggers.
What is the
appropriate solution for handling validation?
Traditionally, validation rules are embedded in code
scattered throughout data entry applications, creating a maintenance nightmare
where even finding a specific rule can be difficult, let alone changing it.
“What are all of the validation business rules being enforced?” is an almost
impossible question to answer. One solution to this dilemma is to collect all of
the business rules into a single code structure. Traditional Oracle shops may
create a large PL/SQL package. Java shops might create a large collection of
classes to reside in the middle tier. Users of JDeveloper may use the
Application Development Framework Business Components
(
Not only are there a large number of validation rules, but these rules are constantly changing. Validation rules are more volatile than most other kinds of system requirements. This is not only due to changes in the business, but also due to the fact that users do not always recognize that the specified rules have many exceptions. The conditions under which many of the rules are enforced need to be continually refined.
Why haven’t existing systems collapsed under the weight of
The optimal solution is to develop some type of rule grammar and/or repository to support all of the validation logic. This paper proposes a solution involving both elements, namely, a simple grammar as well as a small repository.
Case Study:
The Problem – Description of Project
The project that inspired the solution described in this paper was a recruiting system designed and built for the U.S Air Force Reserve. During the recruiting process, there is a great deal of information gathered about individuals being admitted into the Reserve. In addition to the standard information, for security reasons, an individual’s education, employment, residence and medical histories are all gathered. Security clearances are handled by a different agency. Therefore, all of the information collected must be bundled into an XML file and sent off for processing by a separate organization. There are hundreds of logical rules associated with the data in this XML file. Some of these rules are very complex. For example, there cannot be any gaps in an individual’s employment history. The document describing all of the rules is summarized in a 22-page Excel spreadsheet, where each row is a separate rule. The narrative description of the rules is contained in a 250-page monograph.
From looking at these rules and performing initial testing, it was clear that the task of validating the XML file would have required thousands of lines of code. In addition, the intention was to reuse the solution for other military service branches, some of which might be interested in a Java-based solution or some other programming language. Therefore, having to create massive amounts of code that might have to be re-written in another language was not a viable option.
For these reasons, the decision was made to use a repository-based approach.
Case Study:
The Solution – Validation Rules to Support Security Clearance
Validation
Initially, a one-table repository to handle the validation rules was envisioned. For rules involving multiple tables, views would be created joining those tables, and rules would be written against these views. For example, in the Contract table, the “Start Date must be less than End Date” rule was written as follows:
:StartDate < :EndDate
For a rule stating: “Every department must have at least one employee,” a view would be created for the Department table that would include the number of employees in that department. The rule would look like this:
:NumberofEmployees > 0
This architecture would have worked, but would have meant building and maintaining a large number of views with a lot of hand-coded PL/SQL to generate the calculated columns. Therefore this approach was abandoned fairly early on in the project. The architecture was too limited. Managing the rules in this environment would not have been significantly easier than writing them all in code. Further, if forced to move into a Java/XML environment, potentially not even using a database, the entire concept of using views would fall apart.
It was therefore decided to extend the validation rule grammar to support, not only references to columns in the table, but also take advantage of the parent-child relationships between the tables in order to unambiguously reference data values in a different table when running a validation rule.
The syntax used to accomplish this is as follows:
:_Child to refer to child table (the “many” table in a 1-many relationship)
:_Parent to refer to parent table (the “1” table in a 1-many relationship)
In order to say that the employee hire date must be greater than the create date for the parent department, the rule would be associated with the Employee table and be written as:
:_Parent.Department.CreateDate < :StartDate
For referencing the child table, there are usually many child records, so some type of aggregator method is called. To indicate that each department must have at least one employee, the rule would be associated with the Department table and be written as:
:_Child.Employee.Employee_OID.Count > 0
The grammar was designed to support all standard aggregation functions (Sum, Count, Min, Max, etc.).
It was also necessary for this project to filter the child rows, so a WHERE clause was added to the method call. For example, to enforce the rule that the department must have at least one active (isActive = ‘Y’) employee, the rule would be associated with the Department table and be written as:
:_Child.Employee.Employee_OID.Count(where = :_Child.Employee.isActive = ‘Y’) > 0
If there were more than one relationship between the two tables, there had to be some way to identify which relationship was the correct one to use for the join. In this case, the foreign key column was specified in the code. In the above example, if the foreign key column in the Employee table were DepartmentEmployer_OID, then the rule would be associated with the Department table and be written as:
:_Child.Employee(DepartmentEmployer_OID).Employee_OID.Count(
where
= :_Child.Employee.isActive = ‘Y’) >
0
It is apparent that these rules can eventually become quite complex to read so it is just as easy to write “real” code. There was one rule that was so complex that hand-coding was used, even though the grammar could support the description. However, out of the 22 pages of rules, there were only a few rules that required any hand coding. Even the “Debits must equal Credits” rule for a journal entry is not too difficult to enforce. This rule would be associated with the JournalEntry table and written as:
:_Child.JEDetail.Amount.SUM(where = :_Child.JEDetail.DrCrType = ‘Dr’) =
:_Child.JEDetail.Amount.SUM(where = :_Child.JEDetail.DrCrType = ‘Cr’)
Contingent
Rule Execution
The next problem encountered was that the rules were only contingently being executed. For example, if the “Debits must equal credits” rule is only for “Financial” transactions that are “ReadyToBeProcessed,” then this rule would be associated with the JournalEntry table and be written as:
(:_Child.JEDetail.Amount.SUM(where = :_Child.JEDetail.DrCrType = ‘Dr’) =
:_Child.JEDetail.Amount.SUM(where = :_Child.JEDetail.DrCrType = ‘Cr’) )
and :JEType = ‘Financial’
and :Status = ‘ReadyToBeProcessed’
Now the rule is starting to look a lot more complex. In this system, there might be many similar rules for “Financial - Ready to be processed” journal entries. It was decided to move the conditional part of the rule to its own property. Then the rule would be associated with the JournalEntry table and be written as:
Condition: :JEType = ‘Financial’
and :Status = ‘ReadyToBeProcessed’
Rule: (:_Child.JEDetail.Amount.SUM(where = :_Child.JEDetail.DrCrType = ‘Dr’) =
:_Child.JEDetail.Amount.SUM(where = :_Child.JEDetail.DrCrType = ‘Cr’) )
This was also the point where the decision was made to use a multi-table repository. Since object reuse was the driving force in the repository design, every element was created to be reusable.
Description
of the Rules Repository
The rules repository was implemented in an Oracle
database. A Rule is grouped
by Project. A Rule can belong to any number of Projects through the RuleUsage table.
This was done so that the same rules could be used in various
contexts. For example, there is one
set of rules that validates whether or not a customer can be sent for a security
clearance, but only a subset of those rules are relevant if the customer is
being processed without a security clearance. Note that there is an active
indicator in RuleUsage. This is so that a rule could
be declared as active for debugging purposes.
A rule is optionally attached to a Condition. A Condition is a Boolean expression
indicating whether or not the rule should be invoked. In practice, about half of
all rules have conditions attached to them.
A Validation is the basic rule that is enforced (e.g. :StartDate < :EndDate). It
has a many-many attachment to the Rule table through RuleDetail. A Validation can also be grouped using a ValidationGroup and then attached as a group to a
rule.
Note that a Rule, Validation, and Condition are all attached
to a specific table. Theoretically, these objects could be reused across tables
if the column names were exactly the same. However, this idea seemed to
unnecessarily complicate the model.
Error messages are built from the Condition and the Validation error_tx. The
user enters a user-friendly error message for the Condition and the Validation.
The system then uses that text to build the error text if the rule fails. For example, assume a rule on the
Department table with the following repository values:
Condition
Rule_tx = :Active_YN = ‘N’
Error_tx = ‘the department is inactive’
Validation
Rule_TX = :endDate is not null
Error_tx = ‘there must be a valid end date’
If the rule fails, the generated error message would be: “If
the department is inactive then there must be a valid end date.”
Since the system is created with reusable components, the error messages can be overridden for a specific usage. The Condition error_tx override is stored in the Rule class, and the Validation error_tx override is stored in the RuleDetail class.
A UML diagram of the rules repository is shown in Figure 1.
Figure 1: Rule repository data model
User-Friendly
Rules
“For each Department
where the department is inactive (active indicator = ‘N’), there must be a valid
end date (end date is not null).”
Note that the names of the columns in the generated rule include the user-friendly names from the repository rather than the actual column names.
The
Validation Repository Manager
The level of object reuse in the system made it difficult to create a user interface for entering and maintaining rules. It is helpful to think of a simple master-detail relationship between the Rule table and individual validations. However, rules are reusable across different logical applications and individual validations are reusable across individual rules. The result was an application built with a simple master-detail relationship between rules and validations from a user perspective. The complexity of the many-to-many relationship is hidden from the person entering the rules. When a user specifies a new validation in a rule, a selection can be made from an existing validation, or a new one can be entered. When viewing validations or rules, users can see how many times each of these objects is used elsewhere in the system. This prevents those entering the rules from modifying an existing rule or individual validation and causing inadvertent side effects elsewhere in the system.
It was also challenging to communicate clearly to users what actual error message would be generated for a particular rule. The error message could be specified at the object or object usage level, both for conditions and individual validations. A button was added to the user interface to generate a sample error message that users could read to validate the appropriateness of the message in each context.
Screen real estate in such a complex system was also a problem. In addition to rules, error messages and descriptions in large text boxes, there was an English language translation of the rule displayed. This problem was solved by using a combination of on demand popup windows and scrolling text fields.
It became important to find and navigate to particular
validations and rules. A separate Rule Finder was built where users could enter
search criteria. Rules satisfying the entered criteria would appear in a list.
Double clicking the rule navigated to the selected rule in the
system.
Rule
Enforcement
Once the rules have been specified, they must be enforced. For validation, this means that when objects fail a validation rule, some error message is generated. Up to this point, rule enforcement has not even been mentioned, nor has any specific environment been specified. This is the core idea of the business rules approach. The representation of the business rules is independent of the enforcement of the rules. It is possible to determine the appropriate logical grammar to describe the rules and in what context they will be enforced without making any assumptions about the physical representation of the data or the way in which the rules will be enforced.
In using the rules approach to building systems, approximately 30% of the time is spent designing the repository and developing the rule grammar. Another 10% of the time is spent creating the appropriate user interface to work with the repository. Writing the code generator/enforcement mechanism requires approximately 10% of the project time. Entering the rules into the repository consumes the remaining 50% of the time. This breakdown indicates that even drastic changes to the way in which rules are enforced will only require a relatively small amount of effort (a few days to a few weeks of effort).
There are various alternatives, each of which is achievable and surprisingly simple to implement:
1.
Interpreted mode: Leave the rules in the
repository and generate the code using an EXECUTE IMMEDIATE mechanism. This is
the simplest alternative and is frequently used for a proof-of-concept. The
downside is performance. Every rule that is enforced requires an EXECUTE
IMMEDIATE operation. If there are only a few rules, this mechanism works well;
however, if the number of rules is in the hundreds or thousands, severe
performance degradation will be experienced.
2.
Generate PL/SQL into the database (Compiled
mode): Using this alternative assumes that the data exists in Oracle tables and
straightforward PL/SQL will be generated to execute the validation logic. The
downside to this approach is that changes to the rules require regenerating the
code. If everything is generated into a single package, this can require up to
30 seconds each time the code is regenerated. A 30-second recompile time might
not seem long, but in a debugging cycle with numerous iterations of rule
tweaking, waiting 30 seconds for each recompile is not desirable. The main
benefit of this approach is its speed at runtime. By contrast, using the
interpreted mode instead might require 30-40 seconds at runtime.
3.
Generate Java to run against XML: In order to
perform validation away from the Oracle database (for example, to run on an
isolated laptop for later upload to the database), it is necessary to validate
without access to Oracle. This can be done by generating Java code to perform
the same validations against an XML file. PL/SQL code generators have been
successfully migrated to an XML data source using the Sunopsis utility that allows the use of SQL to access an XML
data source.
For the project described in this paper, the Compiled mode approach was used for version one. For the next version, the third approach will be used.
In addition to simple error messages, it was also important for the Validator to provide information allowing users to identify the reason(s) for the validation failure. The following information was provided to users:
· Object being validated when the rule failed
· Value of each field referenced in the rule
· Place in the user interface where the information could be corrected
The ultimate goal was to allow users to double click any field referenced in the error message and navigate to the appropriate portion of the application user interface.
A screen shot of the repository manager is shown in Figure 2:
Figure 2: Repository Manager User Interface
Lessons
Learned: The Benefits of Repository-Based Development
This project was a great success for the repository-based development approach. The time spent building the architecture and generators was more than offset by the speed at which hundreds of rules could be supported. The validation engine built makes further rule specification extremely rapid.
As more and more business rules are moved into repositories and out of code, the benefits of repository-based coding have become more and more apparent. It is easier to make objects reusable and they are far easier to manage in a repository than as code objects. It is also easer to make changes to the repository and perform an impact analysis of potential changes.
At some level, it feels like a “better” way to do
programming. With very little
development time required to create a repository manager, a more efficient IDE
for application development is created than can be built using tools with
millions of dollars of development effort such as TOAD for PL/SQL, JDeveloper or
Eclipse for Java. One can only wonder what could be achieved in repository-based
development if it attracted the attention of one of the major software
development vendors.
About the
Author
Dr.