Storing conditional logic expressions/rules in a database

Question

How can I store logical expressions using a RDBMS?

I tag objects and would like to be able to build truth statements based on those tags. (These might be considered as virtual tags.)

Tags
new
for_sale
used
offer

Rules
second_hand_goods = (!new or used) and for_sale
new_offer = new and offer
second_hand_offer = second_hand_goods and offer

• Rules should be able to reference both tags and other rules.
• Schemas that can be easily accessed by hibernate would be preferrable.
• Preferably it will be possible to retrieve the entire rule in one select/call.

The rules are not for use internally by the database but are created and used by an external application that needs to persist these tags and rules.

Walk

Answer 1

From a pragmatic standpoint, you can make computed fields on the database if all of the columns necessary for the computation live on the same table - computed fields can only work from a single record. Most modern DBMS platforms have some support for this feature.

From a theoretical standpoint, you are getting into Semantic Data Modelling. The best paper on this is Hammer and MacLeods Ruritanian Oil Tankers paper, which describes a semantic data modelling notation imaginatively called SDM. SDM uses a structured english type notation for marking up database rules of the sort you describe. If you wanted to generalise your capability and didn't mind writing a parser for SDM, you could make a rule engine where this sort of logic could be configured. This type of model should also be possible to adapt to play nicely with an O/R mapper.

On the minus side, making this sort of tool would be quite time-consuming, so it would only be worth doing if your requirement for managing data semantics was very large. For the example you cite it would comfortably fit into the realms of overkill, but if your problem is much bigger, it might be worth building something like this. If you didn't want to write a parser, you could make an XML schema for marking up a SDM-like language.

Beverlybevers

Answer 2

Managing the nesting/brackets can become quite complex and prone to errors. The way I have done this in the past is to use XML to define the logic as it handles nesting very well. Using SQL Server 2005 or higher you can also store this nicely in a single table.

Your second hand goods logic could be stored as...

<logic type="and">
    <logic type="or">
        <logic type="not">
            <value type="new" />
        </logic>
        <value type="used" />
    </logic>
    <value type="for_sale" />
</logic>

I'm sorry this is not an actual answer to your question and just an alternative way of doing things. I've just found it to work for me in the past.

Ceres

From my experience I can only say that trying to use XML as a programming language sucks big time. – Stomodaeum

I agree completely, however, this is purely for storing a logic based formula. The main point being I would rather use the readily available nesting capabilities of XML as opposed to building this mechanism into a database. – Ceres

hey, I have a similar situation and Im thinking of using such a design...(an SqlXml field) whats more is this whole expression thing is just for a very trivial feature in my system, Just wanted it ask if this is "ok" it terms of design? Since many like the guys above have some complicated way to store it without xml, in the database.@anton – Dix

@RobinDay that looks like the thing I'm searching for. Thanks! Can you please give some ideas how to parse this XML to PHP expression? – Paid

@DenisO. I know its a couple years late but here is some PHP I have that processes something similar -- it has methods implemented for and, or, at least, at most, and sum gist.github.com/bravadomizzou/b16d12d6e0f8994526468dc2d9937a47 – Moxa

Answer 3

As a default, until I've understood a problem well enough to figure out the solution, I would not store business rules in the database. These belong in code. There are always exceptions to any rule however and you could use your RDBMS' stored procedures and / or functions to encapsulate these rules (provided your DB has them). But, as I said, ideally, you would interpret the data in a meaningful way in code.

Update

Sorry, realise I didn't answer your question. You could use functions, if your DB has them, that allow you to pass in parameters and return scalar values, or use stored procedures. You might have 1 per expression and a larger procedure to combine the expressions in some way.

Merline

If you have an ETL app with no UI, where's the 'code' going to sit? – Melchior

Answer 4

How about something like this:

Tables:
 tags( id, name )
 goods ( id, ... )
 goods_tags_mm ( tag_id, good_id )
 rules ( id, name )
 rules_cnf ( id, rule_id )
 rules_cnf_terms ( rules_cnf_id, tag_id )

Chordate

Answer 5

I would use one table

tags(id,name,type,expression,order)

• type would show if the tag is normal or calculated.
• order is reordered if you add new calculated tags, it specifies the order of the calculation for these tags...
• expression is parsed and checked before inserting a row, it could also be built using a GUI (something like how Oracle discoverer does these things).
• You only link the normal tags to the items

For your example second-hand-goods needs to be calculated before second-hand-offer, all the others can be calculated without any dependencies.

1,'new',1,'',NULL
2,'for_sale',1,'',NULL
3,'used',1,'',NULL
4,'offer',1,'',NULL
5,'second_hand_goods',2,'(!new or used) and for_sale',1
6,'new_offer',2,'new and offer',1
7,'second_hand_offer',2,'second_hand_goods and offer',2

An item could be tagged by only for_sale, calculating would give:

second_hand_goods,second_hand_offer

I would have a function that gives a list of all the tags for the item, including direct tags and calculated ones:

for_sale,second_hand_goods,second_hand_offer

Talyah

Answer 6

If you have a limited well-defined set of rules or a "rules universe". You could store in the database the rule as an entity with an ID, then its type which will lookup the rule implementation in the code. Finally you can also include in the database all the arguments / constant / literals needed to evaluate the rule and / or any other entity dependency already reflected in your ER design for example:

Rule
   + ID
   + type

Rule Literal
   + FK to Rule(ID)
   + name  VARCHAR
   + type  VARCHAR
   + value VARCHAR

...

In code you would implement a simple lookup of class instance implementing the rule interface using the rule type as lookup key e.g. SUM_LOWER_THAN_X, in Python like this where the arguments come from the database:

{ 'SUM_LOWER_THAN_X': lambda x, y, z: x + y < z }

The advantage of this is that your rules will live in your code-base, therefore can be unit-tested, version controlled and evolve as part of your development release cycles.

Mho