Storing a complex structure in mysql. How?

Question

PHP

Greetings.
There is a project dedicated to cinema, written in php, js, I store the data in mysql.

Now a service is being written that will allow you to ask for advice - “what movie should I watch?”.

Actually, the logic of the service is as follows:
the user writes a wish, selects from the list of films that he likes and which of them he wants to see the recommended film look like.
Question: how to store it in the database in the best way?
Now it looks like this:
a table with user comments, a table with rows of similar films (the IDs of films are stored - the service has its own database of films for viewing)

The answer is as follows: the
user writes a comment to his answer and recommends a couple of films

The main question is: how to store?

I don’t really want to make 4 tables (request comment, similar request movies, response comment, response movies) for several reasons:
too many queries in the database will be to get a list of view requests (request, movies, who advises what)
too many extra gestures will be in the answer

Thank you.

Screenshots for better perception of information:

Answer 1

Do as you know. Maybe it will come down. Because joins are evil, and premature optimization is the root of all evil :)

Answer 2

I don’t really want to make 4 tables (request comment, similar request movies, response comment, response movies) for several reasons:
there will be too many queries in the database to get a list of view requests

Nonsense, here everything can be obtained in one request. And not too slow.
In general, you have a strange wording. Properly store as it is more convenient to get. But how to get it you need to write only briefly. Therefore, while storing in mysql in a normalized way, 4 tables is correct. If (more precisely, when) it will be necessary to accelerate - denormalize, only in the direction in which you need, but it is not yet clear where.

Answer 3

I don’t really want to make 4 tables (request comment, similar request movies, response comment, response movies) for several reasons:
too many queries in the database will be to get a list of view requests (request, movies, who advises what)
too many extra gestures will be in response

What does too much mean? Compared to what?
In general, 4 tables - this will be a normalized way to store your data. You can also denormalize, but this will worsen the extensibility and ability to manipulate data using SQL tools. Sometimes denormalization is acceptable, you just need to be fully aware of what the consequences will be.
For example, you can make two tables: Comments and CommentMovies. In Comments, add the IsQuestion field, in which to store TRUE if this is a question and FALSE if this is an answer. CommentMovies will store movies for both questions and answers.
The disadvantage of this approach will be the problems of functional extensibility. That is, if you want to add a question-only field to the Comments table, you basically have three options:
1) Structure refactoring: Splitting into your original 4 tables. It's labor intensive;
2) Adding another table: QuestionCommentFields. The structure is clumsy;
3) Creating a field that will store values ​​for only one type of row. This is already denormalization.
You can store movies as a string in the main table, separated by commas. This is denormalization, it will simplify the structure, but it will complicate and slow down the change and search for data.
In general, highlight for yourself some priorities and guiding stones. Will the structure change frequently in the future, is ease of maintenance important? Whether speed of performance of requests is important, whether possibility to change the data directly SQL'im is important? Etc.

Answer 4

Maybe look towards noSQL, such as mongoDB or redis. Mongo has a nice map/reduce.
You can also try postgreSQL - it's very good with table structures, there are arrays and their own types - it's quite easy to make relationships between tables at the native level.

Answer 5

Request and response can be stored in the same table. Lists of films, of them - too. We get two tables instead of four.
Is it okay?

Answer 6

NOSql solutions are not considered?

Answer 7

First, write in PHP in which way you want to work with data (hierarchy of objects, nesting of arrays, etc.), figure out how you will expand the “fields” if necessary, so as not to break the whole code), and only then think about how to display it on DB. If only because the same relationships between PHP entities can be implemented in the database in different ways, for example, inheritance (the “question” and “answer” are inherited from the abstract “comment”) is displayed in at least three ways, and creating them based on the database can simply not notice that there is some kind of convenient and understandable relationship.
Abstracting from the database schema, you can encapsulate working with it in functions / methods of the type$answers = get_answers($question_id)or $answers = Answer.getByQuestion($question) and bind any database schema in them, or not a schema or a database at all. Change the scheme - you will need to change only these implementations, without touching the main logic, in it you will still refer to the movie name as $questions[$question_id]['answers'][0]['films'][0]['name '] or $questions[$question_id].answers[0].films depending on whether you prefer to store entities in arrays or objects.

Answer 8

Everything has already been thought of before us. There are connection schemes many_to_many, one_to_many, many_to_one. By combining them, you can easily implement the circuit. Most systems for working with a database (I mean AcriteRecord and ORM components) can do this.
By the way, 4 queries each looking at 1 row while searching is better than one query looking at 10000.

Answer 9

Try 1 table for comments with a flag meaning "request/response" and one table for "attached" movies... as an option for "think".