Use many-to-one (N:1) for deals linked to an account and many-to-many (N:N) for deals linked to competitors.
Key takeaways
- Confirm which table sits on each side of the relationship.
- Check existing relationships before creating another link.
- Decide cascade options before saving account relationships.
- Test account totals and competitor counts against the records.
Why the relationship type matters
Underneath, each link is a table relationship, and its type decides how many records can sit on each side, how your views group data, and how hard the setup is to change later. Pick the wrong type and you either limit each deal to a single competitor or end up copying the same competitor into many records.
This post walks through the two relationship types that cover most deal links. You will see why each one fits, how to check what your environment already has, and which mistakes cost teams the most time.
What a table relationship does
Dataverse stores business data in tables, and relationships connect the rows in one table to rows in another. Microsoft defines it this way: "Table relationships in Microsoft Dataverse define the ways that table rows can be associated with rows from other tables or the same table." (Microsoft's guide to table relationships)
In plain terms, a relationship answers one question: how many rows on one side can link to how many rows on the other side? Use that answer to choose the relationship type.
Before you choose, keep three points in mind:
- The type is a design decision. Changing it later can mean reworking links and the data attached to them, so settle it before you build dashboards.
- Direction matters. In a one-to-many link, the "one" side and the "many" side are not interchangeable, so confirm which table sits on each side before you build anything.
- Check eligibility for multiple links. Microsoft notes that "Each pair of tables that are eligible to have a 1:N relationship can have multiple 1:N relationships between them." (Microsoft's guide to table relationships)
That last point matters later, because a deal with a second account involved may not need a new kind of link at all.
Link each deal to one account with N:1
A many-to-one relationship fits the account side of a deal well. A deal belongs to one account, and that account can hold many deals over time, including a closed deal from last year and a new one this quarter. The deal table is the "many" side. The account table is the "one" side. A single lookup on the deal record points to the account, so the field holds one value and reporting stays simple.
You may see N:1 in the relationship designer and wonder whether it is a different kind of link. Microsoft explains that "The N:1 (many-to-one) relationship type exists in the user interface because the designer shows you a view grouped by tables." (Microsoft's guide to table relationships) In other words, N:1 is how the designer presents the link from the table that holds the lookup. It is the same one-to-many idea described above, seen from the deal's side.
Practical steps for account links:
- Put the lookup on the deal table, pointing at the account table.
- Name the relationship so anyone can tell its purpose. Admins who inherit the environment will thank you.
- If a deal needs two accounts, such as the customer and a separate bill-to party, add a second N:1 lookup instead of an N:N link.
- Decide the cascade behavior before you save. Microsoft defines a parental relationship this way: "A parental table relationship is any 1:N table relationship where one of the cascading options in the Parental column of the following table is true." (Microsoft's guide to table relationships)
That last step deserves a closer look. Microsoft's example uses a custom table linked to the account table. The first 1:N link can use the options in the Parental column. But Microsoft also notes that if you later add another 1:N relationship where your custom table is the referencing table, "you can only configure the actions to use the options in the Not Parental column." (Microsoft's guide to table relationships) So if you think a second account link will need the same cascade behavior as the first, check that before you add it. The order in which you create relationships can limit what each one can do.
Link each deal to many competitors with N:N
Competitors do not follow the account pattern. One deal can involve several rivals, and one rival can show up in dozens of deals. That is many-to-many, so the link needs an N:N relationship. A single lookup on the deal cannot hold a list of competitors, and a lookup on the competitor cannot hold a list of deals.
Dynamics 365 for Sales may already include the link you need. Microsoft notes that "The opportunity table in Dynamics 365 for Sales also has a N:N relationship with the competitor table, also available with Dynamics 365 for Sales." (Microsoft's guide to table relationships) If your team tracks deals as opportunities, check the relationships on that table before you build your own. A duplicate link adds clutter, and users will not know which one to use.
Why does this link matter so much to revenue leaders?
- Win and loss analysis. When each competitor links to the deals it appeared in, you can count how often you face a given rival and how often you win against it.
- Consistent names. Each competitor should be a real record, not free text. That keeps "Contoso Rival" and "Contoso Rival Inc" from splitting your data into two groups.
- Flexible lists. A deal can have one rival or four. The list grows with the deal, and no one has to decide in advance how many slots to create.
The common workaround is a set of text fields such as "Competitor 1," "Competitor 2," and "Competitor 3." They run out when a deal has one more rival than you planned for, they are hard to filter reliably, and they lose history when someone edits a name. A proper N:N link avoids all three problems.
Choose the right type with three questions
Run each link through these questions before you create it:
- Can one deal link to more than one record of this kind? If no, use N:1. Accounts usually land here.
- If yes, can one record appear on more than one deal? If yes, use N:N. Competitors usually land here.
- If a deal can link to many records but each record belongs to only one deal, the lookup belongs on the other table, not in an N:N link.
Then check whether the relationship already exists. Reusing an existing link is almost always better than adding a near-duplicate.
The questions also help you avoid two common mistakes:
- Using N:1 for competitors "to keep it simple." The first deal with two rivals breaks the model, and you end up with a second field, then a third.
- Using N:N for accounts because some deals involve two customers. An N:N link lets a deal have many accounts and each account have many deals, which makes ownership and totals harder to read.
Illustrative example: check the links on test deals
Some deals also involve other competitors.
Link each deal to the account with N:1. Link each deal to its competitors with N:N, reusing the competitor records instead of typing names into text fields.
Check those associations against the test records before relying on a report. Adding another rival should change the associations, without requiring another competitor text field.
Reporting on each link
The reporting payoff is where the choice shows. With N:1 on accounts, grouping is simple. Each deal sits under exactly one account, so the totals add up without double counting.
With N:N on competitors, the questions change. You can ask how many deals a given competitor appeared in, what share of those you won, and which rivals show up most often in your largest deals. A deal with three rivals counts once for each rival. That is what you want for competitor analysis, but it would be wrong for a total of deal value.
Keep these two views apart in your dashboards. Deal value totals should group by account, and competitor win rates should group by competitor. Putting both on one chart is an easy mistake, and it produces numbers that look plausible but do not add up.
Before you publish a report, test it against a few deals you can check by hand. Pick one account, one competitor, and one deal with two rivals. Confirm that the counts and totals match what you see on the records. For related checks, see reconcile sales dashboard totals.
What sales reps see on the deal
Reps care about speed. The account lookup is a single field, so it is quick. A rep types part of the customer name, picks the record, and moves on. Keep the field in a visible spot on the form, and make sure account names are distinct enough to find quickly.
The competitor subgrid needs more thought. Reps should be able to add a rival in a couple of clicks and remove one that was added by mistake. If adding a competitor means leaving the deal, many reps will skip the step, and your win and loss data will have gaps. Place the subgrid where reps see it during deal reviews, not several tabs down.
Train reps on one habit: add every competitor you actually meet, including rivals that drop out early. A rival that appears in a deal for two weeks still matters to the story.
Keep the lists clean
An N:N link is only as useful as the list behind it. If reps can type any competitor name, you end up with duplicates, misspellings, and partial names that split your analysis. Decide who owns the competitor records, who can add new ones, and how a duplicate gets merged.
A simple approach works for most teams:
- Give one group, often sales operations, the job of approving new competitor records.
- Give reps a clear way to request a missing competitor, so they do not create a near-duplicate on the spot.
- Review the list on a regular schedule and merge duplicates when you find them.
Moving from text fields
Many teams start with a text field for competitors and an account name typed into the deal. That works for a while, then it breaks. Moving to relationships takes some planning, but the steps are straightforward:
- Export the current deal data, and list every distinct competitor name and account name.
- Clean the names. Merge spelling variants, and decide which name is the official one.
- Create the competitor records and the account links, then map each deal to its records.
- Keep the old text field visible but read-only for a period, so people can compare the old and new data.
- Retire the text field once the reports match the source records.
Plan for the cleanup to take longer than the technical change. The setup itself is quick. Agreeing on names is the slow part, and it is worth doing before the data moves. Before changing records together, review bulk-edit sales record checks.
Set up and test both links
Work through this checklist in a test environment before you change production:
- Confirm which table holds your deals and which tables hold accounts and competitors.
- Check the existing relationships on both tables, and reuse what fits.
- Name each relationship so future admins know its purpose.
- Decide the cascade options for each 1:N link before you save it, and remember that the order you create links can limit later options.
- Add two competitors to one test deal, and confirm both appear on each competitor record.
- Add two deals to one test account, and confirm the account shows both.
- Build one simple report for deals won and lost by competitor, and another for total value by account. Check that the totals match the records.
Testing with a handful of records catches most problems early. It is far cheaper to find a missing relationship in a sandbox than after a quarter of data has gone in.
Document the decision
Once you pick the types, write the decision down. A short record helps the next admin who opens the solution and wonders why competitors are linked differently from accounts. A useful note has four parts: the business question each link answers, the relationship type and the tables on each side, the cascade options you chose and why, and the reports that depend on the link.
Revenue leaders should sign off on the business questions, not the technical details. If the question is clear, the type usually follows.
Revisit the note when the sales process changes. If your team starts selling through partners, or starts tracking several customers on each deal, the account link may need a second lookup. Check the design against the note before you add it, so the change does not quietly alter a report that finance already relies on.
Today, open the relationships for your deal table in a test environment and record which account and competitor links already exist before creating anything.
Common questions
Can a deal have two accounts? Yes, separate N:1 relationships can represent different account roles, such as customer and bill-to account.
Should accounts use N:N too? Usually not. A deal has one customer in most cases, and N:N makes account reports harder to read.
Do I need to decide cascade behavior now? Yes, before you save the relationship. The Parental and Not Parental options depend on the table roles and on the order in which you add relationships.
What if a competitor only ever appears in one deal so far? The N:N type still fits. The design rule is about what is possible, not what happens today. A competitor that appears in one deal this year may appear in five next year.