This is a follow-up to my questions here:
http://stackoverflow.com/questions/9691366/how-to-implement-a-many-to-many-hierarchical-structure-in-mysql
http://stackoverflow.com/questions/9691366/how-to-implement-a-many-to-many-hierarchical-structure-in-mysql
and here:
http://stackoverflow.com/questions/9610942/how-to-record-sequential-collections-of-records-in-mysql
http://stackoverflow.com/questions/9610942/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.
http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
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?