Decision Tables and Your Database

Image via Wikipedia

So I have been talking to a lot of people about my idea for a business rules engine in the database and many of my friends have given really good feedback. Not all of it was good feedback, but it allowed me to re-evaluate and focus my idea on what exactly I want to achieve using this idea.


The Goal

My goal in general is to - cut the crap - out of software developing, shortening and simplifying the process, so that small businesses and people without a lot of means are able to create good applications or websites.

The way I would like to do this, is by allowing the database to use its built in functions and its excellent ways of manipulating data to speed up this process. I would like to direct this at applications or websites that mainly deal with data deliverance.
For example, websites that use content management systems.
For short, any systems that have some "small to medium" data manipulation needs and not bio-engineering proteins with complex mathematical algorithms.

Small businesses that deal with stock allocation, generating statistical reports and managing accounts will benefit greatly from this. Websites that run simple search functions on certain data or process orders, can also benefit from this. And as I mentioned before, to allow these types of people to create their systems without spending too much money - for development, maintenance or new features they need to add to respond to the marketplace.


Business Rule Engine

Now, before I start, I have to admit that I don't really know business rule engines above the research I have done on the internet. Basically, the idea is that all the "business rules" are kept in one place and the other parts of the application communicate with that engine to get things done. The convenience of keeping such an engine and separating it from the code, is that it makes managing the business rules and making changes because of improvements or market changes becomes a lot easier. This means that your application becomes flexible and robust.
You need to consider that, even though you may try hard, your application will have to be changed in some way through out the course of its life time. Making any system that will help you make changes easily, will benefit you and the users greatly.


Decision Trees

So decision trees are a simple idea and very useful one to use when developing software. A few places where you can read up on them are: here and here.

Rules for electricity billing are as below:

If the meter reading is "OK", calculate on consumption basis(i.e. meter reading)
If the meter reading appears "LOW", then check if the house is occupied
If the house is occupied, calculate on seasonal consumption basis otherwise calculate on consuption basis
If the meter is damaged, calculate based on maximum possible electricity usage

On the whole, it is something which is very easily applied to business rules for your application. In fact, it will help greatly if you do use it when planning your application.

Now, there are many websites that link decision trees to data mining and also artificial intelligence. Its very interesting reading and when I read it, it did make me feel like this sort of thing very much belongs in the database next to the actual data. Meaning that, you have your data and you try to make sense of it all or find some sort of pattern. So it would be most potent and beneficial to really put it in the database.


Decision Tables - Binary Tables

So in this web page there is something explaining decision tables, which is very simple conversion of decision trees to a table using Y and N. So for every condition you have a column in the table which will be occupied by Y or N.

BINARY-VALUED DECISION TABLE

Domestic Customer	Y	Y	N	N
Consumption<>	Y	N	Y	N
Minimum rate	Y	N	N	N
Special rate	N	Y	N	N
Double minimum rate	N	N	Y	N
Double special rate	N	N	N	Y

So decision tables are more simplified versions of decision trees and a lot less graphic, but as you can see, it becomes very simple to put decision tables in a database.


The Next Step

So as you can guess, I have been leading to put decision tables into the database along side the tables where the information is kept. This will allow the business rules to placed in the database.

So lets take this as an example:

Table	DecisionTable
FieldA	ConditionA
	Decision

We have 2 tables. 1 table has some information and the other is a decision table.
Now lets imagine that we have a function that is called FindCondition().
This function checks if the data in FieldA is over 300. If it is, it returns Y if it isnt, it returns N.
(of course, you can also do this with a simple if() ).

So now, lets try this:

SELECT Table.FieldA, DecisionTable.Decision FROM Table INNER JOIN DecisionTable ON FindCondition(Table.FieldA) =DecisionTable.ConditionA

So using the function, you join it to the DecisionTable and get the result. Afterwards, you need to do something with the result. So you can use the result in your application layer or you can do something more complicated in the database with a stored procedure. Its up to you.


Conclusion

1) I'd like to mention again, is that the database handles data manipulation very easily. So in this example, to decide if a number is above 300 is as simple as adding a If(fieldA>300,'Y','N').
2) For application developers, you would need to first get the data from the database - which can be time consuming to map the application to the database - and THEN you would do an 'if' to see if its above 300 or not. But since you are already in the database, you skip the whole mapping process altogether.
Now obviously, you will need to map to the database and get the data to your application anyway. But in this case, you can save yourself a lot of work by from looking at many different places to get the answers you need to your questions for your decisions.
3) You now are able to keep your business rules in the database within functions or complicated selects and within decision tables. You can make changes to them in the future and you have an overall view of everything as well. From a maintenance and adaptability point of view, you are arming yourself with tools that will make your product viable and robust for a very long time.


Thank you for reading my blog. Please post a comment about what you think.