What is the best design for entities in order to represent dynamic organizations?

I’m trying to design entities for dynamic organizations for future use-cases;

Pattern1

.
└── headquarters (the largest unit)
    └── division
        └── department
            └── personnel (the smallest unit)

Pattern2

.
└── headquarters (the largest unit)
    └── division
        └── branch
            └── personnel (the smallest unit)

Pattern3

.
└── headquarters
    └── division
        └── department
            └── foo
                └── bar
                    └── personnel

Question

• What is the best design for entities in order to represent dynamic organizations?
• I think some geniuses must have already designed such organization patterns with SQL, I’d like to know that the design pattern if it exists.

Assumption

• There should be some units between the largest unit and the smallest unit

  • Numbers of that unit is variable

• For example, I’d like to know a personnel’s name or division

Thank you

There are two different approaches to achieve this.

Solution 1: The hierarchy is in the employees data

Add a reportsto field to your employees table

Each employee has an ID. This can be a unique identifier or an employee number that is given to each employee when she/he starts at the company. Each employee will have to report to another person (e.g. manager, team manager, …) Here is some example data for such an EMPLOYEE table with a REPORTSTO column

Example EMPLOYEE table:

 UID   | REPORTSTO | NAME      | DEPT
-------+-----------+-----------+---------------
 UID1  |           | Smith     | CEO
 UID2  | UID1      | Vernon    | CFO
 UID10 | UID2      | White     | CFO1
 UID23 | UID2      | Rutzky    | IT1
 UID22 | UID2      | V.        | MN1
 UID6  | UID23     | M.        | IT12
 UID9  | UID23     | Nets      | IT22

Pros and Cons

The benefit of implementing this solution is that the departments and sub-departments are irrelevant. Everybody has to report to somebody. You can now query the hierarchy with a script to return a lovely tree view. The disadvantage is, that you have to change the reportsto each time somebody leaves the company.

Solution 2: The hierarchy is in a separate table.

Separate the hierarchy from the employees data

The employee has a link to a hierarchy/department table. The hierarchy is not stored together with the employee data, but in a separate table

Example of EMPLOYEE table:

 UID   | HIER_ID   | NAME      | 
-------+-----------+-----------+---------------
 UID1  | 1         | Smith     | 
 UID2  | 2         | Vernon    | 
 UID10 | 7         | White     | 
 UID23 | 14        | Rutzky    | 
 UID22 | 17        | V.        | 
 UID6  | 210       | M.        | 
 UID9  | 213       | Nets      | 

Example of HIERARCHY table:

 HIER_ID    | BELONGSTO    | DEPT_SHORT     | DEPT_DESC
------------+--------------+----------------+-------------------------
 1          |              | CEO            | Chief Executive Office
 2          | 1            | CFO            | Chief Financial Officer Office
 7          | 2            | CFO1           | Assistant CFO
 14         | 2            | IT1            | Head of IT
 17         | 2            | MN1            | Manufacturing Lead Manager
 210        | 14           | IT12           | Assistant Head of IT
 213        | 14           | IT22           | Database Administration

Pros and Cons

The benefit of implementing this solution is that the hierarchy is not linked to the employees, but rather the employee is linked to a hierarchy. Likewise you can have an infinite number of sub-departments / sub-structures and don’t have to think about limitations. The disadvantage is if the structure changes inside the company (e.g. IT1 (14) doesn’t belong to CFO (2) but reports directly to the CEO (1)), because it requires a separate user interface to modify the hierarchy.