Post rating system

Question

Django

Good afternoon. I'm trying to write my own small text message in janga, please advise how best to implement the post rating system on the site. Interested in the side of storing the rating on the site. What is the best way to create a model(s) that will store records of voting posts. I scratched my head and thought about this: when creating a post, add the following parameters to the POST-RATING branch:

---------------------------------------------------------------------------------
ID_ПОСТА   |   ID_АВТОРА   |   ВСЕГО_ПРОГОЛОСОВАЛО   |   ОБЩАЯ_СУММА_РЕЙТИНГА   |
---------------------------------------------------------------------------------
   253     |        4      |            0            |             0            |
---------------------------------------------------------------------------------

when the user clicks on ±/view the result, it adds +1 to the TOTAL_VOTE column (if you didn’t look at the result) and +1 or -1 to the TOTAL_SUM column, it also writes to another branch of the database:

----------------------------------------------------------------------------------
ID_ПОЛЬЗОВАТЕЛЯ   |   ID_ПОСТА   |   ПЛЮС   |   МИНУС   |   ПОСМОТРЕЛ_РЕЗУЛЬТАТ   |
----------------------------------------------------------------------------------
          5       |     253      |     1    |      0    |           0             |
----------------------------------------------------------------------------------

this database stores an individual for each user and post so that the user cannot vote again ... on the one hand, it seems to work functionally, but on the other hand, as I imagine how many requests to the database will come from such a heap ... maybe someone has a better one solution to this issue?

Answer 1

I would advise you to store the rating in two stages. Let's say you can vote once a day for any number of posts. Then you need two tables:
1. A table with daily data, and the corresponding model (DayVote).
.---------------------------------------------------------------- ------------------------------------
| USER_ID | POST_ID | EVALUATION | LOOKED_RESULT |
-------------------------------------------------- ----------------------------------------------------.
2. Table with aggregated data and its corresponding model (PostVote):
.----------------------
| POST_ID | EVALUATION |
-----------------------------.
This plate should be filled automatically by crown once a day, collecting and aggregating data from DayVote, after which DayVote needs to be cleared.
Advantages of this approach:
1. The maximum number of records in the tables will be equal to the number of posts + the number of voters for today. And this is much less than the total number of votes.
2. The simplicity of the data scheme.
3. You can extend DayVote with service data (such as IP, USER_AGENT, etc.), and in the script that updates PostVote, you can automatically analyze and filter votes without fear that the base will grow.
Disadvantages:
1. The process of choosing the number of votes for a post becomes more complicated (it now needs to be selected from two tables).
2. It is necessary to log the script cron launches, and monitor the logs so that the aggregation runs smoothly.

Answer 2

In the second option, make 1 field, RATING for example, you will store either 1 or -1 there.
Then index on ID_POST + SQL SUM on this field. As a result, everything should work out quickly, if the server is straining, then you can try to cache or make another table into which you will write and write the data to the main one (from which you show the data) with a trigger every N time.

Answer 3

It is better not to reinvent the wheel, but to try to find ready-made solutions, the creators of which have already been ill with all childhood diseases.