How to implement many-to-many hierarchical structure in MySQL

Question

This is a continuation of my question here: How to write sequential collections of records in MySQL

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

In my previous question, I was recommended a design that allows you to specify a specific order for each instruction in the 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 

Now I would like to include the idea that in a recipe, a subcomponent may be another recipe, rather than an atomically discrete instruction. So my idea is to do it like this:

 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 consists of 3 instructions, and Recipe2 consists of one command, then Recipe1 , then 2 more instructions, one of which is a repeat of step 1. I thought of other ways to capture this idea, but they include a bunch of null records . What I don't like is that the key consists of 4 attributes ...

My questions:

• Is it possible to include a recursive idea inside db?
• If so, is this a way to do this, or is it possible to improve it?

QUICK EDIT: I'm starting to read hierarchies. In my example, each recipe can have multiple parents.

Answer 1

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

However, you will need to manage the integrity in your application, since the foreign key cannot be used, since the relationship depends on the isRecipe flag.