I have a problem here and I hope that you people out there can help me with the algorithm or method or solutions. You guys don't really need to write a full code for me. Something like psuedo-code will do or any form of writing that can make me understand about the algorithm.

OK, here goes. I need to store organization's hierarchy information. The information will be stored in a database (most likely MySQL). This is the information about the organization structure.

The hierarchy contains three types on entity
- Department / Branch
- Position
- Employee

The rules

- Department can contain other Departments.
- Department can contain Positions.
- Department cannot contain Employees.
- Each Position can only contain one Employee.
- Employee does not contain anything.

Here is an example of a hierarchy
Code:
      [D] CEO Office
      |
      |-[P] CEO
      | |
      | \-[E] John Doe
      |
      |-[P] Secretary
      | |
      | \-[E] Sally Mok
      |
      |-[D] Human Resource
      | |
      | |-[P] HR Manager
      | | |   
      | | \-[E] Tom Sean
      | |
      | \-[P] Payroll Officer
      |   |
      |   \-[E] Tom Sean
      |
      \-[D] Finance Department
        |
        \-[P] Chief Financial Officer
My initial plan is to have 3 tables like below:
Code:
  Departments
  -----------
  department_id   : integer
  parent_id       : integer
  department_name : varchar(50)

  Position
  --------
  position_id     : integer
  department_id   : integer
  position_name   : varchar(50)

  Employee
  --------
  employee_id     : integer
  position_id     : integer
  employee_name   : varchar(50)
To display them, I would be using a recursive algorithm something like below:
Code:
function get_department($department_id)
{
   $sql1 = "select department_id from Department ".
           "where parent_id = $department_id";

   while ($child_dept_id = get_from_db($sql))
   {
      $sql2 = "select position_id from Position ".
              "where department_id = $child_dept_id";

      while ($post_id = get_from_db($sql2))
      {
         $sql3 = "select employee_id from Employee ".
                 "where position_id = $post_id";

         $post_id = get_from_db($sql2);
      }

      get_department($child_dept_id)
   }
   
}
The challenge here is that it queries that database too many times resulting in poor response time. And yes, this is a web-base system. In the real world, this the size of information that this system will hold is something like

40 Departments
800 Positions
800 Employees

I need a way that is will support the following

- fast and easy display
- fast and easy update

So is there any other way that I can do this. It does you are suggesting different database structure, as long as it satisfy fast display and fast update.