Hi there,

the concept of recursive relationships seem to be clear for me. A table "Employee" can have such type of relationship (lets say "manages"), which means that an employee is managed by an employee who, again, can be managed by an employee, who again... etc. . Where the table has an attribute "Is managed by" which holds a foreign key containing an "employee_id". The "employee_id" itself is the primary key of the "Employee" relation.

Now I wonder if the recursive relationship can be resolved by introducing another entity.
Lets say it's a 1:n recursive relationship (many employees are managed by one employee.. and so on). If one would create another entity, e.g. "Parent Employee" connected to "Employee" by a n:1 relationship.
Wouldn't I be able to retrieve the same information as with a recursive relationship?

you could, but it would be less elegant

Actually, I almost never keep the recursive relationship in the same entity but almost always create a new one.

One of the advantages of creating a new entity is that, I think, the design is cleaner. The employee entity only talks about employees. While the manager entity talks about managers and their employees. Also, I never need to have a null value for manager_id when the employee is at the top of the hierarchy. The manager at the top of the hierarchy is just as easily identified in either case.

Keeping the manager relationship in a new entity also has the advantage that you can add other attributes to this entity such as start_date and end_date. In real life, an employees manager can change. If so, the single table version loses historical information.

Of course, it depends upon the requirements of your work.

The advantages that I see in creating two entities far outweigh limitations, if any.

Ravi

thanks for the feedback

cheers