Link to home
Start Free TrialLog in
Avatar of Raghav Srivastava
Raghav Srivastava

asked on

creating a restaurant recipe database to calculate nutritional values

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
Avatar of David Favor
David Favor
Flag of United States of America image

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.
Avatar of Raghav Srivastava
Raghav Srivastava

ASKER

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?
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;
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
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/
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.