Best method for storing a list of user IDs

Question

I'm working on a PHP/MySQL rating system right now. For the user to be able to rate the user has to log in. Each user has a unique "UID". There will be multiple rating instances on the website (one for every game, in my case) and I need an efficient way of storing a list of UIDs in a MySQL row (one MySQL row in the ratings table for each instance of the rating system) to keep a tally of who has voted.

I've seen in other systems that the list is stored in a serialized PHP array. Every time a user votes, then serialized array has to extracted, unserialized, the new UID is inserted, the array is re-serialized, and the MySQL row is UPDATEd. Every time the page is loaded, that list has to once again be unserialized and checked to see if the user viewing the page has voted yet to make sure the user doesn't vote twice.

This seems kind of inefficient and cumbersome. Does MySQL have a built in list function to help this process be more efficient? Are there any more clever ways I could fix this problem?

I've considered one possible alternative which is forget the serializing and store the UIDs in a TEXT type field in the MySQL database. I would just append some non-numeric character after each UID (let's say a period [.]). To add a user entry I would just concatenate the UID onto the end of the TEXT field and then a period. When checking if the user has already voted I could just "SELECT * FROM table WHERE votes = '%$UID.%';". Would this work more efficiently or is there a more elegant way of getting the job done?

Follow-up post about the table structure... Efficient MySQL table structure for rating system

Answer 1

In a normalized database, you should store it in a junction table:

UserRatings
-------------
RatingID int
UserID int
UserVote int

I don't know what are your queries. Depending on the application, this might not have the acceptable performance. However, in either case, I suggest you go with the normalized approach, benchmark, and denormalize only if appropriate.

Answer 2

It is inefficient. What you have here in relational terms is a many-to-many relationship between users and games. A user can vote on many games. A game can be voted on by many users. The solution for this is to have a join table:

USERS (uid, name, ...)
GAMES (gid, name, ...)
VOTES (id, uid, gid, ...)

Where uid and gid are foreign keys back to their respective tables.

If someone votes, insert a record in VOTES.

To get a list of votes for a game:

$get = mysql_query("SELECT * FROM votes WHERE gid = $game_id");
...

To get a list of the user's votes:

$get = mysql_query("SELECT * FROM votes WHERE uid = $user_id");
...

and so on.

Don't join an array and store it in a single column. You're right to avoid that.