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:
|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.