Forum: Building VoltDB Applications

Post: Calculating amount of "account balance"

Calculating amount of "account balance"
javadevmtl
Aug 14, 2016
Hello, I would like to calculate an "account balance" based on amount.

So say a user adds 10$, takes 5$, add 20$ then his balance should be 25$

I track each "transaction" in a trx table, so...
foo, ADD, 10
foo, TAKE, -5
foo, ADD, 25
bar, ADD, 100
bar, ADD, 400

I can either do SELECT SUM(amount) FROM trx_table where account_id = foo

Or I can have another table called account balance, so...
foo, 25
bar, 500

And then for each trx I can check the balance per account and do the math manually. And re-update the balance table.

Or create a view?

There's millions of accounts.

Also in the sp there will be a check to make sure you cannot take more than what you have obviously.
javadevmtl
Aug 14, 2016
So yeah view is a bad idea in this case because of the millions of accounts. I opted for option 2 where I transactionally add or remove from the balance of the specified account.
jhugg
Aug 14, 2016
I curious what kind of view schema did you try? A view with millions of rows should still work fine, assuming everything else makes sense.

Do you always just need the current balance, or do you often have to query for historical balances?
javadevmtl
Aug 15, 2016
I tried... create view acct_balances as select acct, count(*), sum(amount) from acct_log group by acct;

But i think transcrionally doing it like so...
- Get account balance from per account balance table
- check balance amount
- add or substract as needed.

Its more efficient and transactionally accurate. The view may not be up to date when a person tries to withdraw...
bballard
Aug 15, 2016
The view you defined would be just as fast to query as a balance table, assuming you have an index on acct.

Also, if you insert a record into your trx or acct_log table, the view would immediately show the updated result. It is impossible for the view to not be up to date.

As to which approach is better, it's up to you. Both would work, both would be fast. Using the VIEW eliminates the need to do write operations on the balance table, so it's slightly simpler. It may come down to how you want to handle things when it comes time to remove older transactions. If you use the VIEW you would need to SELECT from the view, delete the old transactions and then insert a compensating transaction record to preserve the balance. If you use a separate balance table, then you can just delete the older transactions.
javadevmtl
Aug 15, 2016
Ok cool. I have to go back and try it, but either way works.