Storing Business Logic in Database

Question

We want to write some business logic rules that work on top of certain data to build reports. Not sure which is the best to store them in the database MySQL.

It can have a chain of the rules and then a statement for the result as shown above.

Solution 1

For building reports you can convert business logic in any programming language. And use database data for generating reports.

Against of business logic stored in database

I place a high value on the power of expression, and I don't find the SQL space to be all that expressive. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.

But it depends. If you have multiple applications interacting with one storage mechanism and you want to make sure it maintains its integrity and workflow, then you should offload all of the logic into the database server. Or, be prepared to manage concurrent development in multiple applications.

Source: Arguments for/against Business Logic in stored procedures

See also:

1. Business Logic in the Database
2. Business Logic In Stored Procedures
3. Storing conditional logic expressions/rules in a database

Solution 2

CREATE TABLE businessRule (
  id INT NOT NULL ,
  name VARCHAR(32) NOT NULL ,
  description VARCHAR(255) NULL ,
  statement VARCHAR(255) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

CREATE TABLE leftOperand (
  id INT NOT NULL ,
  value VARCHAR(255) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

CREATE TABLE ruleItem (
  id INT NOT NULL ,
  businessRuleId INT NOT NULL ,
  operator ENUM('if','and','or','not') NOT NULL ,
  loperand INT NOT NULL ,
  comparator ENUM('<','=','>') NOT NULL ,
  roperand VARCHAR(255) NOT NULL ,
  roperand_ispercentage TINYINT(1)  NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX businessRule_FK (businessRuleId ASC) ,
  INDEX leftOperand_FK (loperand ASC) ,
  CONSTRAINT businessRule_FK
    FOREIGN KEY (businessRuleId )
    REFERENCES mydb.businessRule (id )
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT leftOperand_FK
    FOREIGN KEY (loperand )
    REFERENCES mydb.leftOperand (id )
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

Solution 3

An argument against "soft coding" business logic like this: http://thedailywtf.com/Articles/Soft_Coding.aspx

"The reason we find ourselves Soft Coding is because we fear change. Not the normal Fear of Change, but the fear that the code we write will have to be changed as a result of a business rule change. It’s a pretty silly fear to have. The whole point of software (hence, the “soft”) is that it can change that it will change. The only way to insulate your software from business rule changes is to build a completely generic program that’s devoid of all business rules yet can implement any rule. Oh, and they’ve already built that tool. It’s called C++. And Java. And C#. And Basic. And, dare I say, COBOL."

Solution 4

All I can give you is the way you should solve this problem, and not the answer itself.

The general way to design a database to store complex data like this is to design the way you would keep them in memory as objects and then try and design the database accordingly. You will be evaluating the rules in a programming language after all. The procedure will be as follow: First the class diagram

Then it's time to convert it into an ERD:

Once you have a database structure to store/reload your object to/from, you can simply create your classes such that each object is responsible to load/store itself.

[UPDATE]

For instance if you want to store the statement a + b * -c into database, it could be translated as the following inserts:

-- c
INSERT INTO statement (statement_id) VALUES (1);
INSERT INTO operand (statement_id, type) VALUES (1, 'double');
-- - (minus)
INSERT INTO statement (statement_id) VALUES (2);
INSERT INTO operator (statement_id, type) VALUES (2, 'minus');
-- -c
INSERT INTO binary (operator_statement_id, operand_statement_id) VALUES (2, 1);
-- b
INSERT INTO statement (statement_id) VALUES (3);
INSERT INTO operand (statement_id, type) VALUES (3, 'double');
-- * (multiply)
INSERT INTO statement (statement_id) VALUES (4);
INSERT INTO operator (statement_id, type) VALUES (4, 'multiply');
-- b * -c
INSERT INTO unary (operator_statement_id, operand_statement_id1, operand_statement_id2) VALUES (4, 3, 2);
-- a
INSERT INTO statement (statement_id) VALUES (5);
INSERT INTO operand (statement_id, type) VALUES (5, 'double');
-- + (plus)
INSERT INTO statement (statement_id) VALUES (6);
INSERT INTO operator (statement_id, type) VALUES (6, 'sum');
-- a + b * -c
INSERT INTO unary (operator_statement_id, operand_statement_id1, operand_statement_id2) VALUES (6, 5, 4);

Solution 5

I think what needs to be done first is question whether or not you should be putting the rules in a database to begin with.

Databases are a heavy handed solution, and are often simply not needed.

Having dealt with rules engines in various forms, including database driven, I can tell you it can get really frustrating and unproductive, really quickly. One of the big mistakes I've seen happen is attempting to write your own ad-hoc rules language and using that to drive conditional logic via the database. At the very least, use a language that's already proven (Python, javscript, etc) and embed that in there.

Even better- if the rules are sufficiently complex, I personally prefer to employ Excel spreadsheets. We use this for automation (to handle variable logic based on effective date, etc), and we also compile rather complex insurance rating logic to Perl scripts interfaced via a web service, using this product: http://decisionresearch.com/products/rating.html.

Contrast storing the logic in a database versus, say, an Excel spreadsheet:

1. Logic in the database is harder to test and develop for compared to Excel, because Excel provides instant feedback.
2. A database is less (much less) expressive compared to Excel.
3. You can color code and add all sorts of other visual cues to Excel to make error conditions, etc, really stand out.

Now of course, as you can imagine, a web service driven Excel rules engine isn't going to fit every situation. And it's not the only possible solution here.

What I'm getting at though is that make sure you're making the right trade offs in terms of usability/expressiveness/testability/performance. Where I work, being right and being productive is more important than being fast in execution, so we go with the Excel/web service.

And to expand on slavik262's comment, what you really want to achieve with rules engines, ultimately, is abstraction and generalization, to minimize moving parts and increase reliability, testability, and understandability. A database rules engine, in my experience, is sub-optimal in comparison often to even simply just making, say, Java based rules. As long as they're sandboxed and organized properly, and hide behind a generalized and consistent interface, then they work just fine.

At my company, it depends on the scale of the rules and how often they change as to what we go with. Rating insurance- Excel, no question about it. Some state specific logic? Interfaced Java rule files suffice just fine.

View more solutions

Comments

[brianpeiris]

"If you have multiple applications interacting with one storage mechanism and you want to make sure it maintains its integrity and workflow, then you should offload all of the logic into the database server". -- Surely a more sensible option would be to build shared, high-level, services on top of the storage in this situation.

[Paktas]

Mehran, very interesting approach. Could you give a sample logics and data insert for this DB structure? Thx.

[sachingupta]

could you please help me what would be the select query if I want to get a+b*-c

[sachingupta]

please share an example on how to insert data into these tables

[Mehran]

@saching I think there's a misunderstanding here. In this solution, the statements (like a+b*-c) are not evaluated in DB layer and so there's no SELECT query for them. What you need to do is to extract the all the records related to your statement (using statement_id) and reconstruct the statement object in your application layer (in whatever programming language you like) and then evaluate the statement.

[sachingupta]

thanks for the explanation @Mehran. I am not able make that select query for even to get the structure of the equation. Can you please help me with that ?

[Mehran]

It could be as simple as SELECT * FROM <table name> WHERE statement_id = ? as I said in my previous comment.