Many-To-Many Relationships
Hi everyone! I’d be interested to hear how others handle many-to-many relationships, particularly in Dataverse / Power Apps.
As I understand it, the out-of-the-box many-to-many relationship creates a non-editable intersect/linking table behind the scenes. Removing the relationship simply deletes that linking record.
Because of that, we usually create a custom table as the intermediary instead. This gives us more flexibility, such as adding custom fields, auditing, additional business logic, etc.
The question then becomes: what do you do when the relationship is removed?
We generally prefer deactivating the linking record rather than deleting it, mainly because it preserves the history and audit trail.
However, this can introduce a usability issue. If a user later wants to recreate the same relationship, and there should only ever be one unique linking record for that combination of master data, they may have to find the record in an Inactive view and reactivate it. That is not necessarily intuitive for end users.
Because of this, I’m wondering whether deleting the linking record is sometimes the better approach, despite losing the record itself and potentially some of its audit/history.
So I’m curious how others approach this:
- Do you generally use the native many-to-many relationship or create a custom intermediary table?
- If you use a custom table, do you deactivate or delete the linking record when the relationship is removed?
- If you deactivate it, how do you handle users recreating an existing/inactive relationship?
- Are there any patterns or best practices you’ve found work particularly well?
Interested to hear how others are handling this in real-world implementations.
