Managing user points on Q&A site

Question

I am making a question and answers site kind of like Yahoo Answers. It is going to have a points system where users can vote on answers, but I don't know if I should remove points that a user earned on a topic if it is deleted (and add points back that were removed if they got any down votes). This leads onto my main question: what would be the best way to do this?

I made a points table and whenever a user earns points it is logged in the points table and then it is added/subtracted from the total on the users table.

But, if a question is deleted how would I manage to subtract the points that were earned (and add points back that were lost with down votes)?

The structure of my points table is (I just did a SHOW CREATE TABLE points query):

CREATE TABLE `points` (
 `pid` int(11) NOT NULL AUTO_INCREMENT,
 `point_user` int(11) NOT NULL,
 `question_id` int(11) NOT NULL,
 `timestamp` int(25) NOT NULL,
 PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And the users table:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `uid` int(11) NOT NULL,
 `fname` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `lname` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `username` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `confirm_key` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
 `user_pic` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
 `user_title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
 `moderator` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'false',
 `deleted` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
 `suspended` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no',
 `points_count` int(11) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I just don't know if it's a good idea to remove/give back the points gained/lost on a question if it gets deleted.

I know that Stack Overflow does this but they have a much more advanced system.

Answer 1

I believe that the user should still be rewarded for the time spent sending the post. Also a user should still be "punished" if your user group thinks that the answer deserves to be down-voted. Remember a point system in answer/question sites are a determining factor in such sites.

Having said that if you still intend to return/take the points to/from the users that earned the points on the deleted post this is the way to do it in SQL

SELECT u.user_id, SUM(//points awarded)
FROM points p
JOIN users u ON p.point_user = u.user_id
Group By p.point_user

Answer 2

If you want to remove points when a question is deleted, one way I can think of to do this is to use a database trigger.

You can have a trigger fire when a DELETE statement is executed on the points table, and then update the users table with the appropriate recalculation, ensuring that the total column in the users table is correct.

For the question on whether or not it is "logical" to modify a user's points if a question is deleted, that is your preference, as you're the one implementing the logic of the site. :)

Answer 3

I would make the return points in case of a deletion.

On your users I would recommend two changes:

`moderator` tinyint(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 0,
`deleted` tinyint(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 0,
`suspended` tinyint(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 0,

I think tinyint is better than varchar in this case.

And I would also add on your points table the id of the user that +1/-1 the question so you can track the user when you need to give the points back.

Answer 4

My thinking is that just because of an administrative change, the person that took the time to answer the question still earned it...just my opinion - I could be wrong.