Thanks Akash ! I will definitely post more on similar topics in the future.
I don’t follow any particular framework when designing tables. I’m sure there are some out there though !
For me I just ensure the data is normalised - and I try to imagine how each data might be accessed in the future.
One general rule (which is applicable above) is to try not to store a calculated value - rather store the events that make up the calculation. For example if I were to make a bank system. If I wanted to get the balance it might be tempting to have a balance column. What is even better is to calculate this based on the sum of transactions.
but what if that calculated value is being required in few or many places, wont calculating it each time add to the response time? is it still viable to not store a calculated value and rather store a db event? is calculated value being accessed multiple times good enough reason to store it in db? like in your case, calculating the account balance by summing up the transactions that took place, wont that be too intensive cause practically there is no upper limit to the number of transactions that can happen.
Absolutely correct. Calculating the stored value takes computing resource. That’s why when searching/filtering you used a stored value cache.
In my specific example you can use something like a statement period. When a new statement is generated you persist the balance. When calculating the current balance you take the stored and sum any transactions since. This prevents any editing of previous financial data. It also means in the future you can even archive historic data.
The benefits are similar to the ones discussed in the article. Write only transactions. Race conditions become impossible. Full audit of an account and it’s balances at any one time. This is another article in itself :)
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.
Thanks Akash ! I will definitely post more on similar topics in the future.
I don’t follow any particular framework when designing tables. I’m sure there are some out there though !
For me I just ensure the data is normalised - and I try to imagine how each data might be accessed in the future.
One general rule (which is applicable above) is to try not to store a calculated value - rather store the events that make up the calculation. For example if I were to make a bank system. If I wanted to get the balance it might be tempting to have a balance column. What is even better is to calculate this based on the sum of transactions.
but what if that calculated value is being required in few or many places, wont calculating it each time add to the response time? is it still viable to not store a calculated value and rather store a db event? is calculated value being accessed multiple times good enough reason to store it in db? like in your case, calculating the account balance by summing up the transactions that took place, wont that be too intensive cause practically there is no upper limit to the number of transactions that can happen.
Absolutely correct. Calculating the stored value takes computing resource. That’s why when searching/filtering you used a stored value cache.
In my specific example you can use something like a statement period. When a new statement is generated you persist the balance. When calculating the current balance you take the stored and sum any transactions since. This prevents any editing of previous financial data. It also means in the future you can even archive historic data.
The benefits are similar to the ones discussed in the article. Write only transactions. Race conditions become impossible. Full audit of an account and it’s balances at any one time. This is another article in itself :)