user2805004 user2805004 - 25 days ago 7
SQL Question

Implementing voting or "likes" using MySQL

I am creating a system where users (who are identified by a user id number) will be allowed to vote on posts (think Reddit, StackOverflow, etc).

Users can vote a post up or not vote at all on it.

The number of votes on a given post can easily be stored within the table containing the posts.

Keeping track of who has voted, however, is a different task entirely that I'm not sure how to approach.

I was thinking I could have a table that would have two columns: user id and post id.

When they vote on a post, I add their user id and post id to that table. If they unvote, I remove that entry from the table.

EG:

User ID | Post ID
1 | 3949
1 | 4093
2 | 3949
etc...


Is this a reasonable solution?

Answer Source

Yes this is reasonably simple and easy solution to the problem. You can do the same for your comments(if you like to). In your MAIN_POST table assign a post_id and use this same post_id in other tables (comments(post_id, user_id, post_comment, comment_time) and votes(post_id, user_id, vote_status(you can use 1 for vote up and 0 for vote down))). It will complicate your sql queries, to retrieve data, a little but you can do it. And on android side there are alot of tricks to handle and furnish this data in application and you can make this vote(like) and comments idea just like facebook (YOU for your comments and likes and NAMES for others).