Nolongerused3921 Posted January 6, 2007 Share Posted January 6, 2007 I could really use some input on a method of storing, and getting votes per content on my site...Right now I have several different content types in separate tables, such as "articles", "news", "gallery", and so on... Each has an auto-incremental field called 'id', and my class (Module) to call these forward has standardized each table so they're all pretty much the same.What would be the best way to allow voting with the ability to edit a user's vote? I have been discussing this with a friend of mine over the past hour or so, and we think we've come to a solution thats best overall, but I'm not so sure... Designs:[list][*]#1: When a user adds a vote, add their vote number (1-5) as well as +1, to the content type's table in total_score and total_votes, then get the average each time you last the page. (Can't edit votes this way)[*]#2: Add votes to a separate table and calculate the total in a query like this: [code=php:0]SELECT news.*, SUM (votes.votenum) AS votes.total, COUNT( votes.*) AS votes.count FROM news AS news, votes AS votes WHERE votes.parent_id = news.id[/code][*]#3: Add votes to a separate table, but calculate the total of ALL votes for that content type / parent_id and add it to the content type's table in a "score" column[/list]As some of you probably have guessed, we've come to the conclusion that #3 is best... But it has its own problems, such as the fact it could potentially take a great deal of CPU/RAM to get, then calculate an article/content's new score on each vote.Are there any other ways to do this, or is what I've listed the best... I don't want to end up having to constantly upgrade my server as my site gets larger (I will have to no matter what, but if I setup something like a voting system on this large of a scale wrong, it'll be sooner rather than later every time), so your opinions would be nice. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 9, 2007 Share Posted January 9, 2007 You could keep your votes and content tables completely separate and simply do something like SELECT AVG(votes) as average, news.* FROM votes WHERE votes.id = news.id (maybe wrong syntax)This would of course execute every time you loaded the page, but it's a simple query and if it came to the worst you could make another table that stores the results for each article and then recalculated if the time column of that table was < time() - $seconds.Someone might have a better suggestion, but that is how I would do it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.