Structuring a recipe database

Question

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.

Answer 1

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'