User features on the site

Question

I want to create a system on a website that allows users to do some things depending on their ranking. For example, I have a rule for an X rating value:

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

for an evaluation value of Y, the rule may be as follows:

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

Every night I recount the user ratings, so I know what every user can do. Opportunities are not cumulative or reset at each recount.

Another important thing: the administrator can fill in specific user features at any time.

What is the optimal database structure (MySQL) for what you want?

I can calculate what a specific user has done:

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

?

Answer 1

.

:

• date: , .
• count:
• userId:
• action: ///...
• : boolean, , admin reset

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

: UPDATE ignore = 1 FROM log WHERE userId = XXX

, ( )

:

• rating_min
• rating_max

:

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

, : - = > 1 - vote = > 0

( , )

, , .

Answer 2

, remainingPosts, remainingComments remainingVotes? , , "" , .

===

, reset . , // .

Answer 3

, , 1 10 . / 5 . , , 10 ?

, , reset/ignored, ?

: 1 5 . "reset", . , , , ,

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

-

SELECT changes FROM adminTable WHERE UserID=XXX AND  type = 'post'

count - , .

Answer 4

:

• /

, "--", :

user_features(
   user_id
  ,feature_id
  ,source      (admin|earned)
  ,primary key(user_id, feature_id)
);

/ .

/ , / .

If you go with this approach, you can actually give functions either on the basis of rating or specific actions.

A rule such as “3 posts in 3 days” can be implemented as follows:

when a user posts, check if the previous post was made within 24 hours.
if yes then
   increment counter by 1
   record current timestamp

   if counter = 3 then
      grant feature to user
else 
   reset counter to 1 
   record current timestamp

You will need two columns (post_count: int, last_post: date) in some table with the user_id key.