Jump to content

Archived

This topic is now archived and is closed to further replies.

bpops

Ranking/Voting System question

Recommended Posts

Hello,

I'm working on voting system for a site of mine. I already have a member database in place where people can post comments and such. But I want them to be able to rank certain items (a la amazon, five stars or whatever). But I was wondering the most space-efficient and time-efficient way of doing this was? I want members ONLY to be able to vote. But not more than once on any particular item. So do I just make a new mysql table with fields like:
user_name, user_id, item_id, rank

and then every time I load that item, i have to query for the ranks with that id, and then average them?
Or is there a better way to do this? Any ideas or suggestions would be helpful. thanks!

Share this post


Link to post
Share on other sites
Sounds like a reasonable idea.  No need for a user_name column though  ;D

Then you could use the avg() function to get the result.

Regards
Rich

Share this post


Link to post
Share on other sites
Assuming that each row of your users table has an id field and that each row of your items table has an id field, I'd create a table:

[b]ratings[/b]
id int auto_increment non null primary key
UserID int non null
ItemID int non null
Rating tinyint
UNIQUE(UserID, ItemID)

I'm not sure that tinyint would be appropriate, but if you're storing values 1 - 5 you certainly don't need a regular size int.

The UNIQUE constraint is important IMO because it will prevent your application from inserting duplicate rows.  Thus if you try to insert for a user and the query fails, you'll know to try and update instead, assuming you want people to edit their previous ratings.

Share this post


Link to post
Share on other sites
Awesome, thanks for the quick responses, guys :)
I'll be able to get to work on this tonight!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.