settingsLogin | Registersettings
Scalefree
Show Menu

how to map recursive relations of multiple entities

0 votes
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
asked Oct 22, 2017 by carlos (120 points)

Please log in or register to answer this question.

Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree
    DataVault.guru is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license.

    DataVault.guru | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke
    ...