I'm am making a system where a user can vote up or down on a product, I need to be able to explicitly work out the amount of ups and downs a product has, as well as a total score for a recent period.
Each vote can optionally have a comment with it, and users need the ability to echo/boost other peoples comments (kinda like a retweet), and this will also add/subtract the total score of the product depending on the parent vote being retweeted.
Here are my current proposed tables:
Product
ID, name, category_id
Vote
ID, user_id, product_id, parent_id, comment, score, datetime
User
ID, username etc.
I am thinking I will possibly need a comments table to do this effectively? The votes' score field is either 1 or -1 as per some advice I read on StackOverflow which would allow me to gather the SUM()
of that column to calculate total votes, another possibility would be to have separate vote_up and vote_down tables...but I am just not sure.