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.
Storing Business Logic in Database
Asked Answered
How large is the underlying data being used by the rules? How complex is it? There are different approach for 1000 rows in one table with 10 columns versus billions of transactions stored in a complex data structure. –
Spleenful
@GordonLinoff its analytics data of last 60-90 days with around 12 columns. –
Terrieterrier
How complex are the business rules you want to include? Are they basically simple filter conditions on the columns along with some new calculated values? Or, are they more complex? –
Spleenful
@GordonLinoff i.imgur.com/KOFPY.jpg the shaded will repeat a few time may be 3-5times at the max –
Terrieterrier
My apologies, I was having browser problems. Is the data for the rules stored in a single table? For instance, how is change in unique visitors calculated? Is there a column with this information? Are you getting the difference from two different rows? –
Spleenful
each row will have data like visitors, unique visiters, mobile visitors etc with a date. So yeah the increase in the users is calculated between two rows identified by date and the unique profile_id –
Terrieterrier
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:
"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. –
Winona
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;
please share an example on how to insert data into these tables –
Katharinakatharine
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."
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);
Mehran, very interesting approach. Could you give a sample logics and data insert for this DB structure? Thx. –
Bosket
could you please help me what would be the select query if I want to get a+b*-c –
Katharinakatharine
@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. –
Forcible 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 ? –
Katharinakatharine
It could be as simple as
SELECT * FROM <table name> WHERE statement_id = ? as I said in my previous comment. –
Forcible 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:
- Logic in the database is harder to test and develop for compared to Excel, because Excel provides instant feedback.
- A database is less (much less) expressive compared to Excel.
- 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.
If you don't need to perform searches based on the components of rules then you can store the rule in two fields in the database. The condition under which the statement gets executed in one and the statement that is executed in another.
id, name, description, condition, statement
Your rules can be stored using JSON or some similar format.
I'll need to define some terminology that I'll be using. There are atomic terms, system values compared to values inputted by the user that evaluate to true/false, and complex terms, terms combined using logical operators.
In an atomic term, var denotes a value that the system will provide (such as the number of visitors or number of unique visitors). Comparisons determine how the var is to be evaluated against the value. The value is a number or string that the user produces. When a var and value are both numbers, comparisons can be "<", "<=", "=", ">=", or ">". When a var and value are both strings, comparisons can be "equals", "begins with", "ends with", or "contains". Atomic terms can be stored as follows:
{ var: varName, comp: comparison, value: numberOrString }
You can store the complex terms consisting of conjunctions, disjunctions, and negations (and/or/not) using the following formats.
// Conjunction
{ op: "and", terms: [ term, ..., term ] }
// Disjunction
{ op: "or", terms: [ term, ..., term ] }
// Negation
{ op: "not", term: term }
You can then build statements that evaluate to true/false using these methods. An example is as follows:
{ op: "and", terms: [
{op "or", terms: [
{ field: "numVisitors", comp: ">", value: 1000 },
{ field: "numUniqueVisitors", comp: ">=" 100 }
]},
{ op: "not", term: {
{ field: "numVisitors", comp: "<", value: 500 }
}}
]}
The example above equates to true when the number of visitors is greater than 1000 or the number of unique visitors is greater than or equal to 100, and the number of visitors is not less than 500.
You can then execute what you refer to as a "statement" when the rule evaluates to true.
So if I understand correctly you are looking to use the front end to allow people to dynamically create logic that will be applied to queries (dynamically built where clauses at runtime based on which rules are being used)?
If that is the case, you would need to be fairly specific about what conditions they can select in their rules (change in what value (column) so they can only have conditional rules against columns that exist in the dataset you are reporting from).
If I am understanding your question correctly, I would start by mapping out which tables/columns you want them to be able to select conditions against. This will be your controls for the webpage to design the rules.
However if you are just asking how to store the rules once they are chosen in the database, I would suggest storing it in a single table that contains:
ID | RuleSetName | Table | Column | Comparison | Value | Percentage | Notes | CreatedDate | Created By
1 | 'VisitorAnalytics' | Visitors | SUM(Views) | > | null | 10 | n/a | 1/1/2012 | JohnDoe
Then once these records are created, you will use them by injecting the tables into the from clause, columns into the where clause for your dynamic sql.
I know this may sound confusing, but what you are asking is a fairly complex solution. But ultimately you just want to store the rules together in one place where you can loop through to dynamically build then execute a SQL to generate your report. Hopefully this points you in the right direction.
One easy way to do it is to use a OODBMS. There, methods are encapsulated with slots into objects, and they can even be executed in the database (like triggers).
Now if you insist on a SQL database, what you can do is to use a dynamic programming language, and have a table to store code, perhaps associated to other tables or rows.
A few years ago I saw a tender for the Tax system of the Algerian Government, in which they planed to store the business rules (tax rules), as Visual Basic code in the RDBMS.
You could choose any language for which you can easily embed the interpreter in your application (Common Lisp http://ecls.sourceforge.net ; or http://common-lisp.net/project/armedbear/ if you write your application in Java), Lua, Javascript, Scheme, etc.
It'd tend to favor Common Lisp or Scheme, since with those languages you can easily write a DSL for the business rules.
The example given could be written as a symbolic expression such as:
(rule :name "RuleName"
:description "Some description"
:body (if (and (< (change-in total-visitor) (percent 10))
(> (change-in unique-visitors) (percent 2)))
(do-something)))
in lisp such a symbolic expression can be printed readably with the PRINT or PRINT-TO-STRING operators, so that you can insert this expression into a SQL database:
insert into rules (id,expression) values (42,"(rule :name \"RuleName\"
:description \"Some description\"
:body (if (and (< (change-in total-visitor) (percent 10))
(> (change-in unique-visitors) (percent 2)))
(do-something)))");
And you can get it back from SQL, read it back as a symbolic expression with the lisp READ or READ-FROM-STRING operators, and then, with the right DSL, you can evaluate it with the lisp EVAL operator:
;; with the right DSL written:
(eval (read-from-string (sql-select (expression) :where (= id 42))))
I am guessing that the purpose of the rules is to name calculated fields from an existing database table (or tables). Otherwise, for mere reporting purposes, you could dump the data into Excel and let users use Excel functions and pivot tables for their purposes.
The key question is how are you going to transform the rules into action. If the purpose is only to store business rules, so you can create a report of business rules, then a simple data structure in SQL is sufficient.
However, if you want to turn the rules into code, you need to consider where the code will run. When the data is stored in SQL, you have several options:
- Create SQL code that will extract the results of the "business rule".
- Create a user defined function that will parse and execute the business rule.
- Extract the data to another environment, such as C++ or C#, and run the code there.
I have a bias toward the first of these. The primary reason is that it limits the tools to one: SQL.
I am not sure what your rules are doing; the key is what the "statement" component does. Let me assume that this is a constant or an expression on data that can be calculated. In that case, your rules start to look a lot like a case statement. One caveat is that the statement may require looking at more than one row in your data table (to handle changes over time).
My recommendation is to store these rules in the database. This storage would allow you to construct a query from a sequence of business rules using SQL coding. Mysql allows dynamic SQL (nowadays). Without knowing a little more about the underlying table and the rules, it is hard to give more information.
I can say that I designed a much more complicated system used for scenario analysis. The scenarios themselves were stored in spreadsheets, in a series of tables, constants, and so on -- much like your business rules. The system worked by using SQL (and some Excel) to transform the spreadsheet representation of a scenario into a (giant) query. It could then run the query to generate the associated reports. This system has proven flexible, performant, and powerful.
The only possible benefit of using stored procedures is the possibility of accessing the database from applications that use different technologies, such as Python and Java.
I imagine that you already have the existing business logic rules and requirements documented?.. That's the most important factor for designing your schema, choosing the best client development tools and designing your client programs and procedures. I did this for my Pawnshop Management application. The application's functionality is fully table-driven. It has control tables in which the administrator can change valid parameters in order to modify the way the system works. When combined with structured programming techniques, this minimizes the amount of programming code modifications. Banking applications are also a good example that have complex business rules.
© 2022 - 2024 — McMap. All rights reserved.