MySQL recipe collection

Question

What is the best way to design a MySQL database for recipes? I want to have for example:

Pancake (thing)
   Ingredients (thing, amount, unit)
     flour 1.5 cups
     baking powder 3.5 teaspoon
     salt 1 teapoon
     white sugar 1 tablespoon

Meatballs (thing)
   Ingredients (thing, amount, unit)
     blablabla xxx cups
     blablabla yyy teaspoon

I guess I could set up a new table for each recipe but I'm sure thats not the right way...

What have you tried? How would you evaluate any solutions - speed, flexibility, usage scenarios? — Neville Kuyt, Mar 07 '14 at 12:13

score 0 · Answer 1 · answered Mar 07 '14 at 12:08

0

Your database should contain 2 tables:

Recipe : (PRIMARY KEY)ID_RECIPE, NOUM_RECIPE, DESCRIPTION_RECIPE, TYPE_RECIPE INGREDIENTS : (PRIMARY KEY)ID_INGREDIENT, (FOREIGN KEY)ID_RECIPE, NOUM_INGREDIENT, TYPE_INGREDIENT, AMOUNT, UNIT

answered Mar 07 '14 at 12:08

ASNAOUI Ayoub

462
2
9

Something like this http://stackoverflow.com/questions/12402422/how-to-store-a-one-to-many-relation-in-my-sql-database-mysql ? – user872661 Mar 07 '14 at 15:00

score 0 · Answer 2 · answered Mar 07 '14 at 12:09

It really depends on what you want to do with the data.

If you're simply looking to store and display recipes I would just create one table recipes with fields for ingredients, method etc.

E.g.

recipes
--------
id (PK, int)
name (Varchar)
ingredients (Varchar)
method (Varchar)

You could add additional fields for cooking time, prep time as needed.

If you want to be able to do a search on ingredients you might prefer each food type to be in a separate table. Something like this:

recipes
--------
id (PK, int)
name (Varchar)
method (Varchar)

ingredients
--------
id (PK, int)
name (Varchar)

recipe_ingredient
--------
recipe_id (PK, int)
ingredient_id (PK, int)
quantity (Varchar)

score 0 · Answer 3 · answered Mar 08 '14 at 11:40

I would suggest something like the following.

You have a number of 'base' ingredients - such as eggs, vegetables, fruit - which you combine together in various quantities to produce a quantity of another ingredient - such as strawberry jam - following a method. This ingredient can then be used with others in various quantities to produce a quantity of another ingredient - such as a cake - again following a method. This can be continued up to individual meals and meal plans if you so wish.

+----------------+     +-----------------+
| RECIPE         |     | INGREDIENT      |
+----------------+     +-----------------+
| recipe_id      |-----| ingredient_id   |
| ingredient_id  |     | ingredient_name |
| method         |     +-----------------+
| quantity       |         |
| unit           |         |
+----------------+         |
             |             |
             |             |
          +-------------------+
          | RECIPE_INGREDIENT |
          +-------------------+
          | recipe_id         |
          | ingredient_id     |
          | quantity          |
          | unit              |
          +-------------------+

MySQL recipe collection

3 Answers3