For example, a user likes a video. The video has tags for example: United States, war, Syria.

MySQL has a table of labels

id | tag 1 | USA 2 | Siria 3 | war

After the user likes the video, it’s necessary that the tags stick to the user and then add to it, if the user likes another video with the same tags. That is, he liked the video tagged with the United States. And in the database you need to put a user with id 1 fell in love with usa tag 1 time, if he likes it again, then he has fallen in love with this tag 2 times.

How to implement it, what tables need to be done?

  • The next question will be the hike - "How to display a list of videos with a label USA that the user with id 1 loved" ----- If you do not know how to create a table in the database with the required fields, how do you plan to create queries to the database to several tables at once? - Palmervan
  • why like tags? - Pavel Dur

1 answer 1

First approach . We make a user_tags table with user_id and tag_id . Every time a user likes a video, write the tags to the table:

  insert into user_tags (user_id, tag_id) values (user, tag);

When you need to count the number of times a user likes a particular tag, we make this request:

  select count(user_id) form user_tags where tag_id = 1;

Plus: easy insertion. Cons: a rapidly growing table and the need to use a statistical function for each sample.

The second approach . We make the user_tags table with the user_id , tag_id and quantity fields. The first two are made with a composite key. When recording, check if there is an entry in the database with such a key. If there is, then we do the update , increasing by 1 the number in quantity . If not, insert it by inserting in quantity 1.

Pros: a more compact table, simple search and selection. Less: more complex insert logic.