2 relationships between 2 entities in ER diagram

Question

I'm trying to draw an ER diagram describing the following:

-"Department" employs "Employees"

-Some "Employees" are "Special" and have more attributes

-Some of the "Employees" ("Special" and non special) are "Managers"

-"Managers" manage "Departments"

So, to convey this I have:

Department ------(employs)------- Employee-----<--------Special
      |                             |
      |-----------(manages)----------

From my understanding, I cannot have 2 relationships between 2 entities. How do I deal with this situation then?

Typal

Answer 1

There is no reason you can't have multiple relationships between the same two entities, your understanding is wrong in this respect.

You have to be certain, of course, that the relationships capture different information and that you haven't, say, mistaken the two aspects of a single relationship as two different relationships. Your example seems to be a good candidate for having two relationships between the two entities.

Jadda

Answer 2

Hi mate watch this diagram, hope it helps

Zerla

This solution is acceptable only if a Department can have multiple Employee as managers. – Clute

Answer 3

Better approach is to add a bit field in Employee table IsManager, so if he is a manager, that means that he is a manager on department he works.

Zerla

This approach doesn't allow for an employee to manage a department unless (s)he is also in that department, and it also allows more than one employee to be a manager in the same department. – Jadda

check out my other answer below – Zerla

hey what software are you using for making the diagrams? – Lippizaner

@Lippizaner I'm using Sql Management Studio. Watch this youtube.com/watch?v=wMbPRHeYvMU – Zerla

Answer 4

Your relationship for employs and manages are going to be two linking tables. e.g. Consider the tables and fields as below:

Department
   DepartmentID
   DepartmentName

Employee
   EmployeeID
   EmployeeName

SpecialEmployee
   EmployeeID
   Attr1
   Attr2

Now you can go on and link Employee and Department as such:

DepartmentEmploys
     DepartmentEmploysID
     DepartmentID
     EmployeeID


ManagesDepartment
     ManagesDepartmentID    
     EmployeeID
     DepartmentID

Now your relationships do not overlap or interfere. What seems to be drawn in your diagram is correct. There can be more than one relationships between two entities.

Critter

Your first linking table is not required. The DepartmentID in DepartmentEmploys can simply be a FK in the Employee table. Also, the second linking table is acceptable only if a Department can have multiple Employee as managers. – Clute

Answer 5

There should be only one class of employee If you have multiple categories of the employee then you can make child classes of Employee Class.
Parent: Employee
1st child: Special Employee
2nd child: Normal Employee

Philanthropy

That does not solve the problem of having two relationships between the same entities and therefore does not really answer the question. But otherwise this looks like a better way to model the employee. – Kahl