Modeling 'group membership' - forehead sore from banging against wall

Greetings all,
I am sure the problem I am trying to solve is a common one, but I could really use some insight. I'm trying to put in place a structure and process for maintaining group membership and roles. Seems fairly straightforward to model, but implementing a few things really has me stumped.

Say we have people and groups and people play a role (or more) in a group. No problem. Now, groups can hold membership and play a role or more in other groups. At first glance, sounds like a tree structure (and very well may be). What I've come up with so far is 2 tables that look something like:

Entity:
  EKey PK

Membership
  MKey PK
  EGroup FK references EKey
  EMember FK references EKey
  MRole

(I think other attributes of 'Entity' are irrelevant here, but an 'Entity' could represent a group or a person, some of whose attributes would be in other locations...that would be a matter of supertypes/subtypes I believe....)

This seems to work well for identifying the members of a group, and making sure that a group cannot have itself as a member and of which groups a person (or group) is a member. The roadblock I've hit is in ensuring that a group cannot be it's own grandparent or great-grandparent or great-great-grandparent - siblings are OK (different roles), but offspring are not! I see that it is in making that jump to the next level and the next level beyond that. What I'm wondering is if a typical solution to this problem employs (roughly) this data structure and a bunch of recursive code, or a completely different data structure and some elegant and efficient code? Any observations would be most appreciatively welcomed.

Thanks
Jim Received on Mon Feb 26 2001 - 14:01:50 CST