SQL中的分层标记 [英] Hierarchical tagging in SQL
问题描述
我有一个PHP网络应用程序，它使用MySQL数据库进行对象标记，其中我使用了标签结构作为这个SO问题。
我想实现一个标签层次结构，其中每个标签可以有一个唯一的父标签。搜索父标签T然后将匹配T的所有后代（即T，父母是T（T的孩子），T的孙子等的标签）。
最简单的做法似乎是添加一个ParentID字段到标签表，其中包含标签的父标签的ID，或一些魔术数字，如果标签没有父。然而，搜索后代需要对数据库进行重复的完全搜索以找到每个一代中的标签，这是我想避免的。
A ）更快，但是这种做法的标准化方法是创建一个包含每个标签的所有子标签的表，或者甚至每个标签的所有后代。但是，这会带来数据库中数据不一致的风险（例如，一个标签是多个父级的子标签）。
有很好的方法来查询阿里的回答有一个链接到 Joe Celko在SQL for Smarties中的树和层次结构，这确认了我的怀疑 - 没有一个简单的数据库结构，提供最好的所有世界。最好的我的目的似乎是本书中详述的频繁插入树，这就像阿里的链接的嵌套集模型，但与非连续索引。这允许O（1）插入（非结构化BASIC行编号），在需要时偶尔进行索引重组。
I have a PHP web application which uses a MySQL database for object tagging, in which I've used the tag structure accepted as the answer to this SO question.
I'd like to implement a tag hierarchy, where each tag can have a unique parent tag. Searches for a parent tag T would then match all descendants of T (i.e. T, tags whos parent is T (children of T), grandchildren of T, etc.).
The easiest way of doing this seems to be to add a ParentID field to the tag table, which contains the ID of a tag's parent tag, or some magic number if the tag has no parent. Searching for descendants, however, then requires repeated full searches of the database to find the tags in each 'generation', which I'd like to avoid.
A (presumably) faster, but less normalised way of doing this would be to have a table containing all the children of each tag, or even all the descendants of each tag. This however runs the risk of inconsistent data in the database (e.g. a tag being the child of more than one parent).
Is there a good way to make queries to find descendants fast, while keeping the data as normalised as possible?
Ali's answer has a link to Joe Celko's Trees and Hierarchies in SQL for Smarties, which confirms my suspicion - there isn't a simple database structure that offers the best of all worlds. The best for my purpose seems to be the "Frequent Insertion Tree" detailed in this book, which is like the "Nested Set Model" of Ali's link, but with non-consecutive indexing. This allows O(1) insertion (a la unstructured BASIC line numbering), with occasional index reorganisation as and when needed.
这篇关于SQL中的分层标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
