How to add a sort order to a MySQL closure table representing a many-to-many relationship?

Question

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.

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.

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

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

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?

Answer 1

A recipe is a sequential series of instructions or other recipes.

Depending how one read that sentence, this can be ambiguous.

How about:

A recipe is a sequential series of instructions.

An instruction is either simple (a leaf) or complex (uses another recipe).

Which gives:

Table recipe:
- column id
- column name
- column total_cost, total_preparation_time, etc

Table instruction:
- column id
- column recipe_id
- column step_order
- column description
- column child_recipe_id (can be NULL)

So, if the peach tart uses dough and peach preserve:

select * from recipe order by id;
id      name
1       Dough
2       Peach preserve
3       Peach tart

select * from instruction order by recipe_id, step_order;
id recipe_id step_order description     child_recipe_id

100     1       1       Get flour       NULL
101     1       2       Add water       NULL
102     1       3       Mix together    NULL

201     2       1       Peel peaches    NULL
202     2       2       Cube peaches    NULL
203     2       3       Add sugar       NULL
204     2       4       Cook together   NULL

301     3       1       Pre heat oven   NULL
302     3       2       Prepare dough   1
303     3       3       Prepare peach   2
304     3       4       Bake            NULL

There are no "is leaf" flags.

An instruction is a leaf if it does not point to a child recipe, i.e. child_recipe_id is NULL.

Answer 2

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 .

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.

Answer 3

I may be way off here, but Recipe and instruction could be the same table, simplifying your relationships.

Instruction: id, name, is_recipe

Steps: parent_id, child_id, order

Now a recipe can have instructions and recipes as children. An instruction could even be to follow a recipe but with reduced butter...

You may need to add some loop-control though...