How should I go about creating a point system for users like SO and yahoo answers?(PHP)

I am creating a voting system for a Q&A site project in which if a user asks a question, he/she losses -5 points; answer a question 开发者_如何学Go+5, vote a question +1, etc. (kind of like SO and yahoo answers)

-->To create the basic arithmetic, I have a "users_points" table that relates the user_id and their total points.

+---+---------+
| 1 | 100     |
+---+---------+
| 2 | 54      |
+---+---------+

-->Basically if the users does certain task, it would + or - the points. How do I prevent users from say voting an answer up 100 times. ex: I want a user to be only able to vote once per question, etc.

You should create a votes table that has the columns: user_id, question_id, delta. Delta is the value of the vote, which should be 1 or -1 (this way you can just do a SUM(delta) to find a question's point value).

To get the uniqueness, create a unique index on (user_id, question_id).

See if this design helps you out any

You can denormalize in all the appropriate places (for ex. remove the VoteType and put a boolean in the appropriate places)

I would keep a detailed log of what user voted on what. So you might have a table with these columns:

• user_id (the user ID)
• item_type (either "question" or "answer" - use an ENUM or a foreign key to an item types table)
• item_id (the ID of the actual item, e.g. a question ID or answer ID)
• up_down (ENUM or BOOLEAN representing whether it was an upvote or downvote)

To calculate the user's "rep", your app logic would run through the votes and add +5 if the question was voted up, -1 if the answer was voted down and so on.

You would probably store aggregate values in the users/questions/answers tables for easy access. But the big advantage of keeping the log in the background is you can recalculate rep regularly and you haven't lost anything. You can also tell which users voted which things on the fly (e.g. SO shows which answers you upvoted in orange when you go back to a question later).

Or just use Stack Exchange, it would be easier ;)