The most efficient method storing voting data in a MySQL database (like StackOverflow)

Question

I'm trying to replicate SE's voting system. User's on my website should only be able to vote on posts one time, then they are locked in. I currently have two tables, users and posts.

How should I store information on which posts a user has voted on? I was thinking of having a column in posts which would store the uids of users which have voted on it. Another idea would have a column in users with the ids of the posts he/she has voted on.

How should I do this? I want to take scalability into account and easy of determining whether or not the vote is valid.

score 4 · Answer 1 · answered Dec 23 '11 at 02:57

4

Best practice, for something the size of stackoverflow, is to store the individual votes in a separate table. But also keep a derived vote count in a field directly attached to the post. As database sizes grow, it'll become prohibitively expensive to sum up all the votes on every viewing of a post.

Keeping this derived field is relatively easy by use of triggers on this user/votes table.

answered Dec 23 '11 at 02:57

Marc B

356,200
43
426
500

I'm currently storing the total votes in a field within the `post` table. Thanks for the tip thought, it'll definitely help someone else who stumbles on this question. – Dec 23 '11 at 03:26

Nonym · Accepted Answer · 2011-12-23T03:10:42.860

4

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 was thinking of having a column in posts which would store the uids of users which have voted on it. Another idea would have a column in users with the ids of the posts he/she has voted on.

Unless you store the values as delimited values (to fit in one cell) or JSON, you'll end up with many rows for just one post. But then, that's a bad approach to start with.

Stick with creating a new table which contains the relationship determining "voting". 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

edited Dec 23 '11 at 03:10

answered Dec 23 '11 at 03:04

Nonym

6,199
1
25
21

So does this mean that the `votes` table has several entries for each post? Like `29, user1`, `29, user2`, `28, user1`, `29, user3`? Where the numbers are post `id`'s and the rest is the username? – Dec 23 '11 at 03:25
Yeah, that's how it'll work --is this not favorable to you? If so, could you tell us why, in case there's another approach to use?.. I just wanna reiterate (although knowing that you just mentioned an example) that it would be one thing to consider --> to work using the primary keys from your `users` and `posts` table as the columns in `votes`. – Nonym Dec 23 '11 at 03:43
Yeah that works perfectly, thanks. I just wanted to make sure I completely understood :) – Dec 23 '11 at 03:48

The most efficient method storing voting data in a MySQL database (like StackOverflow)

2 Answers2