AnsweredAssumed Answered

Best way to combine data from three different ingredient tables into a master ingredient table

Question asked by skibitzky on Sep 21, 2017
Latest reply on Jan 15, 2018 by jbrown

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?

 

Thanks

Outcomes