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

    Resolve recursive relationship

    Hi there,

    a recursive relationship can be resolved by introducing another entity. For example: "Employee" has a recursive 1:n relationship "is managed by". This can be resolved to: "Employee" related via n:1 with "Parent Employee". Is that right so far?

    In a recursive relationship the "Employee" entity would have an attribute (not a foreign key) called "parent_id". What happens to that attribute when the relationship is resolved? Should it be changed to be a foreign key?

    meeks

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Recursive relationships resolved

    Hi,
    The structure of the tables would look like this: (pseudo-code)

    Code:
     
    Employee (emp_id, first_name, last_name, ..., primary key (emp_id))
     
    Manager(emp_id, mgr_id, ..., primary key (emp_id, mgr_id, ..),
    foreign key f1 (emp_id) references employee(emp_id),
    foreign key f2 (mgr_id) references employee(emp_id)
    Hope that helps.

    Ravi

  3. #3
    Join Date
    Dec 2004
    Posts
    54

    Recursive Relationship

    Hi Ravi,
    Relationship between entities, even between the same entity is defined by the nature of the business.

    You said "a recursive relationship can be resolved by introducing another entity." and this isn't up to you to decide this is defined by how the business or real world entity is related.

    If you state 'A (parent) employee can be related to zero, one, or many (child) employees. AND A (child) employee can have one and only one (parent) employee'. Then your entity EMPLOYEE must have a recursive relationship.

    However if your business is:
    If you state 'A (parent) employee can be related to zero, one, or many (child) employees. AND A (child) employee can have zero, one, or many (parent) employees'. Then your entity EMPLOYEE must have an associative entity.

    I'm not certain where you got the 'resolved' idea. You only 'resolve' many to many relationships. There is no 'resolving' of a (zero), one to many relationship.

    You model the business

    If you have a many to many as in my second example, then you have two attributes that make up the primary key of the associative entity. The primary key of employee one (parent id) and the primary key of employee two (child id)


    Hope that helps!
    Vmusic

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Hi Vmusic,

    I may be nitpicking but I was not the one who posed the original problem. Neither did I make the statements attributed to me in your post.

    Reading the original post gives me only a very vague idea of what the original poster (OP) intended. It seems to me that he wanted to model the employee manager relationship. Since the concept of a manager is distinct from the concept of a manager, it makes sense to have a new relation called manager to model that requirement.

    My suggestion seems to meet the business requirements as I understood them. The advantage that it has is that we can add additional information about the manager-employee relationship, such as the start_date and end_date, etc.

    Ravi

  5. #5
    Join Date
    Dec 2004
    Posts
    6
    Thanks for the answers so far.

    I base my assumptions about "resolving" a recursive relationship on this thread:
    http://www.dbforums.com/t1075898.html

Posting Permissions

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