How to record sequential collections of records in MySQL

Question

Suppose I have a table that holds some type of record, say cooking instructions like "Fold the melted chocolate into the egg whites". The table contains a unique ID field and the string.

I want to build another table for recipes (each with a unique ID and a name), each of which would be a series of sequential instructions (some instructions would be used for several/many recipes).

What is the best way to structure my recipe table to map a recipe's unique ID to a sequential series of instructions (which IDs are not sequential)?

Answer 1

Try a normalized design like this:

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

To get the list of instructions for a specific recipe you can use this query:

SELECT i.the_string
FROM recipe_instruction AS ri
JOIN instruction AS i
ON ri.instruction_id = i.id
WHERE ri.recipe_id = 1
ORDER BY ri.sortorder