Indexing Sums from Related Tables
Lets say I have Project, and ProjectCharges as my tables.
ProjectCharges relates to Project on project_id, and has a number field, amount.
Project has a calculation: cTotalChargeAmount that is defined as SUM( ProjectCharges::amount ).
This works fine as an unstored calculation, but I need to be able to store and index the value so that I can speed up finds based on cTotalChargeAmount.
ProjectCharges is in a different file than Project.
If I store the results, how can I be sure that the values in cTotalChargeAmount are up to date?