Adding a Business Rule to Your Database

I have been talking about many theories in the past and in my last post, I decided to start doing something instead of just talking about it.
So here we go..

Adding a Business Rule

We need to start adding some logic-rules to the database. There are two ways you can do this:

1. To create a View with IFs and other functions - this will process the data every time to give you the results

2. To save the result of the logic to the database table using a trigger - this might save some of the repeating processes but will take up more space and might slow things down during new inserts.

Both of these, you can do in your application right now. You can make a complicated SELECT statement with several joins or you could save the results of the business rule in the database table, after your application processed it. So what I am suggesting is not that different from the way things have always been done. There are a few advantages, but I'll get to that later.


The Hotel Allocation Table

(Again, I will use an example from my previous employer)

We have a system that allows users to book hotel rooms. The table example here is to find out if the room is available or not.
For each row in the table we have the date for that day, the hotel and the type of room. Every hotel has a certain number of different types of rooms, such as a suite or room with a balcany. We dont tell the hotels how to book each of their specific rooms, but we can tell them to book one of "these types" of rooms for a customer.
We have 3 indications if these rooms are available.
1) Sold_Out = 1
2) Status = 1 - meaning the hotel said you cant use it.
3) Rooms_Available = 0 - means that all these types of rooms have been booked.


Using Views

The MySQL tools application is very handy when making Views. You can put in a regular SELECT statement and it will clean it up for you and even optimize it somewhat.
Inside the View, you can add your rule, which in this case is an IF to determine if the room is available or not.

Here, you can see the extra IF line that gives a Y or N result.

And here you can see the result of this View.
Now, your application can simply ask for WHERE Available = 'Y'. Your rule is not in the database. If one day you decided to add "Closed_for_Cleaning" column, you can make the changes to your IF and your application will not notice the change.


Triggers

So triggers in MySQL are new to me and I had to learn them recently. Thats the plus side of writing a blog, it forces you to learn new things.

The idea here is to use the same IF statement I used before and save the result to a column in the table. The only way that I found to play around with triggers is using Toad for MySQL (which is free). You can, of course, code it using the MySQL command line, but this was more comfortable for me.




Here I added the trigger. Please notice the Before with the Insert, because from what I understand you have to do it that way.
Triggers are basically additions to your INSERT statement. Whenever you insert a new row, the trigger will start working and add more processes to the insert.
This can benefit you if you have very few INSERTs compared to SELECTs, since in this example, you don't need to calculate the rule for every SELECT. You just look up the result.
I also need to add the exact same code to a new trigger for UPDATE, in case someone makes changes to the data.

The result is the same as when using the View.

Advantages

What are the advantages of using Views and Triggers instead of writing code in your application?
There are on going arguments about if you should put the business logic in the database or the application layer, but these are my thoughts:

1. Sometimes its more comfortable to make a complicated SELECT or View instead of coding it in the application layer

2. (I believe that) When you use Views, the database take somethings into consideration, since it knows what data you will be looking for when you call on the View. There are also ways to cache. So the database might respond more favourably to a View than a embedded SQL in your application.

3. Sometimes its a lot more convenient to let the database take care of its data structures rather than search for problems in your application code. If there is some seperation between the database and the application code, it might make it easier to find problems or make changes in the future.

Ideas for the Future

I'd like to try out the Events feature in MySQL although I am not very familiar with it. It seems like a good idea if I can use it in a sort of way to make the MySQL database work in a concurrent way. Maybe, instead of using Triggers to add processes when you INSERT, you can just make the calculation take place a few seconds later when things are not so busy.


Conclusion

1. Use Views to move the embedded SQL from your application to the database. See if it improves speed or makes things easier to handle
2. Use Triggers to save you some of those "dont forget to also update the other table when you do that" moments. Save yourself the added burden of doing those consideration in your application code.
3. Try to move a few business rules to the database. See if it helps you.

Thank you for taking the time to read my blog.