SQL数据库多对多或一对多

Question

我是MVC和编码的新手，所以如果我问了一个听起来很愚蠢的问题，请原谅我。我正在尝试创建一个包含食谱和配料的数据库。我在试着弄清楚我该如何建立我的模型。我知道我需要至少三个或四个表通过外键连接。我遇到的困难是在创建配方时，我如何选择这些表中的所有成分?我首先习惯了概念模型。

我在网上看过很多关于一对一关系的解释。一对多或多对多，但出于某种原因，我不知道如何用三张表中的所有食材同时创建一个食谱。需要帮助吗?

Answer 1

这是如何设置多对多表的一个很好的例子。在这个例子中，我们将包括Toast, Roast Chicken和Cake作为食谱。

(公平的警告:我不是厨师。不过我知道怎么做吐司!)

您可以在此数据库<>提琴

我们先从基本的两个表开始——菜谱和配料。

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');

在Recipes中，我们只有食谱名。在components中，我们只包含可以包含的成分。

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');

那是我们的两张桌子。

第三个是链接它们的多对多表——例如，菜谱"Toast"需要"Slice of bread"one_answers"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

在上面的例子中，Recipe_ID 1 (Toast)有2个成分_id 1 (Slice of bread)和1汤匙成分_2 (Butter)。

然后我们可以对烤鸡做同样的事情-加入一只整鸡，1kg/2lbs蔬菜和1汤匙黄油!

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

然后甜点，一些蛋糕。

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

要显示菜谱和配料，可以使用多对多表将两个表(菜谱和配料)连接在一起。

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;

结果如下

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