Recipe database design advice

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.

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 / Veggiescan 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, descriptionVeggies : id, nameRecipeVeggies : 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.

Here is what I would do.

I would use inheritance mapping, every ORM has it, and almost every web language has it as well (php has doctrine, java and .net have Hibernate etc).

As the example in this link shows, you have a table at the top of your hierarchy, for your system, I would call it Ingredient, then have veggie/meat/spices as sub tables. Then I would make a table called Recipe, and it would have a one to many relationship with the ingredient table.

I hope you are using an ORM for this,it will save you a TON of time and will result in a lot less bugs.

Thank you for the interesting question, its nice having a more subjective design type question to get the creative juices flowing.