I want to develop a KPI system but have some trouble on database design
Before I start, let me higlight first the business requirement:
- Parent can get all child KPI. For example from image below (Strategy Hierachy), I can get all KPI under Strategy 1 (KPI Project ABC and Project X)
- The project is assigned under one unit. For example Project ABC is assigned under Software Dev Unit.
- For Organization Hierarchy, I also can get KPI under the parent, for example get all KPI under Sofware Dev unit (KPI for Project ABC)
My idea on database design:
Since Organization Hierarchy and Strategic Hierarchy is hierachy based, I want to use nested set model to store the hieraychy in the database.
The nested set model allow for easy query all KPI under the parents. For example get all KPI under MYCOMPANY Enterprise, under ICT sector, under Strategy 1 and so on.
So I will create 2 seperate table, one table to store Organization Hierarchy, and one table to store Strategy Hierarchy
THE QUESTION IS:
Do I create a seperate table for KPI?
Since there is two seperate table, and KPI is only inserted once, How do I link/share the KPI data with both Organization Hierarchy and Strategy Hierarchy while still maintaining the tree / nested set model for both table?
Thanks in advance