Mysql – How to create employee tree structure

database-designMySQL

My apps was used for since 4 years ago. I have 1 table structure which i doubt is the best practice. And i want to fix it if i have free time later.

this my recent design:

tbl_echelon2: 

+----+------------------------+
| id |          name          |
+----+------------------------+
|  1 | Public Relation Bureau |
+----+------------------------+

tbl_echelon3: 

+----+------------+-------------+
| id |    name    | echelon2_id |
+----+------------+-------------+
|  1 | Division A |           1 |
|  2 | Division B |           1 |
+----+------------+-------------+

tbl_echelon3: 

+----+--------------------+-------------+
| id |        name        | echelon3_id |
+----+--------------------+-------------+
|  1 | SubDivision 1 of A |           1 |
|  2 | SubDivision 2 of A |           1 |
|  3 | SubDivision 1 of B |           2 |
|  4 | SubDivision 2 of B |           2 |
+----+--------------------+-------------+

tbl_position: 

+----+---------------------+
| id |        name         |
+----+---------------------+
|  1 | Head of Bureau      |
|  2 | Head of Division    |
|  3 | Head of SubDivision |
|  4 | Staff               |
+----+---------------------+

below is the table that i want to ask for the best practice

tbl_employee: 

+----+---------+-------------+------+------+------+
| id |  name   | position_id | ech2 | ech3 | ech4 |
+----+---------+-------------+------+------+------+
|  1 | Andrew  |           1 |    1 |      |      | Head of Public Relation Bureau
|  2 | Beyonce |           2 |    1 |    1 |      | Head of Division A
|  3 | Cody    |           2 |    1 |    2 |      | Head of Division B
|  4 | Dan     |           3 |    1 |    1 |    1 | Head of SubDivision 1 of A
|  5 | Ernest  |           3 |    1 |    1 |    2 | Head of SubDivision 2 of A
|  6 | Frans   |           3 |    1 |    2 |    1 | Head of SubDivision 1 of B
|  7 | Graham  |           3 |    1 |    2 |    2 | Head of SubDivision 2 of B
|  8 | Hardy   |           4 |    1 |    1 |    1 | staff of SubDivision 1 of A
|  9 | Irwin   |           4 |    1 |    2 |    2 | staff of SubDivision 2 of B
+----+---------+-------------+------+------+------+

My question: how to do the best practice on this case? is my design is acceptable?

The current application has a SQL Server database as the back end; however, the version under development will use MySQL instead.

Best Answer

What will happen if you need a fifth echelon? And what if an employee has a role in more than one division?

A smaller, simpler schema should cover your business case and require less work to implement, maintain and extend. For example:

Employee (id, name)

Division (id, name, subdivision_of)
    [subdivision_of -> Division.id]

Position (id, name)

Role (employee_id, division_id, position_id)
    [
        employee_id -> Employee.id,
        division_id -> Division.id,
        position_id -> Position.id
    ]

If you don't want one person to be able to work in more than one division, I suppose you can declare employee_id as unique.

Edit: another problem that I see is a redundancy anomaly in the employee table. It repeats the information about the hierarchy of divisions. If you ever change that structure you have to remember to go over every row in the employees table to make sure that change is reflected there.

Related Question