I read this article recently about how BI users just really love and are used to their Excel sheets for almost anything they do. My first reaction was "duh". My second reaction was that even I used Excel recently to help me data cleanse some contact information.
Its easy to use, the functions are clear, I can develop my own functions in VBA, I can use colors to see problems with values, etc...
I mean I can bet you that "even" if you use MySQL and you want to play around with the data to see if you can find anything, you would use Excel.

But as the article says, there are some drawbacks in terms of time spent fixing errors and if you use Excel sheets that connect to other Excel sheet and then you go and insert a column or something.


Anyway, the article is worth the read and maybe if we could take some of the advantages of Excel and add it to MySQL, maybe we can get some of the marketshare of Excel to move over a bit to MySQL

I recently reread the book called “Execution – The Art of Getting Things Done” and although its somewhat old, it has surprisingly good and practical advise about putting your focus more on getting things done and less on thinking about strategies.

Or where I read from somewhere recently “The goal is to work, not think about working” which was in reference to keeping a planning meeting fixed to a certain number of hours.

Of course, you do need strategy, but if you don’t actually make sure that what you planned get done, or alternately, notice that your strategy may not be working then it just won’t work.

Hey! Wait a minute! Don’t you do Theory all the Time

Yes, I am very guilty of it myself about just thinking of things and not doing them. I even wrote about it at the top of one of my posts.

So recently, I was looking at all my talk about business rules on my blog and I was thinking “if I take a regular database and I wanted to add some business logic/rules to it, how many rules would I need to add?”.

I took a database that was connected to some web site forms that basically collect information about users for campaigns and news letters. I could really only found one rule to add and that’s because I knew how the data will be used later.

Now I do agree that this is not really a web application that requires a lot of logic. This system is pretty much taking data from point A to point B.

However, I was thinking about past projects and even when it was relatively complicated web application and even then, I could only think, at the top of my head, 3 main rules that might have been used in the database instead of across 10s of PHP files. Everything else in those PHP file was again, taking data from point A and fashioning it in a nice viewable way to point B, which is on your screen.

My Question

So my question is, why is adding business rules to your database such a controversial issue?

Maybe it’s something to do with adopted practices that tell you to not put any logic in the database. Maybe people are afraid to commit to one database and might change it in the future. So keeping the logic separate is more convenient.

I myself have come to realize that a large part of the reasons I thought this was a good idea was when I was working on a MySQL/PHP system and wanted to save time coding PHP (or just couldn’t even found where the lines of code were located in the mess that was there) by making long and complicated SQL statements or by creating Views. This seemed easier to me and I found it more comfortable to control my work, monitor the data and (which was the best part for me) show me the speed in which the queries were generated - so that I can speed up the website.

Conclusion

So I think that if you really take a look at it, (and you are not making an Expert System or Decision Support System) you only really need a few (that’s just my crazy assumption) business rules which can relatively easily be implemented in the database. Why not just try it and see if it works for you.

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.