2

I want to create an app for searching food recipes and intend to create 3 tables below:

  • Recipes(recipeID, recipeName).
  • Ingredients(ingredientsID, ingredientsName).

  • Recipe_ingredients(recipeID,ingredientsID, amount) <= this one is to map two tables above using foreign key.

    My question is in this case, if I have 100 recipes, so I need to create 100 recipe_ingredients table, right? on the other hand, in total, I would have to create one recipes table, 1 ingredients table and 100 recipe_ingredients tables, right? or I only need to make a very long recipe_ingredients including all 100 recipes? I know this question might sound silly, but I'm so new to database world. Any help?

MinhNguyen
  • 816
  • 1
  • 11
  • 26
  • 1
    why 100 `recipe_ingredients` you should need only one `recipe_ingredients` table. – bansi Aug 06 '14 at 06:30
  • Welcome to the world of DataBase design. Please note the difference of __Tables__ and __Records__. 100 Recipes will result in 100 records in the recipe table. If they use say, 100 ingredients this will result in 100 records in the ingredients table. How many records will your relation table Recipes-Ingredients have? It will depend how many recipes use each ingredient. That will vary! Not sure about the design, though: You will need quantities and instructions, too, right? – TaW Aug 06 '14 at 06:40

3 Answers3

2

You would need 3 tables total:

  1. Recipes
  2. Ingredients
  3. Recipe_Ingredients.

In your current design, you'd simply add data to your database; 100 tables would not be required. I think there may be a misunderstanding of the word table.

A table in where you store the data. So in recipes you'd store a RecipeID and its corresponding recipeName.

For the recipe_ingredients, you'd simply connect the recipe and an ingredient of your choice. There is no need to create 100 tables for that. Just add the data in your single table.

Example: say you have one recipe that takes 5 ingredients: RecipeID = 1; IngredientID = 1 to 5. You will have to add each of these ingredients with a connection to the recipeID in the recipe_ingredientstable.

The following entries would be in this table:

PrimaryKey --- RecipeID --- IngredientID
1 --- 1 --- 1
2 --- 1 --- 2
3 --- 1 --- 3
4 --- 1 --- 4
5 --- 1 --- 5

Note that the PrimaryKey is used to distinguish each entry in this table.

If you'd want all ingredients for the recipe with ID 1; you can query this by selecting all IngredientIDs' for RecipeID 1 and then join the ingredientName from your ingredientstable.

On another note, you may want to reconsider your names too. Take a look here: Table Naming Dilemma: Singular vs. Plural Names

Community
  • 1
  • 1
Matthijs
  • 3,162
  • 4
  • 25
  • 46
  • Awesome, thanks so much for your thorough explanation, it really clarifies my confusion. I have another problem, if I want to add amount to each ingredient, should I add it directly to each record of the recipe_ingredients table? in another word, should I create another column for amount on that table?I already marked you answer but I dont have enough reputation to vote it up. Sorry about that. – MinhNguyen Aug 06 '14 at 06:49
  • In theory a relations table should only contain the keys. But theory is just that. Yes, I think that adding the quantity to the relations is fine. – TaW Aug 06 '14 at 08:00
  • Yeah, adding the amounts to the recipe_ingredients table is perfectly okay. This is where the recipes come together with the ingredients. It actually makes sense to place them in that spot. – Matthijs Aug 06 '14 at 08:15
0
i think you only need two tables here.
  1. Recipe - RecipeID, Name, etc...
  2. Ingredients - IngredientsID, RecipeID (reference to Receipe table), Name, Amount
ReynR
  • 11
  • 1
  • 4
  • I want the third table because I need to add amount of each ingredient to each recipe. Multiple recipes can use the same ingredients but different amount. – MinhNguyen Aug 06 '14 at 06:35
  • I dont think you need the third table. In the ingredients table, you already have IngredientsID, RecipeID - which will uniquely identify to which RecipeID the IngredientID, along with the corresponding Amount. – ReynR Aug 06 '14 at 06:39
  • There may be ingredients, like salt, you need in more than one recipe, don't you think? – TaW Aug 06 '14 at 06:45
  • Of course, that's why the table design is like that. i'll edit my post to show what i mean. – ReynR Aug 06 '14 at 06:47
  • Where would you store each individual ingredient then? – Matthijs Aug 06 '14 at 06:47
  • While i'm plotting it just a moment ago, i realized that @Matthijs. There should be a master data table for ingredients. You got it right. – ReynR Aug 06 '14 at 06:56
  • @MinhNguyen - i can't comment on Matthijs' answer but i think your assumption of putting the Amount column in Recipe-Ingredients table is correct. Is that right Matthijs? – ReynR Aug 06 '14 at 07:06
  • @ReynR: That is indeed correct. The `Recipe-ingredientstable` is where the recipe and the ingredients come together. So storing the amounts of the ingredients there is the way to go :) – Matthijs Aug 06 '14 at 08:52
0

i think this will help you , two tables will do the job 

recpies (recpieID, IngredientID)
Ingredient (IngrdientID , IngrdientName , Amount )

to to make things simple Insert like this for example i have recipe : 

recipe name : cake  
ingredients : sugar = 1 kg
              color = 100 gm
              soda  = 500 gm

so when you insert multiple ingredients to ingredient table keep ingredientID same i.e if we insert that cake recpie to tables it would go like that

recpies(1,0001)   // some random unique ID
ingredient (0001 , sugar , 1 kg )
           (0001 , color , 100 gm)
           (0001 , soda  , 500 gm )
user889030
  • 4,353
  • 3
  • 48
  • 51
  • 1
    That was not what I meant: Each recipe contains more than one ingredient, hopefully.. – TaW Aug 06 '14 at 06:43