How to Use Business Rules in a MySQL Database

Using MySQL Functions to Create Two-Tier Business Savvy Applications

Jul 10, 2009

How to Use Business Rules in a MySQL Database, Mark Alexander Bain

Business rules are incredibly important to every organisation. They can be made application independent by placing them in a MySQL database.

When creating an application that uses business rules there is a very important point that a programmer should remember: the business rules should not be built into the application. Business rules should be made available to the application, but not hard coded into it. The reasons for this are:

business rules evolve
situations change (for example new regulations)
the business rules may be used by more that one application

The last thing that the programmer wants is to create new application releases every time that a business rule is added or updated. And that's where MySQL comes in.

MySQL and Business Rules

The aim of the business rule programmer is to create a two-tier application consisting of:

the business rule layer
the application layer

A MySQL database is an obvious choice for the business rule layer because it can be accessed from most programming languages and the programmer can:

store the business rule information in tables and/or
use stored stored functions to run the business rules (anyone unsure of how to create a MySQL stored function should read MySQL Stored Procedures and Functions)

By using a MySQL database the programmer can create a set of business rules that can be used by any application that needs to be developed, whether that be a desktop application (perhaps using C#, C++ or VB) or an Internet application (perhaps using .NET or PHP).

Creating a Simple MySQL Stored Function Business Rule

The business rules themselves can be built into a MySQL function. Obviously it may write to any database table as require, but at its simplest level it can just be a series of if..then..else statements. In this example the function returns the appropriate action depending on the input from the user:

delimiter //

drop function if exists business_rule;

create function business_rule (status varchar(50)) returns varchar(50)

deterministic

begin

declare new_status varchar(50);

if status = "new" then

set new_status = "case opened - send to engineer";

elseif status = "fixed" then

set new_status = "problem solved - inform customer";

elseif status = "nofix" then

set new_status = "problem unresolved - sent to engineer";

elseif status = "raise" then

set new_status = "problem escalated - inform manager";

elseif status = "close" then

set new_status = "case closed";

else set status = "undefined";

end if;

return new_status;

end

delimiter ;

This function can now be used as required by the programmer. For example it can be run from the command line (as shown in figure 1 at the bottom of this article):

echo "select business_rule('new')" | mysql -uuser -ppassword business_rules_db

Or it can be used in languages such as C# (and for more on that then read How to Connect to a MySQL Database with C#). But however the business rules are to be use the key things to remember are:

those business rules are now available to any programming language or application that can interface with a MySQL database
the business rules can be updated and added to without having to change the client application

And so, at the end of this process, the programmer will have a powerful and adaptable set of business rules that are independent of the application that will use them.

The copyright of the article How to Use Business Rules in a MySQL Database in Database Programming is owned by Mark Alexander Bain. Permission to republish How to Use Business Rules in a MySQL Database in print or online must be granted by the author in writing.

How to Use Business Rules in a MySQL Database

Figure 1: Using the Business Rules

Print Article

View All Articles

How to subscribe to feeds