The goal is to build a vegetarian recipe exchange site similar to allrecipes.com. For this portion of the project I'm trying to implement a feature that would calculate a nutritional profile for a user-submitted recipe, based on its ingredients.
See working example at calorie count
As the source of nutritional data I am using a freely available DB from the UDSA (US Dept. of Agriculture). The original data source was in MS Access.
This needed to be done for the data import:
- understand the tables and their relationships in the original data source
- identify the portion of the data that is relevant for this project (e.g. not all tables and fields were imported and I left out all non-vegetarian foods)
- export the data a CSV files
- create rails compliant models to reflect the data
- import the data from CSV into the prepared rails tables
There are four tables that are used to look up nutritional information for an ingredient:
- Foods: contains basically different names for a food item
- Nutrients: has two foreign keys - (food_id) and (nutrient_definition_id) - and contains the value for different nutrients (e.g. calories, protein, carbohydrates) in 100g of the food item
- Nutrient Definitions: contains the name of the nutrient (e.g. Kcal, Protein) and the common unit (g, mg, kcal, etc.)
- Weights: has a foreign key (food_id) and maps different commonly used household measures of the food item (e.g. one cup of butter, one tbsp of butter) to its weight in g
A recipe has a simple one-to-many relationship to ingredients. In order to calculate a nutritional profile, all the ingredients with their respective values will have to be taken into account.
Ingredients should get flagged after creation/update depending on whether they have been found in the lookup tables:
Red flag: ingredient not found in foods table
Yellow flag: ingredient found, but not with the measurement indicated (e.g. 1 "clove" of flour)
Green flag: ingredient found and measurement found. Ready for nutritional analysis.
If ingredient gets green flag, entries will be made into the ingredient_profiles table with the amount of nutrient (e.g. Kcal, Protein, Carbs) for the particular ingredient. After all ingredients have been processed this way, an entry will be made into the recipe_profiles table with aggregated values for each nutrient over all ingredients. This would conclude the nutritional analysis of the recipe.