0

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
Improve this question
1
  • You may want to consider who needs to see these totals, how frequent they need to be updated. If you updated your aggregate every 100 transactions, would it really make that big of a deal? Commented Nov 5, 2014 at 20:14

1 Answer 1

Reset to default
2

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*/
Improve this answer
4
  • Ah, so a view that aggregates all transactions. Not a bad idea, but, there could be many, many transactions...millions. I am storing both user-level transactions AND site-level transactions (I'm using Mongo, so hence the optional-ness of transactions.userId). So, in a site-level transaction, I need to log the current site balance, and since transactions will happen frequently (let's say 10 per second), I would think an aggregation query would have a negative impact on performance. Commented Nov 5, 2014 at 18:59
  • Ah. That wasn't clear from your question. I'll update my answer... Commented Nov 5, 2014 at 20:54
  • 1
    Depending on the stats you need, you might be able to remove the need for aggregate queries (either updating after each transaction, periodic queries, views, etc) by using a running tally system. As a transaction is added, add that value to the your total as well. Commented Nov 5, 2014 at 22:52
  • @GrandmasterB Wouldn't the tally system be the same as what I would be doing with the stats table (keeping a summary and using that to log the balance in each new transaction)? If not, how does a tally system differ? Commented Nov 6, 2014 at 0:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.