Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    6

    Recursive relationships

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could, but it would be less elegant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  4. #4
    Join Date
    Dec 2004
    Posts
    6
    thanks for the feedback

    cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •