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