Create new records in a child table upon entering a new record in a parent table
I am a chef and trying to develop a system to allow the costing of a menu item. I have a table "MenuItems" that is linked to a table "Ingredients". There are many ingredients in each menu item. Works fine.
I have a table"CostCard" that is linked to "MenuItems". The "CostCard" table contains info on how many portions of a menu item are ordered on a given day. One "CostCard" record for a individual day.
I have a join table "CostCardLineItems" that is linked to "CostCard" and "MenuItems". I would like to upon establishment of a new record in "CostCard" ,which is linked to "MenuItem", have a new record added to the "CostCardLineItems" table for each Ingredient, from the "Ingredients" table ( all menu items will have varying numbers of ingredients) .
The result would be that the "CostCardLineItems" table would have a record for each Ingredient that was needed for a given "CostCard" Menu Item. I can then do math to determine the correct volume needed of each Ingredient given the number of portions of a menu item that is needed on any given day. This will change with each day "CostCard".
Thanks for the help