rating....

[Anon]

I have a site with pictures and reviews. I wanna do the sort of thing where users can rate the images on a scale of one to 5, and that sort of thing. My question is what is the best way of making sure people don't vote repeatedly?

A) Should I build a database that keeps track of who voted for each image? I would then have to have a table for each image??? This seems rather large and clunky. Better way of setting up this DB?

😎 Should I use cookies and then just write to the cookie for each user which ones he's voted for?

C) Should I make users register, and keep track of what images they've voted for, but this brings up the same complications as A.

Maybe links to working with databases so that I can understand the best way to set up these databases.

[Anon]

C) is the only option that has much hope of meaningful accuracy. Look at it in reverse: if you don't require individuals to register, what exactly is it you are measuring?

[amcgrath]

OK, I'll assume you have an image table, and a vote table. A row in the vote table represents a single vote cast with the score that was given. (Figuring out multiple votes is easy, use cookies, user_ids, etc.)

table: image
image_id int4

name vc

table: image_votes
image_id int4 fk to image
score int4

your query is:

select sum(v.score) as total_votes,
count(v.score) as votes_cast,
i.name from image_votes v, image i
where i.image_id = v.image_id
group by i.name;

Better yet, just create a view to the data:
create view as
select sum(v.score) as total_votes,
count(v.score) as votes_cast,
i.name from image_votes v, image i
where i.image_id = v.image_id
group by i.name;