I've got a problem creating my database. I must create a table witch consists of let us say a Recipe. I've tables which contain let us say: Spices or herbs, Meat, Vegetables.

Okay now I have to get data in the Spices or herbs, Meat, Vegetables and make up a recipe within the Quality recipes table. My question is how you can map many of these in to the quality recipes thinking about that certain recipe might have more vegetables, more kinds of meet and much more spices or herbs.


Meat, veggies and spices or herbs are elements, IngredientType (M,V,S) can be used to recognize each group.

enter image description here

If for whatever reason you are feeling that every component needs its very own table simply because they have different posts, then make use of this model. Keep all common posts within the Ingredient table.

enter image description here

It appears in my experience you're speaking in regards to a n-m relationship :

  • a Recipe might have many Spices or herbs / Meat / Vegetables
  • Every one of Spices or herbs / Meat / Vegetables could be associated with many Recipe.

How you usually store this inside a database is by using a connection table between Recipe and, for instance, Vegetables.

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

Recipe and Vegetables must have a special primary key (id) and also the table RecipeVeggies store both primary secrets to produce a connection between your two.

Here is a little structure example :

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

The RecipeVeggies table create keep relation from a Recipe along with a Veggie.

You have to create similar tables for Spices or herbs and Meat.

I really hope I am obvious, otherwise you can request more questions and I'll improve my answer.

Here's what I'd do.

I'd use inheritance mapping, every ORM has it, and nearly every web language has it too (php has doctrine, java and .internet have Hibernate etc).

Because the example within this link shows, you've got a table towards the top of your hierarchy, for the system, I'd refer to it as Component, then have veggie/meat/spices or herbs as sub tables. I Quickly will make a table known as Recipe, also it might have a 1 to a lot of relationship using the component table.

I think you'll are utilizing an ORM with this,you will save a lot of some time and can lead to much less bugs.

Appreciate the interesting question, its nice getting a far more subjective design type question to obtain the creativity flowing.

You could do this it by doing this.. within the recipe table list lower the particulars from the recipe title (say x) that is repeated for each one of the content from the recipe item, then you may create another table using the recipe item title and number into it.. make use of this number within the original table.. in a nutshell normalize the recipe table.. it can be done further for each one of the component.

You've got a so known as n:m Relationship between quality recipes and elements. The typical approach to resolve this inside a relational database would be to introduce a mapping table.

Say all of your quality recipes comes with an id (RecipeID) and every component also offers an id (IngredientID). Then you definitely produce a new table which consists of both a RecipeID column as well as an IngredientID column. Each record within this table maps an component to some recipe and the other way around.

This type of table can also be handy if you wish to qualify the connection further. For instance you could include another column towards the mapping table that contains an account of what amount of the component could be required for the recipe.