creating a restaurant recipe database to calculate nutritional values

Raghav Srivastava
Raghav Srivastava used Ask the Experts™
on
Hi,

I am attempting to create a nutritional value calculator for a restaurant's recipes.  So far I have one table with the list of menu items and their ingredients, and another table with the ingredients and their individual nutritional values. How do I create a table that will list the recipe item names and their TOTAL nutritional values. Now keep in mind that the number of ingredients vary from item to item.

Thanks,

Rags
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorLinux/LXD/WordPress/Hosting Savant
Distinguished Expert 2018

Commented:
You may find doing a real time sum of nutritional values will make for cleaner code.

If you calculate this value + save it in a table, this means somehow you'd have to write logic to track, all ingredients + anytime an ingredient's nutritional value changed, you'd have to recalculate all these values + save them.

If you have many recipes, you may find the load for doing a lookup + recalculation + re-writing data may be a worse performance drain, then just calculating this data on the fly, when a person does a lookup for one recipe.

Author

Commented:
I have no problem with live calculations. However, my question at this juncture is a lot more basic. I am totally lost on how to even link the two tables together. I have these two tables and I am unable to join them. Then once they are joined, how do I sum the values?

Commented:
This is a example of a query that would give you the total nutritional value for a given menu item.
I'm assuming table names (you did not provided them) and field names.

SELECT m.name, sum(i.nutritional_value) Total_nutritional_value
FROM menu_items m, ingredients i
WHERE m.ingredientID = i.ingredientID AND m.name LIKE 'some_name'
GROUP BY m.name;
Distinguished Expert 2017

Commented:
This problem requires three tables, not two.  One table is ingredients and their nutritional values.  The second table is RecipeNames.  The third table is what is called a junction table and it is used to implement the many-many relationship between ingredients and recipes.  Ingredients can be used in many recipes and recipes can contain many ingredients.  The junction table contains  contains a FK to the recipe table and a FK to the ingredients table and should also include a quantity.  

Here's a start.
Recipes.zip
Colleen Kayter4D Assets

Commented:
PatHartman mentioned including the quantity in the ingredients table, but what are the quantities upon which the nutritional values based? e.g., if the ingredients table lists mayonnaise and x values per tablespoon... Now, you have a recipe that calls for 4 tablespoons of mayonnaise and makes 10 servings, so the equation would be mayo x 4 / 10 to get the per serving value. That's easy enough, right?

But you will also need to make sure that nutritional values are based on the same measurements as the recipe. e.g., ingredient table lists sugar with x calories per teaspoon (4.93 ml) and recipe is for large quantity, calling for sugar measured in liters, grams, or kg (like 1 lb of sugar). So now you must convert quantities before divvying up the total values by the number of servings.

Assuming this is for one restaurant and a menu that doesn't change frequently, you might find it easier to use this website. In the My ND section, you can input recipes and get very detailed nutritional values. http://nutritiondata.self.com/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial