Forum Discussion
Couple common relationship problems
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.
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.
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?
