In my project I use mysql, I encountered a problem while designing a certain data structure. Approximately how things are:
For example, there is a table articles, let's say it has the fields id, author_id, title, text, icons. (Here icons is an integer that contains the resource id about which I will tell further)
There is a table users, for example with the fields id, login, avatar. (Here the avatar field is an integer that contains the resource id)
There is a table of comments with the fields id, article_id, author_id, date, text
There is a table of resources, which contains the fields id, type_id, files. (Here, type_id is an integer that contains an id of the type about which further, and files contains a json string, an array with the names of pictures / files)
There is a types table, which contains the id, params, and other fields (params here, this is the json string, which contains various resource parameters, for example, it can be the address to the directory in which the pictures (resources) correspond to this type, and other parameters)
Further on the problem. For example, the user goes to the post page. First, we need to get a row from the articles table with the required id, right there, we want to display data about the author of the post, for this we need to join the users table to take the data corresponding to the author_id field in the articles table. But, the user has an avatar (field avatar which contains the id of the resource). Now we need to join the resources table, here we get a list of files in this pack (files field) and the type id of this resource (type_id) in which the data on the resource lies. The next step is to retrieve the data type (we need to display a picture on the page, and the type contains data about where this picture and other parameters lie), and this is another join.
So we printed the post, but we still have comments that have the author's id, and the author has an avatar, the avatar has a resource with the appropriate type, and this again is a lot of joins.
As you can see, it turns out such a tough picture, with a large number of complex queries. The task is to simplify everything as much as possible.
PS is very important! Here I gave the structure on the example of articles for an accessible understanding of the problem, in fact the project is very cumbersome, because of this, such a mountain of partitions into types, resources and so on. options. And about the possible advice on caching, yes, I understand that you need to cache as much as possible, but I have more interest in improving this structure. I think that many of you have experience in large / high-load projects, solving such situations, so I really hope for your advice.
