Is it more beneficial to possess a area within the database that stores the clients balance or use sights and queries to create the data.
For performance, I'd say both. Have a log of all of the transactions (inside a separate table) but conserve a area within the customer record that stores the present balance that will get rejuvenated whenever you increase the transactions.
I believe this is dependent on lots of factors, how frequently are you being able to access the total amount, how complex could it be to recalculate it each time you really need it. Do you know the expenses of getting sights, etc.
Purely around the face from the information you've given I'd keep value as recalculating it on your own every time might be a discomfort.
If sights and queries provide you with fast enough result then don't store inside a separate area.
When not fast enough then store it right into a separate area. Because this area will store redundant information it is crucial to help keep it synchronized.
One project I done we saved the present balance in a single area, and all sorts of the transactions on another table, but due to the amount worth focusing on this project had around the data being perfect 100% (or better) or even the time, we saved a hash from the balance in another area and also the hash was in comparison every time it had been known as to make sure integrity, it if didn't complement it had been recalculated in the transactions table, and re-hashed then delivered to the client support dept. for review.
"It is dependent". Most frequently, you need to avoid derived data. However, you will find cases when getting the derived total is justified.
Just to illustrate:
I done a credit database application, in which the balance was composed of numerous various things, and various business rules with time. For instance, the "balance" was really a quantity of various balances from different containers, for example Principal, Costs, etc.
As transactions were published, these were allotted to various containers based on business rules. Costs visited the costs bucket. Purchases, credits, and debits visited the main bucket. These allocations and rules were susceptible to change with time.
Imagine querying 100,000 customer balances quickly when confronted with altering business rules with time. This can be a solid situation where getting the derived value really seem sensible. We maintained some calculations to "rewind" the account and rebuild the total amount chronologically for audit and verification reasons, however it was nothing you would like to do for big sets.
Everybody here's right. It will depend. But you could have the very best of both mobile phone industry's using a view. Seems like you may have a reasonably small system, and dynamically calculating the total amount would be the simplest factor to complete. To be able to make it simple, I'd define just one view which has the account data you would like (calculated dynamically).
If you are have to more performance than that, I'd setup a trigger-based system for upgrading the total amount towards the primary account table, then update the vista behind the curtain so you don't have to change every other code. Just make certain you are while using right database isolation way of either query, or you will be in danger! -)
That might be a purpose of how frequently you have to access these details. If it is every now and then, then I'd say proceed and recalculate it.
Use sights and queries - you would be suprised at just how fast it'll run.