Database design - storing a sequence

Question

Imagine the following: there is a "recipe" table and a "recipe-step" table. The idea is to allow different recipe-steps to be reused in different recipes. The problem I'm having relates to the fact that in the recipe context, the order in which the recipe-steps show up is important, even if it does not follow the recipe-step table primary-key order, because this order will be set by the user.

I was thinking of doing something like:

recipe-step table:

id | stepName | stepDescription
-------------------------------
1  | step1    | description1
2  | step2    | description2
3  | step3    | description3
...


recipe table:

recipeId | step
---------------
1        | 1
1        | 2
1        | 3
...

This way, the order in which the steps show up in the step column is the order I need to maintain.

My concerns with this approach are:

• if I have to add a new step between two existing steps, how do I query it? What if I just need to switch the order of two steps already in the sequence?
• how do I make sure the order maintains its consistency? If I just insert or update something in the recipe table, it will pop up at the end of the table, right?

Is there any other way you would think of doing this? I also thought of having a previous-step and a next-step column in the recipe-step table, but I think it would be more difficult to make the recipe-steps reusable that way.

Answer 1

In SQL, tables are not ordered. Unless you are using an ORDER BY clause, database engines are allowed to return records in any order they feel is fastest (for example, a covering index might have the data in a different order, and sometimes even SQLite creates temporary covering indexes automatically).

If the steps have a specific order in a specific recipe, then you have to store this information in the database.

I'd suggest to add this to the recipe table:

recipeId | step | stepOrder
---------------------------
1        | 1    | 1
1        | 2    | 2
1        | 3    | 3
2        | 4    | 1
2        | 2    | 2

---

Note: The recipe table stores the relationship between recipes and steps, so it should be called recipe-step. The recipe-step table is independent of recipes, so it should be called step. You probably need a table that stores recipe information that is independent of steps; this table should be called recipe.