1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Database Design For Storing User Votes

Discussion in 'Databases' started by Xangis, Aug 20, 2009.

  1. #1
    For a custom-coded website, I have forums that will let logged in users vote a post up or down, but not allow duplicates. I see a few different ways to handle the database design for this, and I could use a bit of insight about what you think would work best (or better ideas if you know something that might work better):
    SEMrush
    1. In the post record, create two fields, one storing the users that have upvoted and one storing the users that have downvoted. That way we know if someone has voted and which way they voted. In our program logic we'd have to do some parsing to check whether someone already voted, and it may not be very efficient.

    2. Just store the list of people who have already voted and don't worry about whether it was up or down because that will already be calculated and stored in a total field. This means that a user wouldn't be able to change their vote since we don't have the information we need to reverse the operation. It would also mean having to write parsing logic to check whether a user already voted.

    3. Create a separate table storing each vote, referencing the user and post tables. This will add another query and possibly get pretty huge if we have lots of active users voting on things, but would probably require the least amount of program code/logic to handle. In this case would it make sense to store the total in the forumpost object so we don't have to run count queries every time the post is displayed and only have to recalculate when a vote is cast?

    4. Some other method not mentioned?

    I'll be supporting thousands of users and the number of posts will probably hit tens of thousands at the very least (and votes maybe hundreds of thousands), so I'd like to do this in a sensible way from the start.
     
    Last edited: Aug 20, 2009
    Xangis, Aug 20, 2009 IP
    SEMrush
  2. premiumscripts

    premiumscripts Peon

    Messages:
    1,062
    Likes Received:
    46
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Why would the 3rd option create another query? Yes, it would be an insert (make the index unique).

    But the 1st option would probably be bad, you would need to store this as a text field, then have to use php to find out if the user has already voted, then append with mysql to that field, etc.. I can see that becoming the slowest method because of the large text fields. I would just go with option 3 and if the need arises, then you can look at other options.
     
    premiumscripts, Aug 20, 2009 IP
    Xangis likes this.
  3. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    Option 3 for sure.

    Set the table up like:

    votes:
    vote_id
    post_id
    user_id
    vote

    This table could get large as you suggested, but this would definitely be the proper way to handle it.

    You could also add a trigger to update a totals table every time a vote is cast or changed. This way you wouldn't have to query the votes table every time. When a user goes to vote, you would still check to make sure they haven't voted for that specific post yet.
     
    jestep, Aug 20, 2009 IP
    Xangis likes this.
  4. Xangis

    Xangis Active Member

    Messages:
    182
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    78
    #4
    Thank you for the good answers. The more I thought about it the more #1 and #2 sounded like a bad idea. #3 with an update trigger to recalculate totals on insert/update sounds like the magic bullet. :)
     
    Xangis, Aug 20, 2009 IP