I am developing the Performance Management System for our organisation. There can be one or more than one child of an objective and the child objectives can also have one or more child objectives and this can go on and on. The values can be only entered at the final leaf. Please see the attached diagrame for more explanation.
To achieve this i have and Objective table with (Objid PK, ObjName, AggType(Avg,Sum etc))
to create the hierarchy created the the following table
ObjDepend(DependID, ParentID FK Obj, ChildID FK Obj)
Each Objective can have one or more than one measure
OMPairs (ObjID - MeasID PK, Formula etc)
The values are entered in the following table
ObjValues(ObjID-MeasID FKOmPairs, Actualv, TargetV, PeriodDate)
Now i need to calculate the values of child nodes depending on the aggregation type set in the objectives table(SUm, Avg, Max, Min) and set the value of the parent. But as it can be any level so i am stuck as how to find all the objectives in the tree and update them accordingly?
Sorry if i couldn't explain it properly but really looking forward to solve this.