I am looking for advice on how to deal with the following situation. I am creating a database for a restaurant, with the following tables
TABLE - BASE INGREDIENTS – important fields = name and cost per gram
all the basic ingredients (say pepper, flour, salt, paprika, chicken, sugar, oil…) that might be used in menu items
TABLE - SECONDARY INGREDIENTS – important fields = name and cost per gram
new ingredients crafted from a combination of BASE INGREDIENTS (such as a spice rub made from 2 grams pepper, 1 gram salt and 3 grams paprika)
TABLE – TERTIARY INGREDIENTS – important fields = name and cost per gram
new ingredients crafted from a combination of BASE and SECONDARY INGREDIENTS (such as a sauce made from the 8 grams spice rub from the SECONDARY table and 3 grams oil from the BASE table)
We then need to access ALL ingredients from BASE, SECONDARY and TERTIARY INGREDIENT tables to craft the final recipes for the menu items. So I need to access a full list of ingredients and their cost per gram from all three tables in a drop-down ingredient menu for all menu items (BASIC, SECONDARY AND TERTIARY). My thought is to combine all three INGREDIENT tables into a MASTER INGREDIENT TABLE that captures the ingredient name and cost per gram, but I am unsure how to do that and have it update automatically whenever a new ingredient is added to any of the three ingredient tables. Any advice on how to do this, or alternate approaches?