0

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.

0
1

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.

2
  • can you give example how irwin and ernest placed in your schema? Mar 8 '18 at 11:03
  • 1
    Whatever I do StackExchange seems to turn my comment to trash. Here, see this paste instead: pastebin.com/GaYR31br Mar 8 '18 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.