Couple common relationship problems

[MuFeR]

Hello! I have started working on a project including many imported tables and I've noticed some common patterns on relationship issues. Searching around I saw the most common suggestion being to use a star schema but that's not so easy when I get 100 tables that weren't designed this way 😅 So the 3 most common patterns I've had issue with and i'd like some suggestions are the following:

1: 2+ tables that need to connect to the same data table forming a triangle where 1 relationship is disabled as it's ambiguous.

("Spaces - guestRoleId" linking to "Access Roles - id" and "Space Members - member.id" again linking to "Access Roles - id").
For this I've tried 3 things:
a)Use the USERELATIONSHIP function (didn't work).
b)Create a duplicate of Access Roles  (for this scenario may be fine but in other cases like the "Users" table where i got 15 tables related to it I wouldn't want to duplicate it 14 times)
c)Add new columns to retrieve the info I need as in:
guestRoleTitle = LOOKUPVALUE('Access Roles'[title], 'Access Roles'[id],Spaces[guestRoleId])

2: Tables that reference themselves

So left one contains all projects and right has the IDs of parent/child projects:
For example:

FolderProjectParentID-	childId
1	2
1	3
1	5
2	4
2	6

So here the issue is that both FolderProjectParentID and childIds are pointing to "AllFolders/Projects - id". No clue how to approach relationship on this one to be able to show the Title of a parent project as well as all the titles of its child projects.

3: Lastly table that has 3+ relationships to another table.
For example the Projects table has ProjectDirectorID, ProjectManagerID, ProjectOwnerID all keys to "Users - id".
Again the simplest way I've found was either to use LOOKUPVALUE or RELATED function and add extra columns as needed on Projects table but wondering if there's a best practice for this as it's a very common scenario.

[AmiraBedh · Accepted solution]

For ambiguous relationships, I think it is related Spaces and Space Members tables both need to link to the "Access Roles" table.

While duplicating the "Access Roles" table may work, it's not scalable, especially when you have multiple tables requiring similar connections (like your example with the "Users" table).

If you want to use calculated columns with LOOKUPVALUE or similar functions just be careful with the performance.

I have one alternative in my mind (and which I recommend) is to create a bridge or intermediary table that helps resolve the ambiguity. For example, if the relationship between "Spaces" and "Space Members" to "Access Roles" is indirect, you can create a bridge table that uniquely links these tables. This way, you can manage the relationships without duplicating the "Access Roles" table.

For-self referencing tables, you can use PATH and PATHITEM to create paths from parent to child.

If you are comfortable with Role-Playing Dimensions, they may be helpful.

[MuFeR]

Thanks Amira that was helpful to get on the right track. Could you give some more info about the bridge table you mention to uniquely link "Space Members" to "Access Roles" because as you mentioned the relationship from "Spaces" is indirect. Also got any suggestions for the 3rd scenario I mentioned as well?