The most efficient method of storing voting data in a MySQL database (e.g. StackOverflow)

Question

I am trying to replicate the SE voting system. A user on my site should be able to vote only once, and then they are blocked. I currently have two tables: users and posts .

How can I store information on which posts the user voted on? I was thinking of a column in posts that stores the uid users who voted for it. Another idea would be to have a column in users with the id posts he voted on.

How can I do it? I want to take into account scalability and it is easy to determine if voting really takes place.

Answer 1

Create another table to store information from the users and posts tables:

 CREATE TABLE votes ( user_id INT , post_id INT , PRIMARY KEY (user_id, post_id) ); 

With this approach:

I thought of having a column in the posts that would store the uids of the users who voted for it. Another idea would have columns for users with the identifiers of the posts he voted on.

If you don’t save the values ​​as separation values ​​(to fit in one cell) or JSON, you will get many rows in just one message. But then this is a bad approach to begin with.

Stick to creating a new table containing the relationships that define the "vote". The table is simple enough to check:

 SELECT COUNT(t1.post_id) AS vote_count FROM votes AS t1 WHERE t1.user_id = SOME_INTEGER AND t1.post_id = SOME_INTEGER 

Answer 2

Best practice, for something the size of a stackoverflow, is to keep individual voices in a separate table. But also keep the number of votes received in the field directly attached to the message. As the size of the database grows, it becomes prohibitively expensive to summarize all the votes each time you view a message.

Saving this derived field is relatively simple using triggers in this user / vote table.