Structuring a Recipe Database

Question

I am working on a database that will look for recipes for ingredients.

For example, I think I plan to populate the database with the types of ingredients that are accepted, but I don’t want to parse a line that includes all the ingredients in a specific recipe. I was thinking of doing just like a list of acceptable ingredient tables, and finding this to see if it exists or not. I feel that it will be very taxable, though, and I want it to be as efficient as possible.

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

If someone is looking for recipes with butter, mushrooms and spinach, I want him to return the recipe with any of these ingredients.

We look forward to some suggestions on this subject.

Answer 1

It is about as simple as relational databases ...

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 - Request Example

"all recipes with 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'