db Design Question

[ryan.od]

Ok, I have a situation where I want users of a site to be able to vote on submitted product. They are only allowed to vote, however, if they haven't already voted on any particular product (no voting multiple times!). What is the best way to track which users have voted on which products? I have tossed the idea around in my head several times and I keep coming back to needing an array in my table to track product numbers users have voted on. I doubt very highly that is the correct solution.

 

Any help would be greatly appreciated. It is quite difficult to search for help on this since 'array' returns answers to questions about db queries.

 

RyanOD

[bubblegum.anarchy]

A table with a user id and product id

 

vote.user_id

vote.product_id

 

To verify if the user has voted on any product:

SELECT * FROM vote WHERE user_id = $user_id

 

To get a total of votes for a product:

SELECT count(*) FROM vote WHERE product_id = $product_id

OR

SELECT product_id, count(*) AS votes FROM vote GROUP BY product_id ORDER BY votes DESC # most votes first

[Illusion]

I suggest, apart from user_id, product_id you create another column flag which set default to 0.If user voted for that product set flag to 1. Adding flag will helpful to do other sorting of data.

[fenway]

I suggest, apart from user_id, product_id you create another column flag which set default to 0.If user voted for that product set flag to 1. Adding flag will helpful to do other sorting of data.

How so?

[cmgmyr]

You should do something like:

 

users:

userid

username

password

...

 

products:

productid

name

description

...

 

votes:

vid

userid

productid

vote

...

 

That should give you a good start.