SQL database many to many or one to any-CodePudding

I'm new to MVC and coding generally, so forgive me if I ask a question that sounds silly. I'm trying to create a database that's going to contain recipes and ingredients. I'm trying to understand how I'm going to build my models. I know I need at least three tables or four connected through foreign key. Where I have difficulty is in the creation of the recipe, how do I select all the ingredients in these tables? I'm accustomed to the conceptual model first.

I've read a lot of online explanations about one to one relationships. One to many and many to many, but for some reason, I fail to see how I can create a recipe with all the ingredients at once from three tables. Need help please.

CodePudding user response：

This is a really good example of how to set up many-to-many tables. In this example, we shall include Toast, Roast Chicken and Cake as the recipes.

(Fair warning: I'm not a chef. I know how to make toast though!)

You can follow along at this db<>fiddle

We'll start with the basic two tables first - Recipes and Ingredients.

CREATE TABLE Recipes (Recipe_ID int PRIMARY KEY, Recipe_Name nvarchar(50));
INSERT INTO Recipes (Recipe_ID, Recipe_Name) VALUES
(1, 'Toast'),
(2, 'Roast chicken'),
(3, 'Cake');

In Recipes, we just have the recipe names. And in Ingredients, we just have the ingredients that could be included.

CREATE TABLE Ingredients (Ingredient_ID int PRIMARY KEY, Ingredient_Name nvarchar(50));
INSERT INTO Ingredients (Ingredient_ID, Ingredient_Name) VALUES
(1, 'Slice of bread'),
(2, 'Butter'),
(3, 'Whole raw chicken'),
(4, 'Vegetables'),
(5, 'Cake mix'),
(6, 'Water');

That is two of our tables.

Then the third is a many-to-many table that links them - for example, the recipe 'Toast' requires 'Slice of bread' and 'Butter'. However, it is not just the ingredient that's important - it's also the amount. In this case, we want 2 slices of bread, and 1 tablespoon of butter.

CREATE TABLE Recipe_Ingredients (
    Recipe_ID int, Ingredient_ID int, Ingredient_Amount nvarchar(20),
    PRIMARY KEY (Recipe_ID, Ingredient_ID));

INSERT INTO Recipe_Ingredients (Recipe_ID, Ingredient_ID, Ingredient_Amount) VALUES
(1, 1, '2'),              -- 2 slices of bread
(1, 2, '1 tablespoon');   -- some butter

In the above, Recipe_ID 1 (Toast) has 2 of Ingredient_ID 1 (Slice of bread) and 1 tablespoon of Ingredient_2 (Butter).

We can then do the same for Roast chicken - adding 1 whole chicken, 1kg/2lbs of vegetables, and 1 tablespoon of butter!

INSERT INTO Recipe_Ingredients (Recipe_ID, Ingredient_ID, Ingredient_Amount) VALUES
(2, 3, '1'),              -- 1 whole chicken
(2, 4, '1kg/2lb'),        -- 1kg of veges
(2, 2, '1 tablespoon');   -- and some butter

Then for dessert, some Cake.

INSERT INTO Recipe_Ingredients (Recipe_ID, Ingredient_ID, Ingredient_Amount) VALUES
(3, 5, '1 packet'),      -- cake mix
(3, 6, '200ml'),         -- some water
(3, 2, '3 tablespoons'); -- and some butter

To show the recipes and ingredients, you join the two tables (Recipes and Ingredients) together using the many-to-many table.

SELECT Recipes.Recipe_Name, Ingredients.Ingredient_Name, Recipe_Ingredients.Ingredient_Amount 
FROM Recipes
    INNER JOIN Recipe_Ingredients ON Recipes.Recipe_ID = Recipe_Ingredients.Recipe_ID
    INNER JOIN Ingredients ON Recipe_Ingredients.Ingredient_ID = Ingredients.Ingredient_ID
ORDER BY Recipes.Recipe_ID, Ingredients.Ingredient_ID;

And here are the results

Recipe_Name     Ingredient_Name     Ingredient_Amount
Toast           Slice of bread      2
Toast           Butter              1 tablespoon
Roast chicken   Butter              1 tablespoon
Roast chicken   Whole raw chicken   1
Roast chicken   Vegetables          1kg/2lb
Cake            Butter              3 tablespoon
Cake            Cake mix            1 packet
Cake            Water               200ml