Netflix-like Registered User Voting

[skyer2000]

I'm in the planning stages of a website and I'm planning of having a rating system similar to Netflix with different objects on the website. However, I'm pretty much stumped as to what would be the best way to setup the MySQL database for it.

 

Here is the rundown of what I'm trying to do:

-A list of 20 items is on the page, if the logged in user has yet to vote for an item, it gives a option to do so.

-If the user has voted for an item, it shows the vote they previously made

 

What I can't figure out is the best way to store the fact that the user made a vote for one of the objects. Would I make a column in each individual object's row that will store a huge list of every user that has voted for it, and when the page loads, checks that list to see if the user has voted for it?

 

Or do I make a column in the user's row that keeps track of every object they have voted for, then checks the page everytime it loads to see if they have voted for it.

 

Also, in that specific column, would it be setup like -> user1, user2, user3, user55, user67, then the page checks through that list and sees if it matches with the user logged in?

 

Or am I totally missing the ball here on the best way to do this efficiently?

[Jessica]

Third table:

user

item

vote

 

 

[skyer2000]

Would that scatter the data a lot though instead of keeping it relatively close to the specific user/image?

 

Or is MySQL going to be able to handle checking through the huge third table pretty efficiently? (hundreds of objects to vote on with hundreds of users)

[Jessica]

As long as you mark the fields user and item indexes, it won't be "checking", you'd just select * where user = $user.

There are probably other solutions, but it seems more normalized than a big field of comma separated items.

[skyer2000]

Sounds like a really good solution then. Thanks!