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

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

Hope that helps.

Ravi

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

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

Thanks for the answers so far.

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