While everyone tries to improve speed and performance in MySQL, other databases have realized that adding features that cut down on development time and improving time-to-market is what some people are looking for. MySQL is well known for great performance and it might be time to discover other parts of it that will speed up your over-all development process.
This article tries to explain how you can save time and effort on the development process by moving some of that development to the database. It does this by recommending you apply business rules to the database.
Additional Notes:
This example was taken from an actual database. Some of the table designs from that database were not ideally optimized and normalized. Please refer to the theory of the example and not the exact technical detail.
"Business rules represent policies, procedures and constraints regarding how an enterprise conducts its business." www.isr.uci.edu/~alspaugh/glossary.html
“Business rules describe the operations, definitions and constraints that apply to an organization in achieving its goals” http://en.wikipedia.org/wiki/Business_rules
As you can see, business rules help you “achieve your goals”. So what are your goals?
Many people do not take a few minutes to ask some simple but penetrating questions.
For example, your goal is to create an amazing online hotel reservation system.
You will do this by registering hotels and hotel rooms on your system and then letting the customer book them through a web portal.
What kind of information do I need to collect?
The hotel details
The hotel room details
The customer details
The allocation details
Why do I need to collect information about allocation details?
To know which rooms the customer allocated and on which dates.
To know which rooms the hotel needs to reserve for the customer.
To know which rooms have already been booked and to not book them to someone else.
What kind of information do I need to collect for the allocations?
The date – which day has the room been booked
The hotel info – which hotel was used so that you can pay them later
The room info
And is the room available to be booked?
So how do you know if the room is available to be booked?
You might need other types of information that will tell you if the room is available.
For example:
Has the room already been booked on the same days that you want it? Is it sold out? (sold out on your system or the hotel notified you that its sold out)
Is it currently available to be booked or is it being redecorated, fumigated for cockroaches or under repair? – What’s the status of the room?
How many rooms are available for this room type? I mentioned the room type (like suite or with balcony) because we don’t allocate specific rooms directly in the hotel; we just get a certain number of rooms reserved from the hotel (at least that’s what I understood from someone who does have an online reservation system).
The requirements for a room to be available are a sort of business rule. Once you have the requirements, all that interests you is the result of the business rule (the result for the question, is it available?). Meaning, after you know what you need to determine if the room is available, you don’t really “care” about the extra bits of information. All you care about is the result that the room is available.
This is what you normally do in your head and if you explain it to the database, it can also do that for you. You can ask the database to give you all the available rooms between 2 dates and it will give you the results because it already knows what other requirements to look for to determine if a room is available.
“A decision tree (or tree diagram) is a decision support tool that uses a graph or model of decisions and their possible consequences… A decision tree is used to identify the strategy most likely to reach a goal.” http://en.wikipedia.org/wiki/Decision_tree
In order for me to draw business rules, I like to use decision trees. They are simple and very helpful to see problems as well as explain them to other people. They are also a sort of visual method for brain storming problems (example http://www.mindtools.com/dectree.html). There is also another reason to use decision trees and I will get to that later on.
This is a decision tree for our business rule. The diagram shows all the conditions for when a room is available and when it is not.
Off to the Code
Many people don’t know, but SQL is in fact a programming language. It is a “declarative query and data manipulation language… that has procedural constructs, control-of-flow statements, user-defined data types, and various other language extensions” (http://en.wikipedia.org/wiki/SQL). We are interested in the “control-of-flow” part of SQL to help us place conditions so when a room is available.
Here is our allocations table.
CREATE TABLE `allocations` (
`DayID` DATETIME NOT NULL,
`HotelID` INTEGER UNSIGNED NOT NULL,
`RoomID` INTEGER UNSIGNED NOT NULL,
`SoldOut` BOOLEAN NOT NULL,
`Status` BOOLEAN NOT NULL,
`RoomsAvailable` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`DayID`, `HotelID`, `RoomID`)
);
To enable the business rule I will use the following piece of code:
SELECT DayID, HotelID, RoomType,
if((SoldOut=false)and(`Status`=true)and(RoomsAvailable>0),True,False) as Available FROM allocations
We have simplified things a bit. We no longer need to look at the other 3 columns that we hid. The data in those columns is still being collected, but for the purpose of finding out which rooms are available, they can remain hidden.
After creating a View with the above code, we can run a search like this:
SELECT * FROM allocations_view WHERE Available = True
And that’s really all we are concerned about.
What did we just do?
We placed a business rule in the database, next to the data. We took out the need for this to be programmed outside the database using another programming language (PHP, Java, Perl, ASP, etc… ).
Is this a good idea?
I think it is for 2 reasons:
If (and only if) developing this in the database saves you time and simplifies things, then you’ve saved the time and money asking a developer to do the same thing in the application layer.
Instead of the application layer retrieve all the bits of data to determine what is the result of the business rule (meaning more data has to be transferred from the database to the application layer), only the data of the result is transferred to the application layer. This can speed things up by reducing network traffic.
Decision Tables
“Decision tables are a precise yet compact way to model complicated logic. Decision tables, like if-then-else and switch-case statements, associate conditions with actions to perform. But, unlike the control structures found in traditional programming languages, decision tables can associate many independent conditions with several actions in an elegant way.” http://en.wikipedia.org/wiki/Decision_tables
Once you have a decision tree, you can easily make a decision table. Since a decision table is a… table, you can place it in the database.
This may seem more complicated then the previous approach, but bare with me.
If you compare this decision table to the decision tree diagram from before, it covers all the possible outcomes (2 to the power of 3 equals 8 outcomes). In this case, there is only 1 outcome where the room will be available
You now need to join the original table and this decision table, using this code:
SELECT allocations.DayID, allocations.HotelID, allocations.RoomType,
allocations_decision.Available
FROM allocations Left Join allocations_decision on (allocations.SoldOut=allocations_decision.SoldOut)
and (allocations.`Status`=allocations_decision.`Status`)
and (if((allocations.RoomsAvailable>0),True,False)=allocations_decision.AboveZeroRooms)
You have the same results as before, but the approach you used was somewhat more difficult to do.
What did we just do?
We used a decision table to display the possible outcomes of finding out if a room is available and then joined it to the original table.
Is this a good idea?
That is for you to decide. The main advantage is the ability to make changes in the future. You can actually configure the business rules of the system by making changes to the decision tables. Non-developers in particular, can make these changes.
If something happens during the life time of the system, for example changes in the market, then you can react to these changes by configuring your system’s business rules.
You can also use decision tables when using too many “if-then-else” statements that confuse you and you prefer to have a more simplified look at all the conditions.
In this article I have tried to show that you can add business rules to the database. I try to show that using the SQL language helps and simplifies data management and data manipulation. I have also tried to show how to make your database to be configurable so that it can change its business rules when they need to be changed.
Above all, I tried to show that database developing is not so difficult and encourage people to put more emphasis on the database as part of the overall software development process.
Thank you for reading my article.
Almost always a good rule of thumb to move a considerable amount of your application business rules into the database.
ReplyDelete- Shelon Padmore
Nice article, thanks!
ReplyDeleteI have a few remarks though.
In many cases, people talk about moving the business logic "out of the application". Usually they add in one go that the business rules must therefore be "moved into the database".
The big reason for moving that logic out of the application is that many applications will need to work according to the same business logic,and will therefore need to re-use it.
Now that is all fine and dandy, but to me this still does not automatically mean the business logic, and especially processing business logic, should therefore be moved "into the database".
I mean, I would argue that that could make sense, but there is absolutely no reason why it would be impossible to re-use business logic if it would be stored and processed outside of the database.
This is nothing new of course - creating a middle tier between the user interface (application) and the storage service (database) is the important thing - the *physical* location of that middle tier is still pretty irrelevant for the functional implications.
So in other words - why put it in the database? Why not put it in a physically separate component? The only thing that is importance is that the applications code against the middle tier - be it database stored procedures, a java application server, or a custom php extension.
In fact, there are a number of arguments to keep the business rules separate from both the applications as well as the database:
1) portability. The choice for putting logic in the application vs logic in the db is a tradeoff: the first choice favours portability across databases, the second favours portability of application code. If you don't want to trade-off like that, you can solve it by putting the logic in a separate layer, allowing for portability of both (but not of the business logic implementation, although it will have the side effect of favouring clear interfaces that may make it easier to port the business logic software in the future)
2) scalability. Each major distinct service or component should ideally be assigned its own resources. Having shared resources for business rules processing and the data service may mean that one can become a bottle neck for the other. That is not a position you want to be in - better to be able to add resources to each separate layer according to actual resource consumption.
The second thing I was wondering about are the decision tables. In the example, the decision table does solve some things, but there is still a lot of logic hidden. For example, from whence does it follow that we have to compare "SoldOut" using the equals operator? And similarly, how do we know that for "RoomsAvailable" we have to test for "> 0"?
I mean, the logic is still pretty much buried in the code. You pointed out yourself that the example is maybe not perfect, and that makes this particular example perhaps not very convincing.
Do not take me wrong but it seems to me that a business rules engine should be able to accommodate exactly things like
X > THRESHOLD
and it would probably also need to be able to combine rules.
In fact the more I think about it, if the rules engine is not as flexible as the traditional conditional constructs such as IF and CASE, then it is probably not worth it. Esp. if we stick with a "TRUE/FALSE" paradigm such as sketched here, the app developers are still force to code all kinds of logic into their statements in order to be able to test against the boolean states in the decision table, and it seems to me that that will usually not outweigh the advantages.
It's important to know WHICH logic belongs in the database vs. which doesn't. I think you have to consider a few issues:
ReplyDelete- Does my application perform that logic as efficiently as my code
- Where do I have the most flexbility to UPDATE that logic?
A good example with MySQL is ORDER BY and optimization. Many times there are cases where MySQL will not optimize the order by and will resort to a filesort. In that case, maybe it's better to do that using application logic.
The other issue is that of flexibility. Where can you make changes the easiest place possible. I would argue that keeping that logic in the application is much better.
I'm not a developer and never have been but I've seen problems both ways from an architecture and engineering side of the house.
Whichever place you decide to do it, make sure that you do one or the other. Managing business rules in two places is a nightmare than can quickly grow out of hand.
@roland
ReplyDeleteThere has to be a business logic layer somewhere. if its in the application or database it still has to exist.
It depends whats more comfortable for you. When you are developing your solution, if your application needs a lot of data manipulation and processing that can be solved by using the SQL language MORE comfortably then programing it in java/php/any other language, then the database might be more useful for you to develop in.
And again, another issue I'd like to raise is speed. The database has its own configurations and caching and if you rely on it to process your data, it might do it faster then if you develop it by yourself.
Notice that I am only talking about data processing and data manipulation. These are areas I feel that the database might be more convenient to use with.
Regarding decision tables
I used the decision tables for 2 reasons:
1)It was a natural evolution from decision trees and therefore you can use diagrams of the trees to make things clearer.
2)Keeping decision tables in the database - although they might take some time to explain - lets you see and change the business logic. There is an element of configuration that I tried to find with these decision tables. In the future, if you want to make changes, you can do so easier compared to making changes in many lines of mixed up code.
However, I wont defend the decision tables in this case, because they are still very much theory at this stage.
Thanks to the author for giving me to two new ideas.
ReplyDeleteI am programming all my personal and client projects in PHP/MySQL. Business logic was always done in PHP. I never got the idea to do it in MySQL. I will keep it in mind and maybe do it in MySQL statements directly or using decision tables in the database in new projects or refactoring old ones. Really easy to change.
The speed is important as mentioned above. I am keen to do benchmarking all three solutions and to find out the high-performance solution.
Thanks for the article
ReplyDeleteJust to let you know the images don't seem to appear anymore
kr