mysql是怎么实现多对多的,如何在MySQL中实现多对多层次结构

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

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...

My questions are:

Is it a good idea to incorporate a recursive idea inside a db?

If so, is this the way to do it, or can it be improved upon?

QUICK EDIT: 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.

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.