在材料化路径上强制参照完整性？

Question

我正在尝试使用Materialized Path模型实现类似树形结构的功能，该模型在此处有详细描述：http://www.dbazine.com/oracle/or-articles/tropashko4。

在[path]字段上强制执行引用完整性是否可能？ 我不知道SQL如何做到这一点，我必须在DAL中手动执行吗？

- hyperslug

Answer 1

如果您使用物化路径或嵌套集合解决方案来处理分层数据，那么在DAL中必须自行执行数据完整性强制执行。

相邻列表支持引用完整性，并且对于我称之为“闭包表”的设计（Tropashko将此设计称为“传递闭包关系”）也是如此。

- Bill Karwin

---

这是一篇关于传递闭包解决方案的文章：http://www.codeproject.com/KB/database/Modeling_DAGs_on_SQL_DBs.aspx @Bill Karwin：您在OLTP/交互式场景中使用“闭包表”部署过多大的数据集，而没有遇到插入性能问题？ - nawroth

@nawroth：树中仅有数千个节点，所以说得好。如果需要表示非常深的树，则会有许多行。如果需要表示许多浅树，则较为适中。 - Bill Karwin

新信息：我进行了一个490,000个节点的树的概念验证，我可以在0.0006秒内获取任何节点的祖先列表，并且整个树的每级扩展只需0.3秒。http://www.slideshare.net/billkarwin/models-for-hierarchical-data - Bill Karwin

谢谢您记得这个帖子。我更深入地研究了这些方法，发现我最喜欢闭包表。在我的情况下，它很有意义，因为插入数据的用户不会注意到开销，并且它可以实现大规模、快速的检索。 - hyperslug

Answer 2

在Vadim Tropashko的文章中，"Materialized path"引入了关系中的顺序概念（"Jones是第二个成员"）。

"Materialized path"实际上就是在传递闭包上的"某种形式的物化视图"，因此与任何其他"物化视图"遇到的问题完全相同，只不过由于涉及闭包而使算法问题更加严重。

当约束条件应用于闭包时，SQL几乎完全无能为力。（即：是的，SQL要求您自己做所有事情。）这是RM展示其几乎无限的能力的领域之一，但SQL表现极其糟糕，人们错误地认为SQL是关系型的大多数领域之一。

（@Bill Karwin：我想能够给您+1，以便您对树的深度与性能结果之间的关系发表评论。没有已知的算法可以计算具有“疯狂”深度的树的闭包，这是一个算法问题，而不是SQL或关系问题。）

编辑

是的，RM = 关系模型

- Erwin Smout

---

是的，物化路径是反规范化的一个例子。它对某些类型的查询具有效率上的优势，但牺牲了关系模型的诸如参照完整性等好处。 - Bill Karwin

顺便说一下，您可以将鼠标悬停在评论左侧的一个不可见的点赞箭头上，并为该评论提供一些认可。但这并不会给予声望积分。 - Bill Karwin

Answer 3

在物化路径模型中，您可以使用任意字符串（可能是Unicode字符串，以允许超过256个子项），而不是形式为“x.y.z”的特殊字符串。然后，父级的ID是直接子项的ID，去掉最后一个字符。您可以轻松地通过检查约束来强制执行此操作，例如（我的示例适用于PostgreSQL）。

check(parent_id = substring(id from 1 for char_length(id)-1)),

在创建表的命令中。如果您坚持使用“x.y.z”形式的字符串，则必须尝试正则表达式，但我猜测可以找到相应的检查约束。

- Whoever

---

如果您想强制要求根节点的id长度为1，您可以在表定义中另外添加约束条件check((parent_id is null) or (char_length(id)=1))。 - Whoever

Answer 4

是的，我们可以在[path]字段上“强制执行引用完整性”。我几年前就这样做了，在这里有描述：

将配置设置存储为数据库中的层次结构

- A-K