>I have an inventory app, want to switch the back-end to MySQL, someone suggested me not to use calculated fields (eg: stock_OnHand) and replaced them with calculation by the fly.
>
>(I have stock_OnHand field to record the balance, while his idea is throw away this field, just keep the transactions, anytime need it, calculated them with SQL statement).
>
>I'm not sure about this, before I do a big change to my app, I would like to ask for second opinion, is this the way to do with MySQL?
>
>regards,
>
>Jerry

Jerry, in the case of stock on hand I would strongly recommend not trying to calculate it. After a few years that calculation would get very large. Just update the count in the inventory file each sale/purchase, etc. Besides, the inventory will be counted at least once a year, if not more, so even if it gets out of whack the user can fix it. My users usually find it is wrong when they go to pick from the shelf and nothing is there. Usually this is because someone walked out the back door with it.

There are some places where doing a calc on the fly is ok. For example the total of an invoice (by summing all the line items). Just ask yourself would this calc be resonable with 10 years of data.

This has nothing to do with the backend database. It has to do with basic sound database design.

HTH, John.