I’m creating a web site that deals with monetary transactions. Users can deposit funds into a wallet and use them on the site. I’d like to keep track of some stats for the web site, like revenue and balance.
I already have a database table for tracking transactions, but I’d like to also store a summary of all transactions as mentioned above.
If I store these summary figures in my database, I think the table would always have only one row:
stats
revenue
balance
depositCount
... (etc)
transactions <-- already exists
id
transactionType
userId (optional)
amount
balance
createdAt
1
It seems to me that if you wanted to store that information in a table, you’d have to update that table ever time a transaction occurred. Or update at some regular interval.
If you used a view for that purpose, you could just query the view, and the view would do all the calculations for you.
Since it sounds like your system’s volume is heavy enough to cripple an aggregation query even the best-indexed table (and I assume that you’d test this once or twice, just to see), having a table with the aggregate data might be better than constantly querying the source table. The problem that I see is if there are so many transactions in the system, doing a full aggregate every time there is a transaction is probably going to be a problem. So here’s another idea I thought of: Have a table where you insert the aggregate data for a smaller time window. For example, every 5 minutes (or every hour, or every day) you run a job that aggregates all transaction data for the last 5 minutes. Then you have your total aggregate query run off of this table. The downside to this approach is that you don’t have real-time stats, you have stats up to the last aggregation.
window_aggregates
-----------------
id
start_time
end_time
revenue
balance
tansaction_count
...
total_stats_view
----------------
select sum(revenue) as total_revenue, sum(balance) as total_balance, sum(transaction_count) as total_transaction_count
from window_aggregates
/*you could put a where clause here to specify values for start_time and end_time, if you want*/
4