Recipe Database Design Guidelines

Question

I have a problem creating my database. I have to make a table that contains the recipe. I have tables that contain let say: Spices, Meats, Veggies.

Now I need to get data from Spices, Meats, Veggies and create a recipe in the Recipes table. My question is how to compare all these recipes, given that one recipe can have more vegetables, more types of appointments and more spices.

Thanks.

Answer 1

To identify each group, meat, vegetables and spices are used , IngredientType (M, V, S) ingredients .

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

Answer 2

I think you are talking about nm relationships:

• Recipe May Have Many Spices / Meat / Veggies
• Each of the spices / meat / vegetables can be tied to many recipes.

As you usually store this in a database, there is a table of associations between the recipe and, for example, Veggies.

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

The recipe and Veggies must have a unique primary key (id), and the RecipeVeggies table stores both primary keys to create a relationship between them.

Here is an example of a small structure:

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

The RecipeVeggies table creates a repository for the relationship between Recipe and Veggie.

You should create similar tables for Spices and Meats.

I hope that I understand, otherwise you can ask more questions and I will improve my answer.

Answer 3

Here is what I will do.

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

As the link shows in the example, you have a table at the top of your hierarchy, for your system I would call it Ingredient, then have vegetarian / meat / spices as subcategories. Then I would make a table called Recipe, and she would have a one-to-many ratio with the table of ingredients.

Hope you use ORM for this, it will save you from TON time and lead to much smaller errors.

Thanks for the interesting question, its a nice question with a more subjective design to get creative juices.

Answer 4

You could do it this way .. in the list of recipe tables, enter the name of the recipe (say x), which is repeated for each of the contents of the recipe element, then you can create another table with the recipe element name and number for it .. use this number in original table .. to normalize the recipe table briefly .. you can do this further for each ingredient.

Answer 5

You have the so-called n:m relationship between recipes and ingredients. The usual method to solve this problem in a relational database is to enter a mapping table.

Say that each of your recipes has an identifier ( RecipeID ), and each ingredient also has an identifier ( IngredientID ). Then you create a new table that contains the RecipeID column and the IngredientID column. Each entry in this table displays the recipe ingredient and vice versa.

Such a table is also convenient if you want to further qualify the relationship. For example, you can add another column to the mapping table that describes how much of the ingredient is needed for the recipe.