How to do many-to-many relation between the same entity

Question

I have an Employee entity class with (Id,Name,EmployeeType). EmployeeType entity (Id, Description) where Description can be either REGULAR/MANAGER.

I am confused on how to map Employees who are of type REGULAR to their corresponding MANAGER type Employees. Should I just add an extra field to the Employee entity itself so that it now becomes (Id, Name, EmployeeType, ManagerEmployeeId)? Or should I instead have a lookup table Employee_Manager (Id, RegularEmployeeId, ManagerEmployeeId)?

I am considering going with the Employee_Manager lookup table and am not sure how that entity class would look like. The following below is what comes to my mind. Am I on the right track here?

@Entity
@Table(name="EMPLOYEE")
public class Employee{
    @Id
    int id;

    @Column(name="NAME")
    String name;

    @ManyToMany(mappedBy = "regularEmployee")
    Collection<Employee> regularEmployee

    @ManyToMany
    Collection<Employee> managerEmployee;
}

ps. I am using JPA with Hibernate as the persistence provider.

Answer 1

If you're trying to have an employee have exactly one manager, then first of all you're doing a many-to-one relation (not many-to-many) and having the ManagerEmployeeID in the table as a foreign key reference to the same table is just fine.

Use a lookup table if you want to allow an employee to potentially have more than one managerial-type role. You can also use this if you want to assign a particular "role" to these manager-type people:

create table Supervisors (
  eid int,
  sid int,
  role varchar(16)
);

Then you could use role for "Supervisor" vs "Manager" vs "Slavedriver" vs who knows what.

Sorry, I don't know any JPA/Hibernate, so the concepts (and pseudo-SQL) is the best I can give you.

Hope that helps a bit.