Search code examples
databaseerdentity-relationship

How can I create a role hierarchy in an ER-Diagram?

I know for a recursive relation where there are 2 roles, for example:
Worker works for Manager
and Worker and Manager both are 2 roles of Employee, we show it this way: enter image description here

But what if Employee had roles like:
1. Director
2. Manager
3. Secretary
4. Worker
and every role in the lower hierarchy works for the ones in the higher rank.

For Example:
Manager works for Director, Secretary works for Director and, Worker Works for Director

Then again,
Secretary and Worker also Works for Manager and so on...

How to show that in the ER DIAGRAM?

Solution

  • If employees have positions (roles), so we have here a functional redundancy attribute/property. This redundancy is the current position (role) of a employee. When broken down (decomposed), it will become another entity with a 1-N relationship to employee.

    Create a self relationship (recursive relationship) between employees says which employee work for another, but does not tell you which role governs what.

    If on the other hand, you need to define and persist which positions (role) work for each other, then you need to create an role entity, and create a recursive relationship for it, expressing which governs over another. That done, you relate employee with a role (posteriorly, a row/register in the Role table), defining a relationship of which employee have which role.

    and every role in the lower hierarchy works for the ones in the higher rank.

    Based on the positions (roles) of the users, and the hierarchy created among them (the roles), you can tell which employee works for which. This is done with a simple SQL join, for example.

    I think this is your case. You do not want a hierarchy between people/employees, but between positions/roles. One thing leads to another. With this approach, depending on the cardinality imposed by you here, you might even say whether one or more roles govern or are governed by others. This facilitates in terms of maintenance in the future.

    Any doubt or question, please comment and I will answer.