3

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

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:

  1. Is it a good idea to incorporate a recursive idea inside a db?
  2. 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.

Community
  • 1
  • 1
JDelage
  • 13,036
  • 23
  • 78
  • 112
  • 2
    This is also known as a hierarchical query, or a bill-of-materials query. It is related to representing trees in a DB. If you've not researched on those terms, maybe you should. There are a number of systems for handling such data structures. – Jonathan Leffler Mar 13 '12 at 20:28
  • Not sure it's a tree as each child can have many parent (i.e., a given recipe can be a sub-component of more than one recipe). – JDelage Mar 14 '12 at 18:47
  • It's at least a DAG (directed acyclic graph). Whether it is a tree depends on your definition of tree. Mine clearly allows a node to have more than one other node pointing to it; mathematical terminology might preclude that (go discuss on math.stackexchange.com). There's a bug in the data if it is not an acyclic data structure (but I bet few insertions would check for acyclicity - or whatever the approved term is). But, it is a BOM (bill-of-materials) type structure, where (more or less by definition) things like 6mm nuts can be used in many different assemblies. – Jonathan Leffler Mar 14 '12 at 18:58

1 Answers1

3

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.

ntziolis
  • 10,091
  • 1
  • 34
  • 50
  • even if `isRecipe = T` the step_id can take any value, even 3, which is not a valid Recipe id. This is what I meant with integrity, you cannot enforce this with relational means, but you will have todo this within your application – ntziolis Mar 13 '12 at 20:47