如何在代表多对多关系的MySQL闭包表中添加排序顺序? [英] How to add a sort order to a MySQL closure table representing a many-to-many relationship?

问题描述

这是我在此处提出问题的后续措施:
如何实现多对多层次结构MySQL中的结构
而在这里:
如何在MySQL中记录顺序的记录集合.

This is a follow-up to my questions here:
How to implement a many-to-many hierarchical structure in MySQL
and here:
How to record sequential collections of records in MySQL.

简而言之，我想在MySQL中实现一个配方表和另一个指令表.食谱是指一系列或其他食谱的说明.因此，例如，您可以想象一个Peach_preserve配方，一个使用Peach_preserve的Peach_tart以及一系列其他步骤(指令). Peach_preserve可以用于许多其他食谱.

In short, I want to implement in MySQL a table of recipes and another of instructions. A recipe is a sequential series of instructions or other recipes. So for example you could imagine a Peach_preserve recipe, and a Peach_tart that uses the Peach_preserve, plus a series of other steps (instructions). Peach_preserve could be used for many other recipes.

我阅读了 Bill Karwin撰写的这篇博客文章，内容涉及封闭表，我认为该解决方案最能解决我的挑战(我的层次结构是多对多，并且步骤是连续的).因此，例如，我将拥有:

I read this blog post by Bill Karwin about closure tables, and I think this solution best addresses my challenges (my hierarchy is many-to-many and the steps are sequential). So for example I would have:

recipe
id  name
1   Peach preserve
2   Cubed peeled peaches
3   Fresh peaches
4   Powdered sugar
5   Cook together
6   Peel and cut in chunks
7   Mix

step (or instruction)
id  desc
1   Cook together
2   Buy peaches
3   Buy sugar
4   Peel and cut in chunks
5   Mix

recipe_instruction
(Ancestor) (Descendant)
recipe_id  step_id   depth  descendant_is_instruction
3          3         0      0
3          2         1      1
4          4         0      0
4          3         1      1
6          6         0      0
6          4         1      1
2          2         0      0
2          3         1      0
2          2         2      1
2          6         1      0
2          4         2      1
(and so on...)

我不喜欢descendant_is_instruction标志，但是我不知道该怎么做.我想我可以用descendant_is_leaf代替它来识别终端项目...

I'm not a fan of the descendant_is_instruction flag, but I don't know how else to do that. I suppose I could replace it with a descendant_is_leaf to identify terminal items...

排序顺序由一个表表示，该表以1的深度合并了所有关系:

The sort order is represented by a table that incorporates all the relationships at a depth of 1:

Depth=1 table
recipe_id  step_id      order
3           2            1
4           3            1
6           4            1
2           3            1
2           6            2

我在这里简化一下，因为在实践中我将成分和说明分开，但是您明白了.

I'm simplifying here because in practice I would separate ingredients and instructions, but you get the idea.

那么，这是结合分层数据结构和步骤顺序概念的好方法吗?我应该做些什么来改善/简化?

So, is that a good way to combine both a hierarchical data structure and a notion of step order? Anything I should do to improve / simplify?

推荐答案

因此，我对我的问题进行了一些研究，主要利用了比尔·卡尔文(Bill Karwin)在这里和其他地方提供的信息(我最终决定购买他的书).基于此，我认为最好的选择是将面包屑列添加到我的关闭表中，类似于Bill在这里建议的内容

So I have done some research on my problem, leveraging mostly information provided here and elsewhere by Bill Karwin (I eventually decided to buy his book). Based on this, I think the best option is to add a breadcrumbs column to my closure table, similarly to what Bill recommends here MySQL Closure Table hierarchical database - How to pull information out in the correct order .

面包屑允许我ORDER BY，这可以解决我的订购问题.

The breadcrumbs would allow me to ORDER BY, which would solve my ordering problem.

我将通过查询闭合表来查找终端节点，查找除自身外没有祖先的所有节点.

I would find the terminal nodes by querying the closure table, looking for all nodes that have no ancestor other than themselves.

这篇关于如何在代表多对多关系的MySQL闭包表中添加排序顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！