>Or is the design good enough and it is just that I need to change the way my calculations are done when calculating the balance due?
>
>2. I dislike having calculated fields stored in the tables, I would much prefer to calculate the balance due on the fly. However doing it this way speeds up reporting and the user interface, so I will live with this.
We were doing both - recalculate each time an amount was entered and store that in a field in the parent record. Actually, in all parent records: the total for the document, and the current balance for the item and customer or supplier.
It's a matter of getting the related records (i.e. those for the given item or customer) into a cursor, total in a scan/endscan loop, then insert or update. And that was fast enough even on 66MHz 486 using FPD2.6 through a local network, ten years ago. We even had some fairly complicated things - calculating the average price of materials for costing, where a component's price was tentative for a while (based on pro-forma invoice until the real invoice comes and the shipping/handling cost was spread), so each time the supply price would change, we'd recalculate recursively everything from the time of that document until now - not just the price of that item, but any other item that had it as a component, as a component's component, as a ... you get the picture.
Today, I don't even think about it - just recalculate each time, so everything is up-to-date at all times. I do store the totals just to speed up the reports and view grids. The code that calculates goes into the bizobject for the given entity - be it a customer, inventory item, patient, anything.