How banks calculate the balance for accounts?

Question

I'm implementing a small personal bank for the charity. I have created a table called transaction for all deposits and withdraws and each transaction relates to one account. Now how should I calculate the balance for accounts? Which solution is better?

1. Store the balance for accounts in a column of that accoun and update it after each transaction.

2. Store the last balance for accounts in a separate table after each transaction.

3. Don't store balance anywhere and calculate it dynamically when I need it: Sum(transaction.amount where deposit ) - Sum(transaction.amount where withdraw )

This is a web application by laravel framework but it will use on local as offline with about 200 accounts.
Thanks for reading.

Answer 1

Option 3 but, for efficiency's sake, you could cache the current balance in an account table.

Option 2's suggestion to store the latest balance after each transaction seems ok except I doubt you'll want to query that often. I imagine you'd either want the account's latest balance or you'll query a lot of transactions at a time and it'll be easy enough to recalculate each transaction's balance anyway.

Always keep every transaction recorded separately so the current balance can be recalculated and detailed reports can be made on transaction history.

If you do online banking, take ideas from them too.

Unrelated to your question, use an integer-like data type for the latest balance. Don't use float or double since they have floating-point error.

Make sure any updates to multiple tables are done with SQL transactions( https://laravel.com/docs/8.x/database#database-transactions ). You don't want to transfer money from 1 account to another in 2 steps that won't roll back in the extremely rare case that the computer shuts down after subtracting from 1 but before adding to the other.

Unrelated to your question, make security a big priority for that project.
- Always use HTTPS in production
- use CSRF tokens properly instead of just sessions
- check if there are guidelines, laws that you need to follow when managing this financial information.

Answer 2

Martin Taylor Thanks for your important warning about legal problems.
I Exactly worry about the problem that you said about the growing up transactions in the future, if I calculate the balance every time.

Answer 3

mohsen chavoshi wrote, "Josh Greig I appreciate you for your comprehensive answer. Especially for the laravel transaction method. Actually I need it. If I understand well, this method is for multiple operation that needs to perform all together at the same time. For example if I want to delete an installment or a loan from the database, it needs to delete it's transaction too. And if in any cause the second task not perform, all the performed task will roll back. Is that true?"

Response:
Yes. A single SQL transaction will either perform all the steps within them successfully or roll them all back. I warned you about the legal issue in my answer too. It is the last line.

I created medical software that managed identifiable American patient information and in response to Health Insurance Portability and Accountability Act (HIPAA) the software company got special insurance, product performed extra logging, web hosting physically in the United States, the database had to be encrypted, among other restrictions. I know that in Canada and United States there are laws governing how credit card information is to be managed too. I never created a banking application like you're saying or know anyone that did so you'd have to check it out. A specialized lawyer or a senior software developer for a bank in your country should know more.

Answer 4

Josh Greig I appreciate you for your comprehensive answer. Especially for the laravel transaction method. Actually I need it. If I understand well, this method is for multiple operation that needs to perform all together at the same time. For example if I want to delete an installment or a loan from the database, it needs to delete it's transaction too. And if in any cause the second task not perform, all the performed task will roll back. Is that true?

Answer 5

Thanks Josh Greig,
In fact this charity bank is not mine. I have been only a member of this bank about 10 years ago. But I know the owner and many other members. In these years they managed all of jobs in Microsoft excel. But now they have a lot of problems with it and they want to have a application for managing. I don't think I have to be responsible for legal matters, but however I'll consult with a lawyer, as you suggested. Thank you very much.

Answer 6

sql transactions (use case)