How to compose correct mysql model?

Question

PHP

I'm writing an engine for a site with recipes... So I thought about the logical model of Mysql.
There is a recipe, there is a block in it, for example:
Main ingredients (apple, beets, potatoes, etc.) well, the recipe itself
I see it like this:
Create a table with recipes (where the recipe itself)
Table of main ingredients (id, ingredient name , calories) Linking
table of ingredients and recipe (recipe id, ingredient id)
Will it be correct? If I want to add a few more such blocks like: Kitchen geography, cooking method, etc.
And for each of these blocks I will create a table with options and a link table
Or maybe there is a more acceptable way?

Answer 1

habrahabr.ru/post/254773
Based on the description, you correctly split the entities into 3 tables.
You don't have to create an intermediate table if the recipe belongs to the same geographic cuisine (one-to-one relationship). When you add the Kitchen Geography table, then simply match the id from this table to the field in the recipe table (geo_region_id)
. If you implement a one-to-many relationship, then you cannot do without an intermediate table.

Answer 2

Will it be right?

Yes, this is a common many-to-many relationship .

Answer 3

IMHO pull the ingredients from the tables - get tired of filling out the recipe. It is certainly correct from an academic point of view, but from a real point of view, it will probably give a profit only in the fact that the ingredients will be called the same everywhere. But if you plan to give the user a recipe - how to start a new ingredient? And if you give it to the user - as a result of 100% you will get 20 mayonnaise options.

Answer 4

In my opinion, there is a table for communication superfluous. Just add a field, for example, ingredients, and all the ingredients from the ingredients table, separated by commas, into it.
if there are more "blocks" then just a new field.
The downside here is that if you want to search for recipes by ingredients, it will take longer than with a link table, and the plus is that it's easier to find the ingredients themselves.
It is possible to make a "global" link table, where the ad, ID for the link, the source table and the table with which it is linked will be stored