Relationships in Dataverse
A good data model is the foundation of every successful Dataverse solution. The way you set up relationships between tables determines how you can query, display, and secure your data. Dataverse supports three types of relationships: one-to-many (1:N), many-to-many (N:N), and polymorphic. In this article, we explain each type, show when to use which type, and share best practices from real-world experience.
Why relationships matter
Relationships define how tables are connected to each other. They determine which lookup fields are available in forms, which subgrids you can display, how cascading behavior works when deleting or assigning records, and how you can build views and reports. An incorrectly set up relationship can lead to performance issues, data loss on deletion, or the inability to create the desired reports.
One-to-many relationships
The basics of 1:N
The one-to-many relationship is the most commonly used relationship type in Dataverse. A record in the primary table (the "one" side) can be related to multiple records in the related table (the "many" side). Technically, this is implemented by adding a lookup column to the related table that references the primary table. An example: one account can have multiple contacts. The "Company Name" lookup field on the contact table references the account table.
Cascading behavior
When creating a 1:N relationship, you configure the cascading behavior: what should happen to related records when the primary record is assigned, shared, unlinked, or deleted? The options range from Cascade All (all related records undergo the same action) to Remove Link (only the link is broken) to Restrict (the action is blocked if related records exist). Choose carefully, because the wrong cascading behavior can lead to unintended data loss or the inability to delete records.
Many-to-many relationships
N:N relationships explained
A many-to-many relationship is needed when records on both sides can have multiple links. A contact can be a member of multiple projects and a project can have multiple contacts. Dataverse implements N:N relationships via an automatically created intersection table that contains the link records. You do not need to manage this intermediate table yourself. In Power Apps, N:N relationships are displayed as subgrids where you can link and unlink existing records.
When N:N versus manual intersection table
The standard N:N relationship has a limitation: you cannot add extra attributes to the link itself. If you want to record the role or start date in the link between a contact and a project, you need a manual intersection table. This pattern (also called an associative entity) gives you full control over the relationship attributes and is more flexible for complex scenarios.
Choose the right relationship type based on actual business requirements, not based on what is easiest to implement. A wrong choice now leads to costly rebuilds later.
Polymorphic relationships
What are polymorphic relationships?
Polymorphic relationships are a unique concept in Dataverse where a single lookup field can reference records in multiple tables. The most well-known example is the "Regarding" field on the activity table: an email, phone call, or appointment can be related to an account, contact, opportunity, case, or any other table that supports activities. This is enormously powerful but also complex to work with.
Customer and regarding types
Dataverse has two built-in polymorphic field types: Customer (references Account or Contact) and Regarding (references a configurable set of tables). You can also create custom polymorphic lookups, but this is rarely recommended due to complexity. In Dynamics 365, polymorphic relationships are used extensively: the Customer lookup on an opportunity lets you choose between an account and a contact without needing two separate fields.
Best practices for data modeling
Based on our extensive experience at Breathbase, we share a number of best practices. Always start by mapping out your entities and their relationships on paper or in an ERD tool before you start building. Use 1:N relationships as the default and only switch to N:N when it is actually needed. Avoid circular relationships that lead to confusion and performance issues. Document the cascading behavior of each relationship and discuss it with the business. Test your data model with realistic amounts of data to discover performance issues early. A well-thought-out data model in Dataverse is the foundation for a scalable and maintainable solution that grows with the organization.
