How to update balance in a accounting app with django ?

Question

I'm learning Django, trying to do an accounting app for keeping track of my expenses, etc..

I created the database with two models, one for accounts and one for operations. But i have no idea how to keep my balance updated with each operation.

I was thinking maybe, each time i save a new operation, I update the balance by overriding the save method of Operation Model ? But in that case, if i make a mistake and have to delete one operation, my balance won't be updated then, right ?

I thought I could also create an BalanceHistory model, with an history of all balances but then same problem in case of deleting an operation..

The best option i see would be to update dynamically my balance each time i want to display it, adding all the operations at that time.. but i don't see how i could do that..

So if anyone has some insight about that, that would be great. I know it is not a purely Django-related issue, but as i'm working with Django, it would be better if i can find an idea using Django features !

Thanks in advance for your time and your help !

Answer 1

You're right that this is tricky--there isn't a way to guarantee an accurate balance except by dynamically calculating it on every read based on the entire history of the account.

If you choose to go that way, you can use the Django ORM's aggregation features to calculate a sum of the Operations. For example, if you operation had a field called amount (a positive or negative number indicating the change in balance for that operation), you could could calculate the balance like:

Operation.objects.filter(account=my_account).aggregate(balance=Sum('amount'))

If you didn't want to do that for whatever reason, you could also maintain a running balance using a custom save method as you've described. You could use the delete method like save to handle individual item deletions.

However, that method will cause problems if you ever user bulk inserts, updates, or deletes or raw SQL operations, since those will not invoke the save and delete methods. However, if you need to use those features, you might be able to use a hybrid method--use the save and delete options when doing one-off transactions, and triggering a full recalculation with aggregation periodically to fix errors or immediately after doing an operation you know will screw up the balance.