17 Nov 2007

Business Rule Engine

So I read an interesting blog recently that's linked from Sheeri Kritzer's Blog. It went to say that "Embedded SQL" is bad. Which is to say that stored procedures are good and that you should never let a developer mess around with the database. In Sheeri's blog there is an ongoing argument about it as you read this post.
Well, I already wrote in my blog that I do support the stored procedures approach. Maybe not fanatically as the original blog post, but I do agree that developers are not really interested in the database, just the results they are looking for.
So I had an idea. I was thinking how can help manage the way developers interact with the database, to give both DBAs and developers more power and flexibility. And I came up with this:

Business Rule Engine

Well maybe engine isn't the best word. Maybe interface is better, but anyway... My idea is to create an engine that creates and manages "rules" that are applied to the way users interacts with the data in the database. This means that you have a configurable and manageable way to control the interactions with the database. It will give you an overview of all the "rules" that govern your data.

An example of a rule - I want to be able to see all the new orders that came in in the last 30 days.
Sounds simple enough. You setup a "rule" or in this case you can just setup a View, call it "NewOrders" and you are done. Then your application calls on NewOrders and gets this information. What if you decide one day that for some reason, 30 days is not enough and you want 45 days. You then goto the "configurable" engine and change the number of days from 30 to 45.
So as you can see here, the rule engine lets you apply a rule (orders in the last 30 days) and it lets you change this rule in the future if your business needs to make changes.

Another example of a rule - I want to check if all the orders in the last year are orders that are considered "big orders". Big orders are orders that are over $500. I can make a function that checks if the value of the money is over or equal to $500. If its true, then its considered a big order, if not, then its not considered a big order. I then create a view for all the orders in the last year that are considered big orders, using the new function. If in the future, I decide that $500 is too small and $1000 is now a big order, I can simply make the change in the rules engine.

Why Should the Database Handle the Rules Inside the Database?

Databases handle data very very well. I feel its a big waste to get lots and lots of data from the database, process it, apply rules to it and print out the results. All of this could be done in the database and save lots and lots of data being passed in an out of the database (reducing network traffic drastically).

I also believe that the database already has a lot of tools to manipulate data and to do so in the manner in which the database knows the fastest ways to do it.
Most of the content management systems usually just manipulate data in some way, they do not do very heavy calculations and it would be very useful to use the built-in functions that databases have to assist them.

Possible Benefits to MySQL

Some of you may have been noticing a trend in the database world to integrate with more middle-ware applications to provide better and more complete services. We need to give MySQL more power in the area of providing solutions.
MySQL is great at performance and I believe in the near future that it will also be able to handle enterprise-level data loads, but I see databases becoming more popular when they simply provide more and more features that are easily connect-able and manageable to existing systems. The more features it will have, the easier it will be for users to develop their systems, thus making them better and raising the general level of the applications that are made through MySQL. And since MySQL is very very popular, any major improvements or added features effect many many people.

Possible Benefits to the DBAs

This will help empower programmers and DBAs to manage simply and effectively the data that goes in and out of the database: how it is viewed and how it is updated.
It will help you, as a DBA, take a bigger responsibility for the overall development of the application, but at the same time, give you tools to get a better understanding and overall view of what needs to be done and how to do it. For example, if you, as a DBA, design a database and afterwards also forced to create some of the rules how the database is used, it might make you realize that there needs to be more changes to the database design early in the design process.

It can allows you to manage this from outside the database using your own programing language to make changes. So that the possibility to make changes to the database (not to tables but stored procedures) can be done through the application by connecting to the rules engine

Benefits of Configuration Management

Configuration management is a way to manage the business rules separately and not bury it in the code. This engine should give you an overview of all the business rules and where they can be changed. Even if, or especially if, the business logic is kept more in the application layer, this will allow the application layer to have direct access to the business rules in the database using an interface that can be set up. This should allow you to manage this from outside the database using your own programing language to make changes.

One of the big issues with all applications are maintenance and the ability to make changes (because of changes in the market) or make new features. Using configuration management approach, these issues are handled much easier then without applying them. Maintenance is actually one of the main considerations that business ask when thinking about purchasing or developing an application.

This is also a good place to keep documentation so that people can find what they are looking for faster and understand what it does faster.

Making Things Simpler

It should encourage the use of database developing while not really needing a lot of knowledge to start using it. If you manage to create business rules in your database, it can unburden the process to do it in the application layer, therefore possibly speeding up the entire developing process and introducing new products or features faster.
For example, if you need to make a PHP website and in a few lines using the rules engine you managed to do some procedure that saved yourself 100 lines of complicated PHP code, then you can finish developing your website faster and better (because you can test a few lines of code much easier then 100 lines of code)

How will it do it?

The Engine will actually be a table that would be able to create, alter, delete and manage stored functions, views, triggers and prepared statements. Each line in the table will be a rule and each rules will either be a view, function, trigger and so on.

Like this for example:

ID Type Name Comment Process FirstValue SecondValue
1 View NewOrders Shows all orders in the last 30 days Select * from Orders where OrderDate>= $FirstValue; now() - INTERVAL 3 DAY
2 Function BigOrder If the order is more then $500 then its a big order Return If ($FirstValue >= 500,True,False);

3 View ShowBigOrders Show all big orders in the last year Select * from orders where OrderDate>= $FirstValue and BigOrder(total)=True now() - INTERVAL 1 YEAR

This is just a rough idea, but it explains what I am looking to do. Every time you add or change a line, a trigger sets off and creates or alters a function, view or what needs to be changed.
So, for example, rule 1 - the engine automatically generated a new view called "NewOrders" using the Select written in the process and also using the first value which is 30 days before today. So its a simple case of "Drop if exists, Create.....(select statement here)... END". I think its very straight forwards.

I would like this to go into more detail and do more and more things, but the idea is more or less this example. I think it should also hold prepared statements, since I don't think MySQL does that.


The Business Rules Engine lets you create stored procedures automatically in your database. These stored procedures include rules of how you would like the database to be used. The application layer communicates with the stored procedures in the database. The rules engine manages the rules in a table format which allows changes to be made when needed.
The application layer can communicate directly with the rules engine table to make changes or integrate with its configurations and rule management.
This will let you, develop applications faster and maintain your applications easily.

So obviously, I think its a good idea, but what do you think?
Please let me know by posting a comment.
Thank you for reading my blog.


  1. We have a large core database that much of our business activity revolves around, and I've actually written things like you describe to solve a couple of problems:

    1) To enable us to automatically generate new (Web page) search interfaces for users from parameterized SQL queries in a table.

    2) To find user-entered data that may not match business rules.

    I'm not sure about this approach for internal interfaces, but we often need to be able to define subsets of data for end-users to access.

  2. Sounds good -- sounds like a lot of CMS systems, like Drupal -- they're basically designed so people can do work instead of having to code. Good for casual users. I think most businesses will want to build their own solution once they get pretty big, so they can specialize and tailor the app's performance to their needs.

  3. Hi Jonathan,

    I like your idea a lot, and I actually made something that may help; it's called CodaServer and you can get it at codaserver.com.

    It takes a different approach than you specified, but I think it would achieve many of the same goals. It gives developers a way to specify the business process and data model behind their applications and then enforces this process across different application languages.

    Say, for instance, "big orders" get routed differently than small ones; they enter a verification step where an account rep would have to make sure the customer has a certain credit level. Before the order could enter that "verified" status, CodaServer would let you tie a trigger to that operation that tests for the "big order" and rejects the operation if it doesn't meet that criteria. The data integrity is guaranteed across application languages, and the "big order" logic is only in one place.

    You could also have a function (stored in CodaServer, but available from application code) which contains your "big order" logic to communicate this back to the application code and help define the business rule-aware UI.

    The table-based approach is really helpful as well. Generally, though, it is more task-specific than your sample and difficult to generalize without needing to have lots of complicated business logic in the application code. The simpler the database tables, the harder the application.

  4. Hey Mike

    Your project looks very promising and I will keep my eye on it.
    Good luck with it in the future!

  5. Alternatively use:

    PHP Business Rules


  6. You can try to look at Gandalf Decision Engine, it's open source and have GUI that allows you to manage all rules on the fly: https://gndf.io/

  7. How will the Database engine be able to optimise the dynamic SQL that this will be thrown at it?

    What are the execution plan caching implications here?

    Just asking the question in case you have some pointers in that direction (performance related considerations)

  8. So you cannot really store the business rules in the database and JOIN then on a query in a fast manner.
    There are certain database overheads which makes it considerably slower than writing it in your used programming language.
    What you can do is store the business rules in the database, but then generate case statements in your code with a code generator. Alternatively, you can get a business rule engine which will do that for you as well as have some sort of process to decide which rule to apply over others.