User's possibilities on site

Question

I want to build a system on the website, that allows users to do some things depend on their rating. For example I have rule for rating value X:

• 1 post in 3 days
• 10 comments in 1 day
• 20 votes in 2 days

for rating value Y, rule may be following:

• 3 post in 1 day
• 50 comments in 1 day
• 30 votes in 1 day

Each night I recalculate users' ratings, so I know what each user is able to do. Possibilities don't sum or reset on each rating's recalculation.

One more important thing is that admin can fill concrete user's possibilities at any time.

What is optimal database (MySQL) structure for desired?

I can count what concrete user has done:

SELECT COUNT(*) FROM posts WHERE UserID=XXX AND DateOfPost >= 'YYY'
SELECT COUNT(*) FROM comments WHERE UserID=XXX AND CommentOfPost >= 'YYY'

But how can I do admin filling possibilities in this case?

Answer 1

I would log the number of actions of each user each day and use that table to compare.

This table would contain the following fields:

• date: the day when the action took place
• count: the number of actions took that day
• userId: who did this action
• action: which action post/comment/vote/...
• ignore: boolean, if this is set, admin has reset the values

Checking a rule: SELECT SUM(count) FROM log WHERE userId = XXX AND action = YYY AND ignore = 0 AND DATEDIFF(date, NOW()) <= DAYS

Resetting a rule: UPDATE ignore = 1 FROM log WHERE userId = XXX

If his rating changes the result is still valid (you'll just compare with on other total)

When you create a rules table:

• action
• limits
• days
• rating_min
• rating_max

You can query for permissions like this:

SELECT action, IF(SUM(count) < MIN(limits), 1, 0) as can_do_action FROM log LEFT JOIN rules ON rules.action = log.action WHERE userId = XXX AND rating_min <= RATING AND rating_max >= RATING AND ignore = 0 AND DATEDIFF(date, NOW()) <= days

So you get a table loggin like this: - comment => 1 - votes => 0

You do have to update this table every action (create a new row if first action of the day or update the count of the row)

The absence of a rule means no actions have been made, so we can ignore it.