A thumb rule, never create calculated fields or columns in your DB. That's always for your front end apps or views to do as long as you keeping the details of debit/credit transactions that's enough to get the balance right into a report or UI.
That's more flexible to view balance in a more flexible way like opening balance and closing.
That’s actually a great design rule, but what to do when the database grows big? To calculate the balance based on the transactions in this situation can cost a lot, right?
Transactions data are always big in size specially at banks, the most transnational systems could be there. Having a calculated field or a separate field in your DB has higher cost than calculating it in a report or a view, users don't view their balances as much as they transact. If you do double transactions with each request: one to insert the transaction value and type and another one to update balance, that's bad for the performance. You want first your transactions to be fast as much as possible. Triggers impact performance if its one of the ways to do it.
Normally banks don't allow you to view balance over 10 years time in one shot, for example my current bank allows 3 months back on their online interface or to download. There is a limit for everything, so showing a huge amount of transactions for the user like over a year in a one click and expecting it to be fast is not achievable.
That's why in HTTP we have 201 response that can be used for long running jobs, for example you will request a balance report for a year period and the bank system will receive the request and handle it by running a background job probably over non-peak time and the then send back over email...different ways to handle this.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
A thumb rule, never create calculated fields or columns in your DB. That's always for your front end apps or views to do as long as you keeping the details of debit/credit transactions that's enough to get the balance right into a report or UI.
That's more flexible to view balance in a more flexible way like opening balance and closing.
That’s actually a great design rule, but what to do when the database grows big? To calculate the balance based on the transactions in this situation can cost a lot, right?
Transactions data are always big in size specially at banks, the most transnational systems could be there. Having a calculated field or a separate field in your DB has higher cost than calculating it in a report or a view, users don't view their balances as much as they transact. If you do double transactions with each request: one to insert the transaction value and type and another one to update balance, that's bad for the performance. You want first your transactions to be fast as much as possible. Triggers impact performance if its one of the ways to do it.
Normally banks don't allow you to view balance over 10 years time in one shot, for example my current bank allows 3 months back on their online interface or to download. There is a limit for everything, so showing a huge amount of transactions for the user like over a year in a one click and expecting it to be fast is not achievable.
That's why in HTTP we have 201 response that can be used for long running jobs, for example you will request a balance report for a year period and the bank system will receive the request and handle it by running a background job probably over non-peak time and the then send back over email...different ways to handle this.