I have yet to role out a complex design that would entail a large amount of data and sophistication. I'm working on something larger and I'm concerned about the "cost" of calculated fields.
As an example, take an accounting system which has a chart of accounts, and a lot of transactions related to those accounts.
If I were to run a report to show the account balance for each account (sum of credits less debits as an example equation) - what is the best design?
Should account balance be a calculated field, or should I leave it as a normal field and update it as needed?
This might be an oversimplified example but I'm curious about the trade offs.
it could also apply to inventory: current balance, average cost, etc.
I realize that calculated fields will create performance issues, but I don't know if it's worth designing a system that is constantly updating / managing fields rather than calculating on the fly.
Thanks in advance for your feedback!