Dataflows Part 4 - Many-to-Many Relationships

This is Part 4 of my Dataflows series on Hack the Platform. In this post (and the accompanying video), I demonstrate how to handle many-to-many relationships in Dataverse using Characters and Planets. In the video, I selected the manual table approach, giving full control over relationships, mapping, and integration with Dataflows. Below is a detailed comparison of the manual approach versus the built-in Dataverse many-to-many functionality.

Modeling Many-to-Many Relationships in dataverse

In Microsoft Dataverse, modeling many-to-many (N:N) relationships is a common requirement. Whether you're building a game universe, a CRM, or a knowledge graph, you’ll often need to link entities like Characters and Planets. This blog post explores two approaches to modeling such relationships in Dataverse, comparing their pros and cons across multiple dimensions.

The Scenario

We have two tables:

Characters: Stores individual character records.
Planets: Stores planetary records.

We want to relate these two tables in a many-to-many fashion. A character can visit multiple planets, and a planet can host multiple characters.

The Two Approaches

Approach 1: Manual Many-to-Many (Custom Intersect Table)

Create a third table called CharacterPlanet with two lookup columns:

Character: Lookup to the Characters table.
Planet: Lookup to the Planets table.

This table acts as the bridge between the two entities. You manage the relationship explicitly and can add additional metadata if needed.

Approach 2: Built-in Many-to-Many Relationship

Use Dataverse’s built-in many-to-many relationship feature. Dataverse automatically creates a hidden intersect table behind the scenes. You define the relationship directly between Characters and Planets, and Dataverse handles the rest.

Comparison

Here’s a summary of how each approach compares across key dimensions:

Dimension	Approach 1: Custom Intersect Table	Approach 2: Built-in Many-to-Many
Compatibility with Apps	Works in both app types. In model-driven apps, users must create a new CharacterPlanet record for each association. In canvas apps, you manage the linking logic manually.	Seamless in model-driven apps with multi-select UI for linking Characters and Planets. Canvas apps support `Relate()` and `Unrelate()` functions.
Visibility & Data Access	CharacterPlanet is a visible table. You can use Dataflows, Power BI, and Excel to import/export links between Characters and Planets.	The intersect table is hidden. Dataflows and standard imports cannot directly access or populate the Character–Planet links.
Performance & Scalability	Comparable performance. You can index the CharacterPlanet table and use alternate keys. Slightly more complex queries in canvas apps.	Slightly more optimized for relationship queries. Better for very large volumes of links.
Security & Role-Based Access	Fine-grained control. You can set CRUD permissions on CharacterPlanet and even use record-level security if needed.	Security is governed by Append/Append To rights on Characters and Planets. No direct control over individual links.
Maintainability & Extensibility	Fully extensible. You can add fields like “FirstEncounterDate” or “IsAlly” to CharacterPlanet. Easy to audit and trigger flows.	Cannot add fields to the relationship. No built-in support for converting to a custom table later.
Tooling and Integration	Power Automate can trigger on CharacterPlanet creation. Power BI can easily report on relationships.	Requires use of Associate/Disassociate actions. No trigger for when a Character is linked to a Planet.
Known Limitations & Quirks	Requires careful setup (e.g., cascade delete, alternate keys). UI is less streamlined in model-driven apps.	No extensibility. Limited visibility. Cannot trigger flows on link creation.

Deep Dive: Pros and Cons

Model-Driven Apps

Built-in N:N offers a smoother experience with multi-select dialogs and automatic subgrid behavior.
Custom table requires users to create each link manually, which can be tedious without custom UI enhancements.

Canvas Apps

Both approaches are supported.
Built-in N:N allows use of Relate() and Unrelate() functions.
Custom table requires manual creation and deletion of link records using Patch() and Remove().

Dataflows and Integration

Custom table is fully visible and accessible via Dataflows, Power BI, and Excel.
Built-in N:N is hidden and cannot be targeted directly by Dataflows or standard import tools.

Performance

Both approaches scale well.
Built-in N:N may offer slightly better performance for relationship queries.
Custom table allows indexing and alternate keys for optimization.

Security

Custom table allows granular control over who can create, read, or delete links.
Built-in N:N relies on permissions on the parent tables.

Maintainability

Custom table is extensible and future-proof.
Built-in N:N is rigid. If you need to store metadata about the relationship later, you’ll need to migrate to a custom table.

Tooling

Custom table integrates easily with Power Automate and Power BI.
Built-in N:N requires special handling and lacks triggers for relationship events.

Migration Considerations

There is no built-in support for converting between the two approaches. If you start with one and need to switch, you’ll need to manually migrate the data and update your apps and flows.

Final Recommendation

If you need visibility, control, and integration with tools like Dataflows, Approach 1 is the better choice.

If your priority is simplicity and UI convenience in model-driven apps, and you’re certain the relationship will remain simple, Approach 2 may suffice.

Bonus Tip

If you’re unsure about future requirements, lean toward Approach 1. It’s easier to ignore unused columns than to rebuild your data model later.

Dataflows Part 4 - LookUp Column with Multiple Values (Many-to-Many Relationships)