Ranking/Voting System question

[bpops]

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!

[HuggieBear]

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

[roopurt18]

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.

[bpops]

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