Results 1 to 7 of 7
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Financial/Organizational/Positional Hierarchies - managing all three!

    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....

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I haven't looked at Orange's link, but I would start with your most complex hierarchy then see how best to map the other hierarchies to it.

    How do I tie an organization to a cost center? How do I tie a cost center to a salary?
    it sometimes helps to come at a problem from the opposite direction i.e. tie a cost centre to an organisation or a salary to a cost centre. More a bottom up approach rather than top down

  4. #4
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Ok...So somebody please check my math. Following Ajax's recommendation (Orange - I didn't see a data model from that site that fit), I started with the Position hierarchy.

    Every Position is part of one Organization, so that sounds like a one to many table to me.
    Every Organization is then part of one Cost Center, so that sounds like a one to many table to me as well.
    So the position, organization and financial tables will have self-joins to describe their internal hierarchy, and the table joins will help translate those hierarchies across the tables themselves.

    To get salaries in there, I'll have to create an Employee table that joins to the Position table somehow (the rub there is that since we're a health system, we have employees who work in multiple positions). One employee can hold multiple positions, but each position can hold multiple employees (because of the need to maintain a history of incumbents). Sounds like a junction table between employees and positions (which I've seen several data models have). But here's another one: each employee can have multiple salaries in the same position, or multiple positions with the same salary. Do I just make salary a field in the employee/position junction table? Then if they get a raise, you create a new record, holding all other info the same?

    Sorry to ask questions like these. It's actually rare that I'm actually stumped completely; more usually it's that I have a solution but my inexperience makes it unnecessarily elaborate or creates problems elsewhere I just don't see yet. So looking for validation with some of my stream of consciousness posting here.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    each employee can have multiple salaries in the same position, or multiple positions with the same salary.
    that needs some explaining

  6. #6
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    The manager of one of my teams has been in that position for five years. She has had multiple pay raises.
    I have a network engineer who moved from one team to another (different position), but kept the same salary.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    include start dates for the salary - new salary with a later date supersedes the previous salary

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Financial years
    By sdel_nevo in forum Queries
    Replies: 10
    Last Post: 01-10-2017, 09:28 AM
  2. Replies: 5
    Last Post: 06-27-2013, 11:15 PM
  3. Financial Report
    By Luke in forum Reports
    Replies: 11
    Last Post: 07-05-2011, 01:08 PM
  4. Financial Query
    By Luke in forum Queries
    Replies: 8
    Last Post: 06-30-2011, 12:33 PM
  5. Modify tables organizational structure
    By snoopy2003 in forum Database Design
    Replies: 2
    Last Post: 03-01-2011, 11:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums