动态验证：每个字段0个或多个，如何AND或OR验证规则？
进一步：动态表单字段验证的数据库设计
我将如何建模当特定字段可以有 0 个或多个验证规则并且每个验证规则通过 AND 或 OR 与另一个规则“相关”时，数据库会执行此操作。
例如，假设我的 field1 需要最少 5 个字符且最多 10 个字符。这是适用于同一字段并通过“AND”关联的 2 条规则。规则如何通过“OR”关联的示例如下：field1 应该正好有 5 个字符或正好有 10 个字符。
验证可能会变得复杂并具有 n 层嵌套。我如何在数据库中执行此操作？
Furthering: Database design for dynamic form field validation
How would I model the database when a particular field can have 0 or more validations rules AND that each validation rule is "related" to another rule via AND or OR.
For example, say I have field1 that needs to be minimum of 5 characters AND maximum 10 characters. These are 2 rules that apply to the same field and are related via an "AND." An example of how rules relate via an "OR" would be something like this: field1 should have exactly 5 characters OR exactly 10 characters.
The validation could get complex and have n-levels of nesting. How do I do this in a DB?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为对于如何建模没有一个简单的答案。希望以下对话能够帮助您入门，并让您对所涉及的问题有所了解。
据我所知，您至少有三种类型的实体：字段、简单规则和复杂规则（即通过组合其他简单和/或复杂规则而制定的规则）。
一个好消息是，我很确定您只需要两种类型的复杂规则：AND 规则和 OR 规则，每个规则都应用一组子规则，并根据结果返回 true 或 false由这些子规则返回。
因此，您想要构建一个结构，其中每个表单都有 1 个或多个字段，每个字段有 0 个或多个验证规则，每个规则有 0 个或多个子规则。
一个挑战就是跟踪每个复杂规则的结构。让我印象最简单的方法是在树结构中，每个节点都有一个父节点。因此，您可能有一个 OR 规则，其父级为 0（表明它是顶级规则）。这样就会有 2 个或多个以 OR 的ruleId 作为其父级的规则。反过来，其中任何一个都可能是 AND 或 OR 规则，它们将成为其他规则的父规则。依此类推，顺着树往下走。
另一个挑战是如何从数据库中提取结构，以便验证表单。最好尽量减少执行此操作所需的查询数量。在直树中，结构仅由了解其父节点的子节点建立，您需要单独的查询来获取每个父节点的直接子节点。因此，如果您可以将所有子项聚合到一个祖先下，那就太好了。
如果任何规则只能分配给 1 个字段，那么您可以在规则表中包含一个 fieldId 列，并且每个规则将分配给一个字段。然后，您可以将表单连接到其字段，并将这些字段连接到其规则，并在一个查询中提取所有内容。然后应用程序逻辑将负责将数据转换为功能树结构。
但是，如果您希望规则可重用，那是行不通的。例如，您可能需要一个抽象的邮政编码规则，它组合了多个子规则（而不是一个巨大的正则表达式）。然后，您可能希望将其设为美国邮政编码规则，并为加拿大制定另一个规则，为多个国家/地区中的任何一个制定另一个规则，然后您可能希望根据正在验证的字段来组合其中的部分或全部规则。因此，您可能会将美国或加拿大邮政编码规则应用于某些字段，将仅限美国的规则应用于其他字段等。
实现此目的的一种方法是从规则中删除 fieldId 字段，并添加新的 field_rules 联结表以 fieldId 和ruleId 作为其列。但是，从字段中删除 fieldId 会使您无法使用单一查询方法来提取字段的所有规则（包括子规则），更不用说表单了。因此，您可以将起源列添加到规则表中，并且复杂规则的所有子规则都将以该顶级字段的 id 作为其起源。
现在，如果您希望允许覆盖特定字段的某些可重用规则的数据，事情可能会变得更加复杂。然后，您可以添加新的 field_rule_data 表，或者仅将数据列添加到 field_rules 表中。
实现树结构意味着构建和应用复杂规则的应用程序逻辑可能必须是递归的。
话虽如此，我怀疑您真正的挑战将出现在用户界面级别。
编辑
我又想了一些，它看起来更加复杂。我确信以下内容也不够充分，但我希望它有助于找出完整的答案。
我现在认为您有 5 个表：rules、rule_defs、rule_defs_index、fields、field_rules。他们是这样的：
规则
规则 ID (PK)
姓名
数据（可以为空）
Rule_Defs
规则定义 ID (PK)
rule_id（FK 到rule_id）
父级（FK 到rule_def_id）
origin（FK 到rule_def_id：可选便利字段）
Rule_Defs_Index
规则 ID (FK)
rule_def_id (FK)
字段
field_id (PK)
名称
字段_规则
field_id（FK 和 PK 的一部分）
Rule_id（FK 和 PK 的一部分）
只是以一种模糊可信的方式在这里编造一些东西，这里有一些示例数据：
Rules id name data 1 AND 2 OR 3 5 digits /^\d{5,5}$/ 4 5-4 pattern /^\d{5,5}-\d{4,4}$/ 5 US Zip 6 6 alphanumerics /^[A-Za-z0-9]{6,6}$/ 7 US or Canada Zip Rule_Defs id rule_id parent origin 1 5 0 1 2 2 1 1 3 3 2 1 4 4 2 1 5 7 0 5 6 2 5 5 7 5 6 5 8 6 6 5 Rule_Defs_Index (just data for US Canada Zip since that's biggest) rule_id rule_def_id 7 2 7 3 7 4 7 5 7 6 7 7 Fields field_id name 1 billing zip 2 shipping zip Field_Rules field_id rule_id 1 7 2 7请注意，这里的假设是相对于应用规则，创建和编辑规则很少发生。因此，创建和编辑将是相当麻烦且相对缓慢的活动。为了避免在更常见的规则应用中出现这种情况，Rule_Defs_Index 应该能够提取通过单个查询为字段（或表单）构建规则结构所需的所有内容。当然，一旦检索到数据，应用程序就必须做大量的工作才能将数据转换为有用的结构。
请注意，您可能希望以序列化形式缓存构造的数据，并在编辑或创建规则时在相对罕见的情况下重建缓存。
I don't think there's a simple answer to how to model this. The following conversation will hopefully get you started, and give you some sense of the issues involved.
So far as I can see, you have at least three types of entity: fields, simple rules, and complex rules (that is, rules made by combining other simple and/or complex rules).
The one piece of good news is that I'm pretty sure you just need two types of complex rule: an AND rule, and an OR rule, each of which applies a set of sub rules, and returns true or false based on the results returned by those subrules.
So you want to build a structure where each form has 1 or more fields, each field has 0 or more validation rules, and each rule has 0 or more sub-rules.
One challenge is just to keep track of the structure of each complex rule. What strikes me as the simplest way to do this is in a tree structure where each node has a parent. So you might have an OR rule with a parent of 0 (indicating that it's a top-level rule). There would then be 2 or more rules with the OR's ruleId as their parent. In turn, any of those might be an AND or OR rule which would be the parent of other rules. And so on down the tree.
Another challenge is how to extract your structure from the db so you can validate a form. It's preferable to minimize the number of queries it takes to do this. In a straight tree, where the structure is only established by children nodes knowing their parents, you'd need a separate query to get each parent's immediate children. So it'd be nice if you could aggregate all the children together under a single ancestor.
If any rule can only be assigned to 1 field, then you can have a fieldId column in your rules table, and each rule will be assigned to a field. Then you can join a form to its fields, and those fields to their rules, and pull out everything in one query. Then the application logic would be responsible for turning the data into a functional tree structure.
However, if you want rules to be reusable, that's not going to work. For example, you might want an abstract zip code rule which combined several sub rules (rather than being a giant regex). And then you might want to make that a US zip code rule, and make another for Canada, and another for any of multiple countries, and then you might want to combine some or all of those depending on which field was being validated. So you might have, for example a US OR Canada zip rule applied to some fields, a US only rule applied to other fields, etc.
One way to do this is to remove the fieldId field from rules, and add a new field_rules junction table with fieldId and ruleId as its columns. However, removing fieldId from fields puts you back into not having a single-query means of extracting all the rules (including sub rules) for a field, never mind for a form. So you might add an origin column to the rules table, and all the subrules of a complex rule would have that top-level field's id as their origin.
Now things might get even more complex if you want to allow overriding some of a reusable rule's data for specific fields. Then you might add either a new field_rule_data table, or just data columns to the field_rules table.
Implementing a tree structure means that your application logic for both building and applying complex rules is probably going to have to be recursive.
Having said all that, I suspect your real challenge is going to be at the UI level.
Edit
I thought about this some more, and it's seeming even more complicated. I'm sure the following is also inadequate, but I hope it will facilitate figuring out a full answer.
I'm now thinking you have 5 tables: rules, rule_defs, rule_defs_index, fields, field_rules. They go something like this:
Rules
rule_id (PK)
name
data (can be null)
Rule_Defs
rule_def_id (PK)
rule_id (FK to rule_id)
parent (FK to rule_def_id)
origin (FK to rule_def_id: optional convenience field)
Rule_Defs_Index
rule_id (FK)
rule_def_id (FK)
Fields
field_id (PK)
name
Field_Rules
field_id (FK and part of PK)
rule_id (FK and part of PK)
Just making stuff up here in a vaguely plausible way, here's some sample data:
Rules id name data 1 AND 2 OR 3 5 digits /^\d{5,5}$/ 4 5-4 pattern /^\d{5,5}-\d{4,4}$/ 5 US Zip 6 6 alphanumerics /^[A-Za-z0-9]{6,6}$/ 7 US or Canada Zip Rule_Defs id rule_id parent origin 1 5 0 1 2 2 1 1 3 3 2 1 4 4 2 1 5 7 0 5 6 2 5 5 7 5 6 5 8 6 6 5 Rule_Defs_Index (just data for US Canada Zip since that's biggest) rule_id rule_def_id 7 2 7 3 7 4 7 5 7 6 7 7 Fields field_id name 1 billing zip 2 shipping zip Field_Rules field_id rule_id 1 7 2 7Note that the assumption here is that it creating and editing rules will happen rarely relative to applying rules. Thus creating and editing will be fairly cumbersome and relatively slow activities. To avoid this being the case for the far more common application of rules, the Rule_Defs_Index should make it possible to extract everything needed to build a rule structure for a field (or a form) with a single query. Of course, once it's retrieved, the application will have to do a fair amount of work to turn the data into a useful structure.
Note that you might want to cache the constructed data in serialized form, rebuilding the cache in the relatively rare instances when a rule is edited or created.