Hey, I have a page where users can vote up or down other user's comments (like digg and reddit...).

I want to keep track of the comments a user has voted on. The comments all have a unique id.

So for example, if you are user_id #100 and you vote up comments #: 12,2,1,14,27,23 I need to store this info in mysql.

Now which is better, a table with repeat entries:

user_id | comment_id
100 12
100 2
100 1
100 14
100 27
100 23

Or, to do this:

user_id | comment_id
100 12,2,1,14,27,23

With the second method, to see if the user has voted for a specific comment I explode it into an array and search through it.

Method 1 would take more space on the webserver, but method 2 would be more for the script to process.

Which is better?

Thanks

    
user_id | comment_id
100 12
100 2
100 1
100 14
100 27
100 23

    this is better

      With the second method, to see if the user has voted for a specific comment I explode it into an array and search through it.
      ...
      Which is better?

      You are using a relational database, not a CSV file, so there is no question here: do not use multi-valued fields. Use your first method.

      Method 1 would take more space on the webserver, but method 2 would be more for the script to process.

      Considering that these are integral values, the "additional" space consumption is minimal. What is more important is that if you use multi-valued fields, you cripple the power of your relational database. When you want to insert/delete/update, you are on your own since you have to manipulate the multi-valued field yourself. Instead of being able to do normal aggregate queries like COUNT, you would have to retrieve the data and process it yourself. When you want to search, you have to use wildcard operators.

      Consequently, you might as well stick with a CSV file. It may even save space, if that is your concern.

        Write a Reply...