Designing a database of Recipes

Question

Task

I need database design to record Recipes.

Recipes can contain several Ingredients and several ordered HowToSteps.
Each HowToStep can be split into ordered HowToSections.
A single recipe can contain a mix of several (ordered) HowToStep and (ordered) HowToSection elements.

My Solution

Table HowToSection with FK to the table HowToStep with FK to the table Recipe.
Table Ingredient with FK to the table Recipe.

Issue

I have no idea how to handle a business rule which stipulates that

"A single recipe can contain a mix of several (ordered) HowToStep and (ordered) HowToSection elements".

Weird... sounds like a university assignment question....................... — Murphybro2, Jan 28, 2019 at 21:38
Not your question, but you'll be bumping into it later. The relationship between ingredients and recipes in many-to-many. One recipe has many ingredients, but an ingredient, like chopped garlic can go into many recipes. Learn how to design for this case, using a third intermediate table. — Walter Mitty, May 10, 2019 at 10:13

danblack · Accepted Answer · 2018-12-18 21:12:30Z

1

You'd have a:

RecipeSteps table containing, recipe_id, stepnumber for order, and a HowToStep ref

And:

RecipeSections table containing, recipe_id, sectionnumber for order, and a HowToSection ref

The stepnumber and sectionnumber need to be non overlapping (per recipe_id for the pulling in the steps/sections in an order.

answered Dec 18, 2018 at 21:12

danblack

7,5852 gold badges10 silver badges26 bronze badges

Add a comment |

1 Answer 1