How can I store logical expressions using a RDBMS?
如何使用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.)
我标记对象，并希望能够基于这些标记构建真值语句。 （这些可能被视为虚拟标签。）
Tagsnewfor_saleusedoffer
标签new for_sale used offer
Rulesecond_hand_goods = (!new or used) and for_salenew_offer = new and offersecond_hand_offer = second_hand_goods and offer
规则second_hand_goods =（！new或used）和for_sale new_offer = new并提供second_hand_offer = second_hand_goods并提供
- Rules should be able to reference both tags and other rules.
- 规则应该能够引用标签和其他规则。
- Schemas that can be easily accessed by hibernate would be preferrable.
- 可以通过hibernate轻松访问的模式是首选的。
- Preferably it will be possible to retrieve the entire rule in one select/call?
- 优选地，可以在一个选择/呼叫中检索整个规则？
How do you guys store expressions and business rules in your databases?
你们如何在数据库中存储表达式和业务规则？
Thanks in advance.
提前致谢。
Update
To be clear, the rules are not for use internally by the database but created and used by an external application that needs to persist these tags and rules. Thanks.
更新要明确，规则不是由数据库内部使用，而是由需要保留这些标记和规则的外部应用程序创建和使用。谢谢。
5 个解决方案
#1
3
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.
从实用的角度来看，如果计算所需的所有列都存在于同一个表中，则可以在数据库上创建计算字段 - 计算字段只能在单个记录中工作。大多数现代DBMS平台都对此功能有一些支持。
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.
从理论的角度来看，您正在进入语义数据建模。关于此的最佳论文是Hammer和MacLeods Ruritanian Oil Tankers论文，该论文描述了一种富有想象力的称为SDM的语义数据建模符号。 SDM使用结构化英语类型表示法来标记您描述的排序的数据库规则。如果您想要概括您的功能并且不介意为SDM编写解析器，那么您可以创建一个可以配置此类逻辑的规则引擎。这种类型的模型也应该可以适应与O / R映射器很好地协作。
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.
从负面来看，制作这种工具非常耗时，因此，如果管理数据语义的要求非常大，那么这样做是值得的。对于这个例子，你引用它可以很容易地适应过度杀伤的领域，但如果你的问题更大，那么建立这样的东西可能是值得的。如果您不想编写解析器，则可以创建XML模式以标记类似SDM的语言。
#2
1
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.
管理嵌套/括号可能会变得非常复杂并且容易出错。我过去这样做的方法是使用XML来定义逻辑，因为它很好地处理嵌套。使用SQL Server 2005或更高版本，您还可以将其妥善存储在单个表中。
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.
对不起，这不是你问题的实际答案，只是另一种做事方式。我刚才发现它适合我。
#3
0
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.
默认情况下，在我完全理解问题并找出解决方案之前，我不会将业务规则存储在数据库中。这些属于代码。但是，任何规则都有例外，您可以使用RDBMS的存储过程和/或函数来封装这些规则（假设您的数据库具有这些规则）。但是，正如我所说，理想情况下，您将在代码中以有意义的方式解释数据。
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.
对不起，意识到我没有回答你的问题。您可以使用函数（如果您的数据库具有它们），允许您传入参数并返回标量值，或使用存储过程。您可能有1个表达式和一个更大的过程以某种方式组合表达式。
#4
0
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 )
#5
0
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).
- 在插入行之前解析并检查表达式，它也可以使用GUI构建（类似于Oracle发现者如何处理这些事情）。
- 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:
一个项目只能被for_sale标记，计算将给出：
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
#1
3
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.
从实用的角度来看，如果计算所需的所有列都存在于同一个表中，则可以在数据库上创建计算字段 - 计算字段只能在单个记录中工作。大多数现代DBMS平台都对此功能有一些支持。
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.
从理论的角度来看，您正在进入语义数据建模。关于此的最佳论文是Hammer和MacLeods Ruritanian Oil Tankers论文，该论文描述了一种富有想象力的称为SDM的语义数据建模符号。 SDM使用结构化英语类型表示法来标记您描述的排序的数据库规则。如果您想要概括您的功能并且不介意为SDM编写解析器，那么您可以创建一个可以配置此类逻辑的规则引擎。这种类型的模型也应该可以适应与O / R映射器很好地协作。
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.
从负面来看，制作这种工具非常耗时，因此，如果管理数据语义的要求非常大，那么这样做是值得的。对于这个例子，你引用它可以很容易地适应过度杀伤的领域，但如果你的问题更大，那么建立这样的东西可能是值得的。如果您不想编写解析器，则可以创建XML模式以标记类似SDM的语言。
#2
1
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.
管理嵌套/括号可能会变得非常复杂并且容易出错。我过去这样做的方法是使用XML来定义逻辑，因为它很好地处理嵌套。使用SQL Server 2005或更高版本，您还可以将其妥善存储在单个表中。
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.
对不起，这不是你问题的实际答案，只是另一种做事方式。我刚才发现它适合我。
#3
0
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.
默认情况下，在我完全理解问题并找出解决方案之前，我不会将业务规则存储在数据库中。这些属于代码。但是，任何规则都有例外，您可以使用RDBMS的存储过程和/或函数来封装这些规则（假设您的数据库具有这些规则）。但是，正如我所说，理想情况下，您将在代码中以有意义的方式解释数据。
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.
对不起，意识到我没有回答你的问题。您可以使用函数（如果您的数据库具有它们），允许您传入参数并返回标量值，或使用存储过程。您可能有1个表达式和一个更大的过程以某种方式组合表达式。
#4
0
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 )
#5
0
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).
- 在插入行之前解析并检查表达式，它也可以使用GUI构建（类似于Oracle发现者如何处理这些事情）。
- 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:
一个项目只能被for_sale标记，计算将给出：
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