Storing Conditions in the Database

Caching Business Logic in the Database

I had this relatively simple idea of storing conditions in the database. The idea is to store the condition of the if-then-else from your code in the database. While this will add (slightly) more space to the database, I hope that it will do away with processing the data over and over again as well as help drastically speed up searches and any analytical processing you would like to do in the future. I will try to explain with an example..


Story:
You run an electronics store and you have sales people working in that store. You would like to give a bonus incentive and you decide that the rules for the bonus would be the following:
1) Sales people that sell more then average amount of money each for at least 15 days out of a month will receive a bonus. After a bit of calculation you see that the average is 300$.
2) If anyone received a complaint from a customer, that's an automatic disqualification of the bonus.
3)If the sales person came late more then 5 days in that month, they will be disqualified from the bonus.


Now we have our requirements, lets try to do some pseudo code:

1) Go to the `Employee` table and check for all the sales people that are currently working.
2) Go to the `Sales` table and check for each sales person, if they got over 300$ that day.
3) Count how many days they sold over 300$ that month. Check if its over 15 times.
4) Check for each sales person in the `Complaints` table, if they got a complaint. If they did, they cant get the bonus.
5) Check for each sales person in the `Timesheet` table, if they were late that month. Count if its over 5 times. If it is, then they cant get the bonus.

Final check after all the information is gathered:
Check for each sales person if they sold more then 300$, more then 15 times that month and if they had no complaints and if they didn't come late more then 5 times, then give them the bonus


How long would it take?
Now lets take a moment to theorise the CPU and hard disk processing required for all that calculation. You would need to check the table where the sales people are located. Then, according to which programming background you come from, you would either create an array or list of objects, then run a query for each sales person x3, because its 3 different tables. Or you could do a complex join of all tables using SQL (and probably generate the results faster). Either way, you are going to work the hard disk a few times, use memory and use the CPU to do some light math.
(This why I like to test my SQL with MySQL Query Browser. I can see exactly how fast it took to generate the result)


Suggestion
This is where I suggest something not too controversial, that you might want to store the conditions for the final decision of the bonus in another place.
For this example, I would suggest creating an extra table that would have:

The date of each day - date
The sales person ID - integer
Did s/he more then average that day - boolean (Edited from - sold more then 300$)
Did s/he come late that day - boolean
Did s/he get a complaint that day - boolean

The booleans store the conditions in a compacted way. All you need is true or false for the answer to the conditions. Then whenever you want to see the results of who will get bonus, you can query that table. This could save you that processing and waiting time we talked about earlier.

Depending on how fast you would like your response to be when generating that list, you could use this method which does take up space but saves on processing time.


Notice also, that I do not specify how you should process and generate the new table. You can write the process for finding and storing the conditions in an application or with stored procedures or PL/SQL.
Since there is a trend in MySQL for "less-is-much-faster" with Drizzle and that many organisations see putting logic in the database layer as evil, I am leaving the decision in the hands of whoever uses it.
I believe that storing the conditions in the database should compliment the database layer and the application layer, but I need to test my theory some more.


Final Note
Please tell me what you think about this approach. I am sure its not new, but I would like to get feedback about it.

I plan on continuing with it and already plan to do a more complicated example soon.