Database design for a product voting system

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开发者_如何学Python 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.

Depending on what you want to do, this can be an incredibly sophisticated problem, but here's my take on the simplest way (eg. what i can throw together in the 10 min before I leave work ;-P)

I would try the StackOverflow/HotOrNot style approach, and Store their ranking as an unsigned integer.

PRODUCTS(
id, 
category_id, 
name, 
rating INTEGER UNSIGNED NOT NULL DEFAULT 0
);

Then in your 'VOTES' table, you store the Vote (up/down). I think the table you have for your 'VOTES' table looks fine( although I would use either an enumeration as the SCORE datatype, or some strategy to ensure that a vote can't be manipulated via XSS. eg. someone modifies the vote so that their vote up is +10,000 instead of +1, then that would not be cool )

For a small fun app, you can probably get by with incrementing or decrementing the count when the user clicks, but if you are doing anything with aspirations of scaling out, then you would do the vote calculation and ranking via some batch process that runs every 10-15 minutes.

Also at this level, you would start using an algorithm to weight the vote values. For example, if the same user votes (up or down) the same product more than once a day(or once every) then the votes after the first should not count towards calculating the rank of the product.

For Example, here is how Quora's Ranking Algorithm works

If the user is a "Power User" or has an account that is more active, maybe their vote is more important than a new users vote. I think on Yelp, if you don't have more than one or two reviews, your rating and reviews don't get counted until you meet some minimum number of reviews. Really, the skies the limit.

PS. I would also recommend checking out this o'Reilly book on some of the strategies for solving these kinds of problems

If you expect a large number of users simultaneously voting, you really need to consider performance....

A naive approach might look something like this (my apologies if i oversimplify your example, and if T-SQL isn't your poison):

create table Products(
ProductId BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Score     INT NOT NULL...
ProductDetails...

where you will be performing updates to Products by summing up/down vote tables. BAD!

If you have a large number of users voting, deadlocks are sure to occur by constantly inserting/updating/selecting against the same table.

A better approach would be to drop the Score column altogether, only insert into the up/down vote tables, and select as needed. There's no reason you can't calculate the sum in code (i.e. PHP, C#, or whatever), and it avoids ever having to update the Products table (at least for calculating the Score). In other words, storing the Score on the Products table buys you nothing, and is just unnecessary overhead.

I speak from experience when I say updates "can" be bad in high volume systems. Updating is expensive when compared to inserting at the end or selects (assuming your table is properly indexed), and it's very easy to unknowingly take out a substantial lock in situations like this.

Book Beginning CakePHP has an Ajax tutorial on implementing a working voting (up/down) system on comments. I did the tutorial a few years ago. I am not sure how secure it is or if it would be a good foundation for your project, but it would probably be worth having a look at for some ideas.