Items belong in two tables - how should I normalize this?

[Big_Rog]

I'm working on an app that is like a cookbook, where there are recipes and ingredients. Both are modular and some recipes can be listed as ingredients in other recipes to save space, e.g. pie crust has a recipe listing, but will also be listed as an ingredient in apple pie.  The two tables are many-to-many: linked with a join table.  What is the preferred method for normalizing these kinds of "fits in two tables" problems?

 

My initial thought is to add a boolean value to the recipes table for "is_ingredient" and doing a lookup of both tables when listing/searching for ingredients, and likewise with "is_recipe" in the ingredients table.  Is there a better solution for this?  Thanks in advance.

[fenway]

I've used type_uid and table_type in tandem before... sometimes you can cheat with specific ranges.

[Big_Rog]

Thanks for the prompt reply!

 

Pardon my n00bness but that's greek to me.  I searched the manual for mySQL on the sun site and didn't find anything meaningful for table_type, and  Google only showed foreign language hits for type_uid.  I've only been tinkering with php and mysql for about 2 weeks, and i'm still at the crawl phase (e.g. I can add to and view table data for single tables, and that's about it.)  The data itself doesn't need to reside in both tables, but for display purposes I want some of the recipes to be nested inside other recipes.  Would it be better to use two SELECT queries and merge them into a single array?  I'm not sure how to go about that either, just throwing that out as a possibility for fetching the data without lots of extra calls to the DB.  The current plan is to have the items listed on both sides of the join table, i.e. one side where it is a recipe, the other side where it is an ingredient.  Is that bad normalization, and if so, what problems would that cause?

[aschk]

I think you should be fine with an ingredients and recipes tables. I think about it this way, a recipe is a set of instructions that require ingredients, and ingredient is a single (smallest demoninator) item.

 

I don't believe that a "pie crust" (for example) is an ingredient. It is in fact another recipe, so ideally want you require is a mapping between recipes and other recipes. So, in the case of a pie you have a set of ingredients listed (basic components), and also a reference to related recipes (pie crust).

 

I forsee the tables being

 

recipes, recipe_ingredients, ingredients

recipe_recipes

 

This is just my speculation after about 5 minutes thinking.

[Big_Rog]

I was thinking about how to go about pulling the data up for display, and it seems that if I do a recipe_recipes table I can just do two queries (one for recipes, one for ingredients) and nest them in loops to populate the top level recipe.

 

e.g.

$recipe_query="SELECT * FROM recipes WHERE la=dida";
$recipe_recipes_query="SELECT * FROM recipe_recipes WHERE foo=bar";
$recipe_ingredient_query="SELECT * FROM recipe_ingredient WHERE elvis=prestley";

for each distinct recipe in $recipe_query {
[list top level recipe]
for each recipe in $recipe_recipes_query {
  [add item to html buffer]
}
for each ingredient in $recipe_ingredient_query {
  [add item to html buffer]
}
...
}

 

Does that sound workable?  And if so, the fun part will be sorting out the actual queries--as noted above, I'm still in PHP and mySQL diapers =)  Adding the data to the table should be interesting as well: I suppose some sort of switch/boolean should be used to determine if I'm adding a recipe to a recipe or just an ingredient before sending the data off to the DB.