DEV Community

Discussion on: Solutions for storing state in a database

Collapse
zyabxwcd profile image
Akash

This was very interesting and much needed. I was looking for an article like this. You should do more of these. It would nice if you can even go into how indexing can be done, in a similar manner this article is written. Is there a robust framework or like a series of specific questions that one can ask himself when designing a db schema? For eg - the questions you mentioned that would be impossible to answer if we hadn't stored history of state movements and perhaps used pivot tables.

Collapse
ddarrko profile image
Daniel Benzie Author

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.

Collapse
zyabxwcd profile image
Akash

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.

Thread Thread
ddarrko profile image
Daniel Benzie Author

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 :)