Database design for storing food recipes

Question

I want to make a database of recipes that I like, but I'm having trouble designing it. I want to have at least two tables:

1. Recipe table (Has description, ingredients, directions, etc...)
2. Ingredients table (ingredient type, other attributes, etc...)

What would be a way to associate a the two tables together? Would I need a third table that would store the several relationships from a recipe to multiple ingredients?

As you can probably tell, I'm relatively new to this stuff, just trying to figure out the right way to do it on the first try.

Thanks!

Answer 1

Here is a link to a pretty advanced one:

http://www.databaseanswers.org/data_models/recipes/index.htm

but if you really want to code it yourself I would go with a third relational table.

cheers, Mike

Answer 2

Does one recipe have many ingredients or many recipe's have many ingredients

I'd expect it will be the latter to allow you to find recipe's by ingredient.

So you will need an intermediate table to break the many to many relationship into two one to many relationships.

Recipe(RecipeID, etc...) 
Ingredients(IngredientID, etc....)
RecipeIngredients(RecipeID, IngredientID, etc..)

Then in RecipeIngredients I would put information such as quantities of that ingredient for that recipe.

Answer 3

You could consider something like a folder of text files, with a full-text index of some sort.

Or a table like Recipes(ID, LikeRatio, Description as text). Again with a full-text search.

Theorically you could use a normalized model like the one @Mike suggested. But looking at actual recipes, it needs to be more flexible. For example in this model, there's no evidence of the use at some step of the outcome of a previous step.