views:

57

answers:

1

I'm working on building a database that will search for recipes by ingredients.

For example, I think I plan on populating the database with types of ingredients that are accepted, but I don't want to have to parse the string which includes all the ingredients in a particular recipe. I was thinking of making just like an list of acceptable ingredients table and searching through that somehow to see if it exists or not. I feel like this will be a very taxing operating though, and I want this to be as efficient as possible.

What is the best way to structure something like this? I have a couple of ideas, but they just seem so inefficient.

If someone searches for recipes with butter, mushrooms, and spinach, I want it to return a recipe with any of those ingredients in it.

Looking forward to hearing some suggestions on this.

+2  A: 

This is about as easy as relational databases get...

Table One - Ingredients

[ID]  [Name]  [Description?]
 1     butter   delicious cow output
 2     bread    wholegrain please

Table Two - Recipe Basic Data

[ID]  [RecipeTitle]  [RecipeAuthor]  [RecipeSteps] (maybe as BLOB text?)
 1     Happy Toast    Andrew          butter on bread, then toast bread, etc.    

Table Three - Recipe Needs (many-to-many)

[RecipeID]  [IngredientID]
 1            1               (toast needs butter)
 1            2               (toast needs bread)

That should get you started.

EDIT - sample query

"all recipes using butter"

SELECT r.name FROM recipeNeeds n
    LEFT JOIN tableRecipes r
        ON r.ID=n.recipeID
    LEFT JOIN tableIngredients i
        ON i.ID=n.ingredientID
    WHERE i.name='butter'
Andrew Heath
So each time I want to add a new recipe, I'll have to go through and make sure to relate each recipeID to the ingredientID manually? Is this the optimal way to structure this? After awhile, it seems table 3 is going to be pretty ugly and unable to read. This wouldn't really work for substitutions of ingredients, thinking forward would it? Thanks for the response.
Parker
Andrew Heath
And yes, that's the optimal way to structure it. The database doesn't see ugly, the database sees pure one-step relationships that are lightning fast to browse and perform calculations with. :-)
Andrew Heath
To give you an actual example, I have a site that catalogs over 1,200 battles and the military units involved in each. So I've got a many-to-many table with only two columns - battleID -)
Andrew Heath
Ok thank you so much. I'm going to play around with this. Yeah, I'm a PHP noob, although I'm in a database systems class which at least allows me to understand what you're talking about :D Thanks for the help.
Parker