1 Dec 2007

Simplified Business Rules Engine

So I was thinking about some of my ideas that I had already previously written in my blog and I was also doing research on some things. Considering that I only recently started database developing, there are some things that I have some slight understanding in. There are also things that I dont have any clue about. But I think that that plays to my advantage when coming up with new idea, since I am not forced into any mindset. Recently a lot of my thoughts and research clicked together and I had a really good idea that I'd like to share with you. I think its somewhat revolutionary and yet simple at the same time.

Simplified Business Rules Engine.

I'd like to start off by first getting your appetite's wet. I am going to say that this idea includes the following buzz phrases:
Business Rule Engine (duh), Expert System, Business Intelligence and Collective Intelligence.

Ok, now that I amazed you with the buzz phrases, lets continue.

I got this idea, thanks to my dad. My dad LOVES his excel sheet. Oh boy, does he ever. The man has some pretty complex things he needs to do, which I would argue he needs a full-on application to handle them, but he sticks to his excel.
Why does he do that? Well... excel is pretty comfortable. You have your data displayed in front of you and you also have some functions that rely on other cells and they will change accordingly.
At some stage, you begin to see something happening on the page. Some figures are with a red background because they went under a certain number. Some figures show that this guy didnt pay for 45 days and thats more then hes allowed. Some products will be out of stock on so and so dates. And all this on 1 excel sheet.
What just happened? Well, you took regular data and you added some functions to it, took some data from another place, put some conditions that if a number is above or below something then do this....
and in the end, you have a fully functional program (that only my dad can understand) that gives you all the relevant information you need to see whats going on in the business.

Then I was thinking to myself, why don't we have something similar in our database? I mean, sure, we can do exactly the same using selects and views. But why not also have fields or columns that contain inside of them a function and become dynamic?
So I started thinking about making something properly (so that I wont put it on my blog and have people ridicule me) and here is what I have come up with:

  • I need to have an engine in the database that can contain both data types and functions.
  • The engine needs to cache the results for the functions
  • The engine has to generate the results for these functions and store them until they become irrelevant, due to something being changed. Meaning, when something has been changed and changes the result of the function, the engine should erase the result and processes it again at a later time.
  • The engine should either generate it when the user requests them and then cache them or generate them when something else had been changed - an event - and in turn forces the result to change. If the cpu is not doing any serious work, then the engine can process the erased results and cache them.
  • The engine should have functions for when data is inserted so that the inserted data can be verified so to retain its quality. (same as the trigger - before insert)
  • You cannot insert information to a field that is a function.
  • If the function came back with an error, then the result is null

An example of how a table might look like would be like this:

Sample Table

Name DataType Restriction
ID INTEGER This > 0 and this < 4294967295
FirstName VarChar(20) regex(something)
LastName VarChar(20) regex(something)
Email VarChar(45) regex(to check email)
Name Function
IsLastNameLongerThenFirstName if(len(this.LastName) > len(this.FirstName), 'Y', 'N')
DoesEmailHaveFirstName if(instr(this.Email,lower(this.FirstName)) >0, 'Y', 'N')


I think there should be some constraints regarding the functions. If people go abit too crazy with them, you could have a situation that might bring the database to a screeching halt.
But in all likelihood, the functions might be processed when the database is idle and you might not notice how long they take to run.
And again, the solutions that this might solve can, of course, be done using selects, views and functions.

How this can benefit the DBA?

Well, my last example wasn't that great in terms of what the DBA can benefit from it (apart from keeping data quality at a high level). So I made another example.

So when I tried to do this example, I kind of "let my self go" a bit and just threw some ideas in the air. So the questions represent the possible functions that can be used.
All these questions, while can be a bit extreme, explain what kind of information the business owner would be interested in.
In this example "was the customer satisfied", how would you know that? Maybe you would need to put in some survey or check how many clicks he/she had to do before making the order on the website. Whatever you choose, you already have some insight on what kind of extra data you will need to answer that requirement.
So the benefits to the DBA are:
  • Gives insight about possible data that you might need to plan for. And it does this at the design stage, so you can get a much clearer picture early on.
  • Its easy of use. Easy to understand since is logical and very natural for humans to look at data in that way.
  • This enables you to save time managing the functions. Meaning, that instead of generating functions, naming them and maintaining them, you can just place them in the table and the engine will manage them. This also allows you to manage the business rules and know that there is only one place where you need to look for them.
  • Allow you to make dynamic changes to the table when data updates. Obvious, but it means that you as the DBA, don't need to write extra code to do these changes.
  • You only need to know a bit about SQL to create a function in this way. So it simplifies things for the DBA and therefore can allow for more complicated situations to be solved. If you know more about SQL, then you can do even more then you could do before, simply because your code will be more organized.
  • Allows you to embed SQL into the database and manage it there. So again, you know where you need to go to find, make changes to or maintain the functions.
  • This will removes a lot of frustration from the development process and speed up developing time for the entire application.

How does this benefit the developer?

Well, as I am trying to shift some of the workload from the developer to the database, where (I think) you can take advatange of pre-built data-manipulating functions, the developer will have to do a lot less.
So the benefits for the developer are:
  • Improves development of the whole application by allowing the developer to focus on less things and produce better results accordingly.
  • Can save a lot of time..
  • Removes the tedious SQL query placement in the code and then testing it, by placing it in the database.
  • Allows the developer to focus on the results from the database and not about the whole process of getting data from several different places in the database and then processing it.
  • Makes the developers life pretty easy by giving him/her pre-generated reports and hiding the business rules behind them. All the developer has to do is display the information.
  • Removes a lot of frustration from the development process.

How does this benefit the project sponser/boss?

If the application is completed faster and hopefull cheaper, then the boss has more time to focus on market needs and how to respond to them. This will make companies, big and small, more competitive and supply user demands better. Also, it will allow the boss to consider collecting types of information that might benefit him/her in the future for example marketing, user satisfaction, etc.
So the benefits for the boss are:
  • Develop applications faster and cheaper.
  • Allow the boss to change business rules when needed since the business rules are placed in the database and can be changed from one location.
  • The data is now structured in a more human way and can be easier to understand. So the boss can take a more active stance in participating in the development of the application.
  • By making things more simpler and easier and providing tools for this, we can raise the level of all applications.

How does this benefit the database?

The database would be in a situation in which it knows ahead of time, what data is required. It knows what the user is looking for and it can cache the results. So the database would become a bit more "intelligent" and would manage more things on its own.

So the benefits for the database are:

  • Increase performance by allowing the engine to pre-process the queries according to what the user wants and has control whether to do it when requested or after an event.

  • Can process the queries at off-peak computing periods

  • Can ensure data quality and data analysis.

  • Can have much more “intuitive” caching (since you know what the user will see before hand) and therefore better performance. Allows the database to predict the usage of the table and cache accordingly.

  • Changes the paradigm that the database is only for holding data. Now the database is a little more intelligent.

This time, since the subject is a bit complicated, I decided to make a picture to help explain the idea more clearly.

So as some of you may have noticed, I have put some thought into all of this. I really do believe that this maybe the way of the future for databases. But naturally, I will always think my ideas are great and thats why I need feedback from people to help stear me in the right direction. If you have any comments and feedback about this idea or think its good and might like to consider working on it, please leave a comment in this post.


  1. I'm sorry, I just don't see much benefit in your model compared to keeping the business logic inside triggers, views, stored procedures or application classes. I like your enthusiasm for your idea but I think much of the problem stems from your inexperience as a DB developer, because most people are already familiar with these concepts and how people generally have ways of dealing with them.

    Your concept of function-based fields is precisely what views are for. The only problem in MySQL is that you (presently) cannot create an index on a function, but this is a limitation other databases don't have. Regardless, your method requires denormalization of the data which is generally frowned upon unless required.

    On the other hand, as a *client* side application I think your idea has a lot of merit. If you could create a client which allowed creation of function-based fields in views in a user friendly manner, this could be a good way to get folks like your dad out of Excel and into database analysis.

  2. You are absolutely right, you can keep all this logic in triggers, views and stored procedures. In fact, for an experienced DBA, this is nothing new and I am sure that they can do all the things I am suggesting, apart from 1 thing and I will get to that later.

    The idea of something like this is to help the less experienced people by simplifying the process for them and perhaps making it more clear.

    The added benefit that I am suggesting is that the results of the functions are cached and that the results are generated "when the engine sees it fit to do so". Meaning, either by generating the result when some related data has been changed, generating the result when the CPU is idle or not too busy and if it doesn't have the result with the first two methods, then generate by request.

  3. Essentially, what you are trying to accomplish is a 'materialized view system'. You take a view, which can have as many functional components as you want and you either update the view when the data changes, or you asynchronously propagate changes.

    This works as long as the functions are deterministic, but that would be a requirement of your system as well.