#### Need Help with Algo for Storing and Retrieving Organization Hierarchy / Tree View

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.