如何在MySQL中实现多对多层次结构 [英] How to implement a many-to-many hierarchical structure in MySQL

问题描述

这是我在此处提出的问题的跟进内容:如何在MySQL中记录记录的顺序集合

This is a follow-up from my question 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.

在上一个问题中，我建议我设计一个允许配方中每个指令具有特定顺序的设计:

In my previous question I was recommended a design to allow for a specific order for each instruction within a recipe:

recipe
id  name
1   Recipe1
2   Recipe2

recipe_instruction
recipe_id  instruction_id  sortorder
1          5               1
1          3               2
1          4               3
2          6               1
2          7               2
2          3               3
2          6               4

我现在想做的就是将这样的想法纳入其中:一个配方中的一个子组件可能是另一个配方，而不是原子/离散指令.所以我的想法是这样:

What I would like to do now is to incorporate the idea that within a recipe a sub-component might be another recipe rather than an atomic / discrete instruction. So my idea is to do it this way:

recipe
id  name
1   Recipe1
2   Recipe2

recipe_instruction
recipe_id  step_id  isRecipe    sortorder
1          5             F           1
1          3             F           2
1          4             F           3
2          1             F           1
2          1             T           2
2          2             F           3
2          1             F           4

在这里您可以看到Recipe1由3条指令组成，而Recipe2由一条指令组成，然后是Recipe1，然后是另外2条指令，其中一条是步骤1的重复.捕获此想法的其他方法，但它们涉及大量空条目.我在上面不喜欢的是键是由4个属性组成的...

Here you can see that Recipe1 is made of 3 instructions, and Recipe2 is made of one instruction, then Recipe1, then another 2 instructions, one of which is a repeat of step 1. I thought of other ways to capture this idea but they involve a bunch of null entries. What I don't like in the above is that the key is made of the 4 attributes...

我的问题是:

1. 在数据库中合并一个递归的想法是一个好主意吗?
2. 如果是这样，是这样做的方法还是可以改进的方法?

快速我开始阅读层次结构.在我的示例中，每个食谱可以有多个父母.

QUICK I'm starting to read on hierarchies. In my example, each one recipe can have several parents.

推荐答案

这是一种非常常见的技术.它用于在数据库中存储分层数据(您称其为递归).

This is a very common technique. It is used to store hierarchical data (you call it recursive) within a db.

但是，由于该关系是基于isRecipe标志的条件，因此由于不能使用外键，因此您将不得不管理应用程序的完整性.

However you will have to manage the integrity in your application, because a foreign key cannot be used, since the relation is conditional to the isRecipe flag.

这篇关于如何在MySQL中实现多对多层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！