Using Business Rules in MySQL

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


What is a Business Rule?

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




Decision Trees

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

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

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



Conclusion

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.