Sorry if this has been asked somewhere else, but so far I haven't found any questions like this, or can't find the words to search for.
TL;DR: What's the best way to implement counters in the database to track usage for billing purposes? Specifically when those counters will reach into the 10's or 100's of thousands?
I have a LAMP application that allows users to send out emails (Laravel, MySql). I want to start charging them for "email usage" and imposing monthly sending limits, which if they exceed, I will charge them extra, rounding to the next thousand.
So from a database standpoint, how can I make this happen in a way that is stable and maintains integrity, while not burning up all my CPU with updating counters? I also would like to record usage month by month for good record keeping. Here is what I'm considering:
accounts: account_id company_name email_limit (counter integer, example might be 20,000) email_usage (counter integer, example might be 13,425) ... users: user_id account_id email ... email_history account_id period (date field or something to know which month it was) email_usage (the historical usage they used that month) email_limit (the historical limit they had that month)
In this scenario, my system would process, let's say 5000 emails at once, and increment the
email_usage counter 5000 times. On the other hand, I could just use an internal counter ($i) and when the process is complete, just add $i to the usage counter. But what if there's an error during processing? I could lose my counter half way through.
Another way I could think to do this would be to save batch operations, so when we process 5000 emails at once, we just record that:
accounts: account_id company_name ... users: user_id account_id email ... email_batches account_id date (date batch was processed) emails_sent (integer, total emails in batch)
That way, when it comes time to process billing each month, we just
SUM(email_batches.emails_sent) WHERE MONTH(date) = [this_month] and process that.
We're going to be billing people based on this mythical usage tracking, so any advice from someone who's done it before would be appreciated.
UPDATE: My project's been put on hold for a short bit, but I will definitely mark an answer when I start working on it again (this is a high priority, so it won't be long).