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

ja wrote:
>
> 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.

This might be a perfect case for Celko's nested set approach. I'm not very firm on it, but you define the structure by adding left and right columns to Entity. It should be something like this:

Name        Left    Right
Group1      1       10
Group2      2       7
Person1     3       4
Person2     5       6
Person3     8       9

Of course, the role would have to be included in Entity as well.

I'd be happy, if somebody more firm on nested sets could jump in (perhaps even Joe himself?).

hth,
Heinz Received on Tue Feb 27 2001 - 10:15:08 CET