Deduct food recipe amounts from the main Inventory table?

I have worked on scenario somewhat similar to this

Surely there must be a Table which will store recipes for Each Item
1.Lets Says in Inventory Table stores Raw Material

2.Menu table stores the End product like Salad

3.and there should be another table will store Items needed for each menu with quantity (using foreign key over above two tables)

if you have this structure, your recipes can be flexible. and every time you have to use inner join query to find out how much stock you have consumed

________________________________________________________________
Let’s Say Inventory Table have Primary Key PKInvid.
And Menu table (end Product Salad) have Primary Key PKMenuId
The third table will have structure like this

CREATE TABLE [dbo].[Recipe]
(
[PKRecipeid] [int] IDENTITY(1,1) NOT NULL, -- Primary Key
[FK_InvID] INT, -- Foreign Key refering to Inventory Table (raw material)
[FK_MenuID] INT, -- Foreign Key Refering to Menu table
[Qty] Float , -- Inventory Quntity needed

)

This table can hold information like for
FK_MenuID Salad, FK_InvID of water with Qty as 20
FK_MenuID Salad, FK_InvID of Oil with Qty as 50
FK_MenuID Salad, FK_InvID of water with Qty as 500

Now there is order for two salads, you can use query like below
Declare @Order INT -– for 2 orders
DECLARE @Menu INT – for Salads

Finally you have to store this information in table variable and write a while loop to update each quantity row by row

Declare @tab AS TABLE
(
TID Identity(1,1)
FK_MenuID INT,
QTY Float
)
INSERT INTO @Tab
Select FK_InvID, Qty * @Order FROM Recipe where FK_MenuID = @MenuID
DECLARE @RowCOunt INT,@i INT =1
SET @RowCOunt =(select Count(*) form @tab)

WHILE @i < @RowCOunt
BEGIN
SET @QTY = (SELECT QTY FROM @tab WHERE TID = @i)
SET @FK_MenuID = (SELECT FK_MenuID FROM @tab WHERE TID = @i)
UPDATE Invenotry SET QTY =QTY - @QTY where MenuID = @FK_MenuID
SET @i=@i+1
END