Choosing a Rules Engine Design

For those that don’t know. A rules engine is a way to harness powerful decision-making based on source data. These decisions are defined in rules.

Rules will generally consist of a query that outputs a list of successes or failures, depending on what needs to be reported on.

In my experience, I have only found two major methods for building a rules-based engine in SQL Server. They both have their pros and cons.

The trick is finding the middle ground which is defined as a third method between the two different extremes that will fit your requirement.

With that said we will discuss the two extremes — and the middle ground.

The Three Kinds of SQL Rules Engines

1. Extreme 1 – The Dynamic SQL Rules Engine

The first method involves dynamic SQL. Using dynamic SQL, the sky is the limit in regards to possibilities. The downfall however is maintainability. Dynamic SQL is very hard to maintain and even harder to troubleshoot and debug. My experience dealing with dynamic SQL is that it often starts out small, but once you’ve gone the dynamic route, it gets bigger and bigger and harder to maintain. Therefore I try to avoid this method altogether. If you’re into it, you’ll definitely find more job security, I’m not into that game personally.

2. Extreme 2 – The Hard-coded Rules Engine

The next method is to write each rule as a hardcoded procedure. When the rule executes, it returns back the list of results that fail (or pass) its test. Using this method, it is also likely that you will not code yourself into a corner.
The downfalls however is a slow turn around time in order to generate rules, along with a lack of code reuse. This lack of code-reuse opens the possibility to query the same tables using different methods — which is a negative.
To elaborate, imagine a set rules that need to query the same set of tables in order to find similar result set. This opens a negative opportunity to write queries that join agains tables differently — which sucks.

3. Middle Ground – The Modular Filter Rules Engine

This last method is my favorite, yet it requires some inginuity. It involves creating modular procedures that take input parameters as criteria, and return back “pass-or-fail” result sets. I got this method by reading this article http://msdn.microsoft.com/en-us/library/aa964135(SQL.90).aspx by Joshua Greenberg, Ph.D. In this method, you are basically defining procedures that take multiple parameters and return back a list of objects that pass or fail. This is a powerful method because it allows code reuse, does not require extensive maintenance, and also provides a quick turn around time for new rules to be defined. Regarding coding yourself into a corner, you have an opportunity to make that mistake based on the design you choose.

In the next article, we will provide a methodology to create a rules-based engine.