How to map recipe within recipe in SQL

Question

Looking for a solution to build a database for recipe generation. A number of ingredients are used to create recipe and further a recipe can also contain a recipe plus can also have some ingredients.

For example: recipe R1 needs three products i.e I1, I2, I3.

But recipe R2 needs recipe R1 and in addition to it some other ingredients, maybe I4, I5, I6

So

R1 = I1 + I2 + I3 

whereas

R2 = R1 + I4 + I5 + I6

To accomplish this, I have an Ingredient table like below:

Id name
--------    
1  I1
2  I2
3  I3
4  I4
5  I5
6  I6

Now I need maping where I can map Recipe with ingredients like

Recipe table

Id Name
------------   
1  Recipe1
2  Recipe2

Recipe FormulationTable

Id   RecipeId   IngredientId
-----------------------------
1    1           1
2    1           2
3    1           3
4    2           4
5    2           5
6    2           6
7    2           

but here I can not add any ingredient as recipe 2 needs 4,5,6 and recipe 2

How can I map these tables so that I may fetch any kind of information at my dashboard

Like->

• Recipe 1 has three ingredients
• but Recipe 2 has other three ingredients and also it contains receipe1

Answer 1

I would suggest you to create another table Recipe_Sub to handle the sub ingredients. So that table RecipeFormulation only store Recipe-Ingredients mapping.

table Recipe_Sub

table RecipeFormulation

then create a view to join all data.

CREATE VIEW [dbo].[vRecipeFormulationFull] AS
    select rs.RecipeId, rf.IngredientId
    from Recipe_Sub rs
    inner join RecipeFormulation rf on rs.RecipeId_Main = rf.RecipeId

    union all

    select RecipeId, IngredientId
    from RecipeFormulation
GO

result:

select *
from vRecipeFormulationFull vrff
inner join Recipe r on vrff.RecipeId = r.Id
inner join Ingredient i on vrff.IngredientId = i.Id
order by r.Name, i.Name