Hello,
I'm writing an IT Financial Management database which will include cost allocation (like chargeback) to various departments based on usage. For that reason, it's critically important that I model the organization correctly...and I need help.
We essentially have three hierarchies in the corporation:
1) Financial hierarchy: this hierarchy is very flat - only two levels - with a parent corporation and its constituent cost centers underneath. The parent corporations correlate directly to corporate entities; however, the cost centers do NOT strictly correlate to departments. For example, within my own division, I have a cost center (Application Services) that is actually made up of two different teams with two different managers. They are grouped under Application Services because they perform the same type of work.
2) Organizational hierarchy: this hierarchy is a little less flat. The parent corporations are the same but the tree goes deeper. In the example from #1, the Acute Applications and Ambulatory Applications teams (forming the Application Services cost center), are part of Information Services, which is then part of the Medical Center, which is then part of the Health System. The tree doesn't have the same depth across the organization; some are deeper, some are shallower.
3) Positional hierarchy: this hierarchy is the most vertical. The Acute Applications team leader reports to my Deputy, who reports to me. I report to the COO, who then reports to the CEO. Of the five people in that chain, only two align as leadership of a cost center, and only three align as leadership of an organization.
Managing these hierarchies is critical. The financial hierarchy is important so I can apply cost centers to budgets, expenses and invoices (G/L entries). The organizational hierarchy is important for calculating things like number of positions under any level of the hierarchy (for cost allocation). And the position hierarchy is important to defining who has access to the ITFM tool as well as salary budgets.
I would love to hear your ideas about how to manage this level of ugly. I could create a bunch of hierarchical tables to manage each one, but the challenge arises when you try to tie them together. How do I identify the position in charge of an organization? How do I tie an organization to a cost center? How do I tie a cost center to a salary? The only answer I can come up with is 42....