The DMCommunity’s Aug-2023 Challenge brought serious discussions at LinkedIn about the integrated use of SQL and Rule Engines. Instead of making generic statements about which technology is better, I prefer to answer this question for a concrete problem. My colleague Alex Mirtsyn has already provided a pure rules-based solution. As I was asked to provide a solution with OpenRules RuleDB, I extended Alex’s solution with access to a relational database directly from business rules. I will describe the resulting decision model in this post.  

The challenge itself is quite simple: we need to “assemble a single timeline for the soldier over a given service period that shows his/her hourly pay rate in any given time.” There are several interesting solutions that consider different time intervals and possible database organization. I would agree with Bob Moore that this particular problem formulation is too simple for a rule engine and everything could be done in SQL (especially considering that the information about soldier services is anyway is probably located in a database).

Still, the calculation of pay rates could be only a small part of a larger accounting system and could contain much more complex rules. For example, Alex Mirtsyn in his pure rules-based solution added a special rule for the retired soldiers and mentioned possible bonuses for other combinations of service characteristics. It would not be easy to express such rules in SQL queries. So, I tried to add a direct database access to a Alex’s solution using OpenRules RuleDB.

First, I added a new table “Soldiers” to the classic relational database for MariaDB or MySQL. Here it is:

To access this database I added the database properties to the file “project.properties”:

To read records from the table “Soldiers”, I did the following:

1. Created the OpenRules table of the type “DataSQL”:

It corresponds to a simple SQL query that finds all records in the table “Soldiers” with Soldier equal to the current value of the decision variable ${Soldier} and whose ${Service Date} is between StartDate and EndDate defined in the database.

2. Added the decision table “DetermineSoldierCharacteristics” that will apply the following rules to all selected records in SoldierCharacteristics:

3. Extended the glossary as follows:

It is the same Glossary that was used in the pure-rules-based decision model with the additional Business Concept “SoldierCharacteristics” that corresponds to the above DataSQL. Its decision variables Characteristic and Value refer to the attributes that correspond to the name of columns in the database table “Soldiers”.

I may use the same business rules used in Alex’s solution:

OpenRules automatically defines all inter-rules dependencies. For instance, here is the execution path for our main goal “Pay Rate” was automatically defined as follows:

• DetermineSoldierCharacteristics
• DetermineUnitRate
• DetermineServiceTypeRate
• DetermineProfessionRate
• DetermineCombatRate
• DetermineRankRate
• DeterminePayRate.

To test this decision model, I created a few simple test cases:

and executed this decision model. Here are the execution results for the second test case:

To demonstrate that such integration of a database and business rules is more flexible compared with a pure database/SQL solution, let’s give an additional $2 to active fighters who are paratroopers participating in combat. To do this, we may modify the way we calculate the final Pay Rate:

I added one more test case for Service Date 2022-10-16 when Bill was a fighter (not a driver) and thus was eligible for additional $2:

When I ran these test cases against the modified rules “DeterminePayRate” I received for the Test 1:

CONCLUSION. This decision model demonstrates a quite natural integration of a relational database and Business Rules within the same decision model. What is especially important is the following facts:

1. Businesspeople should not have problems understanding this decision model including the DataSQL table without becoming experts in SQL.
2. It is easy for businesspeople to enhance their business logic without any changes in the database and the way they read it.