Ratings Question

[atomicnuke]

I've asked around on how to go about making a ratings system. I've been told to use two different methogds and would like to know peoples thoughts at which one would be more efficient, mainly loading pages and DB strain. It is a rating system for registered users, who can only vote once.

1. Create a table with the id of the item being rated, an array for the id of the voter, array for the votes and an incremented times rated. I realize this method would cause the page to load slower.

2. Create two tables. One with the id of the item and voter. The second table stores the id of the item, voter and what they rated. Since it isn't an array there might be 500 entries for one item, opposed to the array method, which would just need one entry.

So which would be a better method of the two or is there a better method than those?

[atomicnuke]

Anyone have any good ideas/comments or a resource for this?

[Element]

or just create one table with an id, 3 columns for options, and 3 columns for the votes. The first method you metioned may be easier MySQL wise if you have three columns 'id', 'option_arr', 'vote_arr' then split up the options and votes by '|' and when you fetch that row, explode by '|' to get the array. Not sure if you can direction submit a PHP array into a database. However with this option it allows you to easily create multiple options without more columns. Basically unlimited options.

[arnyinc]

I would have one table (called "items") that has an ID number and the item being rated. Then in another table (called "ratings") I would have ID, item_id, and user_id fields. When a user rates an item, their rating is inserted in the "ratings" table (associated with the user_id and item_id). If they try to rate that specific item again, you do a select and see if they have submitted a rating yet.

[atomicnuke]

or just create one table with an id, 3 columns for options, and 3 columns for the votes. The first method you metioned may be easier MySQL wise if you have three columns 'id', 'option_arr', 'vote_arr' then split up the options and votes by '|' and when you fetch that row, explode by '|' to get the array. Not sure if you can direction submit a PHP array into a database. However with this option it allows you to easily create multiple options without more columns. Basically unlimited options.

How much will the arrays effect parsing page time? IF I have 500+ votes, that going to slow it down majorly?

I would have one table (called "items") that has an ID number and the item being rated. Then in another table (called "ratings") I would have ID, item_id, and user_id fields. When a user rates an item, their rating is inserted in the "ratings" table (associated with the user_id and item_id). If they try to rate that specific item again, you do a select and see if they have submitted a rating yet.

Not sure if I understand your method, is that like my second example? Do is the rating an array or you doing new rows for each vote?

[Element]

I don't think it would slow it down, because its a solid number of 500+ votes as a string, not 1, 1, 1, 1.

[ralph l mayo]

Here's my take, it might be the same as what you meant above:

(code tag to preserve whitespace)

item
id
ratings # total number of ratings, used to calculate the avg
rating # current running avg
ratings
itemid # id above
value # maybe not needed unless you want to be able to recalculate from scratch or get specific votes in admin
userid


On vote:

SELECT value FROM ratings WHERE itemid=thisitemid AND userid=thisuserid
If no results
{
item/rating = (rating * ratings + new rating) / ratings + 1 # Maybe, anyway.. have to test that :]
item/ratings++
ratings/itemid = id of item
ratings/value = new rating
ratings/userid = err.. uid.
}


However you do it, don't do it like Element says. You don't want the database to have to search through potentially hundreds or thousands of records, each with potentially hundreds of thousands of terms in a huge|***|delimited|text|area using LIKE just to figure out if a user rated a thread or not. (This is an every page cost if you want to show/hide the rate button!)

My method will cause many more entries, but it will be a simple and much faster integer comparison.

[ralph l mayo]

I don't think it would slow it down, because its a solid number of 500+ votes as a string, not 1, 1, 1, 1.

String parsing is relatively expensive, and your method requires fuzzy matching, also expensive.

[atomicnuke]

Okay so see if I get what you are saying, have two tables like so:

Item
id
userid - id of user being voted
voterid - id of voter

Ratings
id - not unique, matches id above
votes - autoincrement
average - keeps total score to get average

So if I'm creating 100's of entries eachtime some one votes, this obviously greats a bigger DB entry wise as opposed to the other method. But it'll still be faster? Loading time is something I don't want to sacrifice obviously. If I did it with arrays I'd only need one array, for voters. But then I'd have to parse it and check, then calculate. Instead of just checking a table for two id's.

[ralph l mayo]

Item

id
userid - id of user being voted
voterid - id of voter

Ratings

id - not unique, matches id above
votes - autoincrement
average - keeps total score to get average


That doesn't really match. Item has no field for userid, ratings does. There's no voterid.

Ratings doesn't have the number of votes, item does. Same with cumulative rating. To review:

item
itemid
totalratings # total number of ratings, used to calculate the avg
avgrating # current running avg
ratings
itemid # id above
value # maybe not needed unless you want to be able to recalculate from scratch or get specific votes in admin
userid



So if I'm creating 100's of entries eachtime some one votes, this obviously greats a bigger DB entry wise as opposed to the other method.


You aren't. Each vote is one new entry in the ratings table, an increment to the total ratings column in the item table, and the addition of the new value to the average rating column. There will be more entries but they will be INT, hence much faster to search and much smaller in memory. When threads are archived all ratings of that id can be deleted, and the rating table should never grow too large.

[atomicnuke]

But I need a voterid to stop them from voting more than once.

[ralph l mayo]

How is voterid any different from userid? userids are unique, right?

edit: I guess if you're allowing users to vote on other users that makes sense, just swap it for itemid.

[atomicnuke]

So...

item
itemid - samething as userid
totalratings # total number of ratings, used to calculate the avg
avgrating # current running avg
ratings
itemid # id above
value # maybe not needed unless you want to be able to recalculate from scratch or get specific votes in admin
voterid


What I meant with the 100's of entries comment is... Everytime a new user votes, there is a new row in the ratings table, right? So if one item is voted for 500 times, that is 500 entries in the table, not to mention any votes other images get voted on. SO the item table should only contain as many records as there are users, but ratings will grow with votes. Will there be a limit of entries that a table can have? I guess that is my only concernm because your method seems more efficient.

[ralph l mayo]

You can create some tables with both methods and hundreds of votes and time a bunch of looped queries to be sure.

[atomicnuke]

But the array method would drag down page loading with more and more votes right? Does a table pretty much have an infinite amount of entry room, atleast more than most sites would need?