mysql - 如何在配方表中添加自我引用以允许子配方？

我想要一个包含食物成分列表以及子配方列表的表格食谱，例如：

#recipe yellow lemon juice
Ingredient         unit cost  quantity
1.Yellow lemon       Kg    1.7  1
2.--suppose it has ingredient 2
3.--suppose it has ingredient 3

#recipe mayonnaise
Ingredient                  unit cost  quantity
1.olive oil                 Lt  $2.5    1
2.egg                       Kg  $2.5    0.12
3.mustard                   Kg  $1.59   0.01
4.salt                      Kg  $.9     0.01
5.RECIPE yellow lemon juice Lt  $1.9   1 ------------this one is a recipe

我怎么能说蛋黄酱食谱使用黄色柠檬汁食谱？
我有以下内容，但不知道如何在配方表中允许子食谱

现在可能是这样，我有3个食谱A,B,C，而食谱A有几种成分，但也包括食谱B和C

here is fiddle

Create table recipe
(
    recipe_id int primary key,
    recipe_name varchar(30)
);
Create table ingredient
(
    ingredient_id int primary key,
    ingredient_name varchar(30),
    ingredient_cost float
);
Create table food_ingredient
(
    fk_recipe int not null,
    fk_ingredient int not null,
    measure float,
    unit_of_measurement varchar(30)
);
INSERT INTO ingredient VALUES
(1,'olive oil',2.5),
(2,'egg',2.5),
(3,'mustard',1.59),
(4,'salt',0.9),
(5,'lemon ingredient 1',1.9),
(6,'lemon ingredient 2',1.9),
(7,'lemon ingredient 3',1.9);


INSERT INTO recipe VALUES
(100,'Recipe lemon juice'),
(200,'Recipe MAYONNAIse');

INSERT INTO food_ingredient VALUES
(100,5,1.0,'Lt'),
(100,6,1.0,'Lt'),
(100,7,1.0,'Lt'),
(200,1,0.2,'Lt'),
(200,2,0.5,'Kg'),
(200,3,0.5,'Kg'),
(200,4,0.2,'Kg'),
(200,5,1,'Lt');

最佳答案

我可能会添加：

   Create table dressing
(
    dressing_id int,
    recipe_id int,
    constraint pk_dressing primary key (dressing_id, recipe_id),
    constraint fk_recipe foreign key (recipe_id) references recipe(recipe_id),
    constraint fk_dressing foreign key (dressing_id) references recipe(recipe_id)
);

我还要在蛋黄酱中加入一些糖和醋... :)

关于mysql - 如何在配方表中添加自我引用以允许子配方？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50826685/