Question

I got a particular case that I am quite unsure of how to model.

source has 3 tables

Groups
GroupSourceSystem - BK
GroupName - BK
group details ...

Users
UserSourceSystem - BK
UserName - BK
user details ...

GroupMembership
SourceSystem - BK - UserSourceSystem and GroupSourceSystem must be same to match
GroupName - BK
MemberName - BK

Mapping Groups and users its easy - standard rules

For Group Membership that is where I am unsure

Members can leave or join a group at any time - a member can be on a group for a few days, then be removed for weeks and added again at a later stage
a Member can be either a user or a group - this creates a recursive situation - including circular cases that need fixing
As it can be either a user or a group we don't have foreign keys to the user/group table as that would not work.

As an example at a point in time we could have (for a single source system)

Groups
Group A
Group B
Group C

Users
User A
User B
User C

GroupMembership

Group A Members
  Group C
  User A

Group B Members
  Group C
  User B

Group C Members
  User B
  User C

Following Business questions are the most common ones

Which groups does a user belong to - directly
Which groups does a user belong to - directly and indirectly

Which groups does a group belong to - directly
Which groups does a group belong to - directly and indirectly

Which users and/or groups belong to a group - directly
Which users and/or groups belong to a group - directly and indirectly

One option for the above would be to have both groups and users on the same hub but this would not be possible for all sources as in some cases the group name is the same name as one of the users of that system.
And would not be clear from the model point of view of what is a group and what is user even if that could be addressed with a view.

Any ideas quite welcome