Hi All -
I'm a long time lurker/reader but I've run into an issue now that I know should be easy but can't wrap my head around.
I've got a tables of ingredients and their components for a recipe. For example, ingredient A has 2 components (20% water and 80% flour) and ingredient B has 2 components (50% salt and 50% fat). Now I want to create a recipe that uses those 2 ingredients and builds a complex ingredient list.
Using the same items above, let's say there is 20% of ingredient A and 80% of ingredient B in my recipe. What I would like to generate is a list of components which should come to (again using the values above) 16% water, 4% flour, 40% salt, and 40% fat.
The relationship between ingredients and components is a direct one, as is the one for recipe, ingredient, and component. All are related directly using their IDs. When loading the final components on a portal on a recipe I can list them all out, see their ingredient breakdowns (for example 20% and 80% for ingredient A) but I cannot multiply successfully given their use in the recipe.
I am attaching a picture of my relationship graph, I use a "lot" table to connect to the ingredients because I store expiration dates and other information that that I pull when I make the recipe.
Any help is appreciated.