Keeping track of web application usage in an SQL Database (Need Suggestions)

Question

I have a system that will allow users to send messages to thier clients. I want to limit the user to x amount of messages per day/week/month (depending on subscription). What is a good way to tally for the time period and reset the tally once the time period restarts? I would like to incorporate some sort of SQL job to reset the tally.

Answer 1

table limits (id, client_id, limit_days, msg_count);

Assume limits count from midnight, Sunday, and 1st of the month.

New clients get three records, one each for limit_days of 1, 7, and 31.

Cron job to run at midnight resets msg_count = DayLimit where limit_days = 1; msg_count = WeekLimit where limit_days = 7 and weekday(current_date) = 1 msg_count = MOnthLimit where limit_days = 31 and day(current_date) = 1

Sending message is allowed if min(msg_count) > 0 where client_id = '$client_id' then send msg and update limits set msg_count = msg_count - 1 where client_id = '$client_id'

Answer 2

Each time you send a set of messages, log the date and the number of messages sent. Your application can then sum the message count fields, grouping by either the day, the week of year, or the year of the date to enforce limits. The where clause used to limit the user to a specific number would use a message limit, start date, and stop date from the user profile table, or some global settings table.

In MySql dialect, you would write something like this:

select 
  users.id, 
  (users.msg_limit - subq.msgs_used) as msgs_available
from users
inner join (select
              sum(msg_log.cnt) as msgs_used
              from msg_log
              where weekofyear(msg_log.date) = weekofyear(now())
                and msg_log.user = :user_id_param) as subq;