Recipe database design advice

Question

I have a problem designing my database. I have to make a table witch contains let's say a Recipe. I have tables that contain let's say: Spices, Meats, Veggies.

Okay now I need to get data from the Spices, Meats, Veggies and create a recipe in the Recipes table. My question is how to map all of these into the recipes considering that one recipe can have more veggies, more types of meet and more spices.

Thanks.

Answer 1

Meat, vegetables and spices are ingredients, IngredientType (M,V,S) is used to identify each group.

If for some reason you feel that each ingredient needs its own table because they have different columns, then use this model. Keep all common columns in the Ingredient table.

Answer 2

It seems to me you are talking about a n-m relationship :

• a Recipe can have many Spices / Meats / Veggies
• Each one of Spices / Meats / Veggies can be tied to many Recipe.

The way you usually store this in a database is with an association table between Recipe and, for example, Veggies.

Recipe <----> RecipeVeggies <----> Veggies

Recipe and Veggies must each have an unique primary key (id) and the table RecipeVeggies store both primary keys to create an association between the two.

Here's a little structure example :

Recipe : id, name, description
Veggies : id, name
RecipeVeggies : recipe_id, veggies_id

The RecipeVeggies table create store the relation between a Recipe and a Veggie.

You must create similar tables for Spices and Meats.

I hope I'm clear, otherwise feel free to ask more questions and I'll improve my answer.