Database Design - Consistency

Question

Imagine the following: there is a recipe table and a recipe step table. The idea is to allow the use of different recipes in different recipes. The problem that I have is that, in the context of the recipe, the order in which the steps of the recipes are displayed is important, even if it does not correspond to the order of the primary key of the recipe table, since this order will be set by the user.

I was thinking of 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
 ...

So the order in which steps appear in the steps column is the order I need to maintain.

My problems with this approach are as follows:

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

Is there any other way you could do this? I also thought that there is a column in the recipe table for the previous step and the next step, but I think it would be harder to make the recipe steps reused.

Answer 1

In SQL, tables are not ordered. If you do not use the ORDER BY , database engines are allowed to return records in any order that they consider to be the fastest (for example, a coverage index can have data in a different order, and sometimes even SQLite automatically creates temporary coverage indexes).

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

I would suggest adding 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 keeps the relationship between recipes and steps, so it should be called recipe-step . The recipe-step table is not dependent on recipes, so it should be called step . You probably need a table that stores information about recipes that does not depend on steps; this table should be called recipe .