Many-to-many relationships in Dataverse

Some real-world relationships are inherently many-to-many — students enrolled in courses, products tagged with categories, contacts associated with marketing lists, opportunities competing on the same deal. Dataverse supports many-to-many (N:N) relationships in two distinct ways — native N:N and manual intersection entity — each with different trade-offs.

Native N:N relationships.

The simplest approach. From the maker portal, define an N:N relationship between two tables:

• Select the two tables.
• Name the relationship.
• Optionally configure subgrids on each side.

Behind the scenes, Dataverse creates a system intersect table — a hidden table with two columns (one foreign key to each related table) that stores the relationships. The intersect table is invisible to most operations; users see the relationship as a list on each side.

Strengths of native N:N:

• Zero configuration overhead.
• Subgrid rendering on forms — display related records on either side as standard subgrids.
• Quick to add and remove relationships — simple Associate / Disassociate operations.

Limits of native N:N:

• No fields on the relationship itself. You can't say "Contact X is associated with Marketing List Y as a VIP starting date Z". The relationship is binary — exists or doesn't.
• Limited security — both endpoints' security applies; you can't add specific privileges to the relationship.
• No audit history on the relationship — adding / removing the link isn't audited at the relationship level (only at the endpoints).
• No business rules or workflows on the relationship — can't trigger on add / remove.

Manual intersection entity.

A more flexible approach. Create a custom table that represents the relationship:

• Custom table ContactMarketingList with lookups to Contact and Marketing List.
• Plus fields for role, start date, end date, notes, status, custom attributes.
• Each row represents one specific contact's association with one specific list, with the additional data.

Strengths of manual intersection:

• Rich relationship data — date, role, status, notes, anything you can model on a Dataverse table.
• Full audit — the intersection records audit normally.
• Business rules, workflows, plug-ins — trigger on add / remove / update of relationship.
• Per-relationship security — the intersection table has its own security; you can grant access to specific intersections.
• Reporting on relationship attributes — "show me all VIP associations created in Q3".
• Complex relationships — many roles, lifecycle states, custom attributes.

Limits of manual intersection:

• More schema — extra table, extra columns, more complexity.
• More effort to use — users add records to the intersection table rather than just associating.
• Different UI — typically a subgrid showing the intersection records, not a simple lookup.

Choosing.

• Native N:N when the relationship is simple and you just need to track "associated" — no extra attributes, no workflow triggers, no history matters.
• Manual intersection when the relationship has its own data, lifecycle, security, or business meaning.

Examples.

• Contact ↔ Marketing List — native N:N is fine. Contacts are on lists or they aren't.
• Worker ↔ Project — manual intersection. Each association has a role (Lead, Member, Consultant), allocation %, start / end dates, billable rate.
• Product ↔ Category — native N:N usually fine. Products are categorised; the relationship is uncomplicated.
• Customer ↔ Sales Rep — manual intersection. The association has a role (Primary AM, Backup, Inside Sales), commission split, effective dates.

Limits on cascade behaviour. Native N:N intersection tables have limited cascade configuration; manual intersection tables have full configurable cascade behaviour (Cascade, Restrict, Remove Link).

Performance considerations.

• Native N:N — efficient for simple lookups; the platform optimises.
• Manual intersection — queries traverse the intersection table; performance similar to any custom table with appropriate indexes.

Both scale well for moderate relationship counts (thousands per parent); very heavy N:N (millions of relationships) needs careful indexing and query design.

Querying N:N relationships.

• Native N:N — Web API supports $expand over the relationship; OData query syntax is straightforward.
• Manual intersection — query the intersection table directly with $filter on the lookup fields; $expand to fetch related records.

Common pitfalls.

• Native N:N chosen, then needing relationship attributes — the migration to manual intersection requires data migration and code changes. Choose deliberately at design time.
• Manual intersection without proper indexes — queries slow as relationship volume grows.
• Cascade behaviour wrong — deleting a parent without considering cascade on intersection produces orphans.

Operational reality. Native N:N is the quick answer; manual intersection is the right answer for any non-trivial relationship. Make the choice deliberately based on what the relationship actually represents.