Trouble with data modeling/normalization

Question

I have a kitchen recipe script I'm working on, and am having trouble accessing fields in joined tables. My tables are setup as follows:

ingredients
-----------
ingredient_id (pk)
ingredient_name (pk)
ingredient_calories (pk)

recipes
-------
recipe_id (pk)
recipe_name (pk)

meals
-----
meal_id (pk)
meal_name (pk)

recipe_has_ingredients
----------------------
recipe_id (fk)
ingredient_id (fk)

meal_has_recipes
----------------
meal_id (fk)
recipe_id (fk)

a recipe contains many ingredients, and a meal contains many recipes.

THE PROBLEM IS:

While I can SELECT SUM(ingredient_calories) FROM recipe_has_ingredients, I cannot figure out how to do this via the meal_has_recipes table (to calculate total calories in a meal).

Any help is greatly appreciated :)

Answer 1

You have over constrained the primary keys on the fact tables ingredients, meals and recipes. Just use the id field as the pk.

You can create a unique constraint on any name field.

The recipe has ingredient table should have recipe_id and ingredients_id as a compound primary key.

The meal has recipes table should have meal_id and recipe_id as a compound primary key.

The query for calories for a meal is now

 select meal_name, sum ( ingredients_calories)   from

    meals, meal_has_recipes, recipes_has_ingredients, recipes, ingredients

   where

      meal_name = ' good   food'   

      and   meal_has_recipes.meal_id =  meals.meal_id 

      and   meals_has_recipes.recipe_id = recipes.recipe_id

      and   recipes.recipe_id = recipes_has_ingredients.recipe_id

      and    ingredients.ingredients_id = recipes_has_ingredients.ingredients_id;