Dataverse Tables and Relationships

Analogy categoryAnalogies

What Is a Dataverse Table?

A Dataverse table is a structured store of records, organized as rows and columns, much like a database table. Microsoft ships standard tables such as Account and Contact with prebuilt columns and business logic, and you can also create custom tables (for example, Project or Invoice) tailored to your organization's data. Every table has a primary column that acts as the human-readable name shown in lookups and views.

🏏

Cricket analogy: Just as a scorecard table lists every batter as a row with columns for runs, balls, and strike rate, a Dataverse table lists every record as a row with columns for its attributes.

Column Types and Data Modeling

Columns in Dataverse are typed: text, whole number, decimal, currency, date/time, choice (option set), lookup, and two computed types — calculated columns (evaluated on read from a formula) and rollup columns (aggregated asynchronously from related records, such as summing all open Opportunity amounts for an Account). Choosing the right type matters because it drives validation, storage, and how the column behaves in formulas and reports.

🏏

Cricket analogy: A player's strike rate is a calculated column derived from runs and balls faced, while career average might be a rollup summed across every innings, just as Dataverse computes calculated and rollup columns differently.

One-to-Many Relationships

A one-to-many (1:N) relationship connects a single parent record to many child records through a lookup column on the child table. For example, one Account can have many related Contact records, each Contact row carrying a lookup column that points back to its Account. This lookup column also determines cascading behavior — what happens to child records when the parent is deleted, reassigned, or shared.

🏏

Cricket analogy: One IPL franchise like Mumbai Indians has many contracted players, each player record carrying a lookup back to the franchise, the classic one-to-many pattern.

Many-to-Many Relationships

A many-to-many (N:N) relationship lets records on both sides associate freely, such as Contacts attending multiple Events and Events having multiple Contacts. Dataverse implements this with a hidden intersect table that stores pairs of record IDs; you typically manage N:N relationships through related grids or subgrids rather than a visible lookup column, and there is no cascading delete behavior to configure because the intersect rows are simply removed when either side is deleted.

🏏

Cricket analogy: Many players can play for many different teams across their careers in T20 leagues, and many teams field many players, an N:N relationship tracked by an intersect table of player-team pairs.

Cascading Behavior and Referential Integrity

Each 1:N relationship exposes cascading rules for actions like Assign, Share, Reparent, Delete, and Merge, configurable as Cascade All, Cascade Active, Cascade User-Owned, Remove Link, or Restrict. Restrict behavior blocks deletion of the parent while child records exist, protecting data integrity, while Cascade All on Delete means removing the parent also removes every related child record, which can be destructive if configured carelessly on a heavily used relationship.

🏏

Cricket analogy: If a team is disbanded mid-season, a Restrict rule is like refusing to release contracted players until they are reassigned elsewhere, preventing orphaned player records.

javascript

// Power Fx: look up related Contacts for the selected Account in a Canvas app
ClearCollect(
    RelatedContacts,
    Filter(
        Contacts,
        'Company Name'.Account = SelectedAccount.Account
    )
);

// Patch a new Contact and set its parent Account lookup
Patch(
    Contacts,
    Defaults(Contacts),
    {
        'First Name': "Priya",
        'Last Name': "Nair",
        'Company Name': SelectedAccount
    }
);

The primary column (often named 'name') is what appears in lookup search results and subgrids, so choose a value that is unique and meaningful — for a custom Invoice table, an auto-generated invoice number makes a far better primary column than a generic 'Title' field.

Changing a 1:N relationship's cascading behavior from Restrict to Cascade All after the table is already in production can silently enable mass deletion of child records; always test cascading changes in a sandbox environment before applying them to a live Dataverse database.

A Dataverse table stores records as rows and typed columns, similar to a relational database table.
Standard tables like Account and Contact ship with Dataverse; custom tables model your own business entities.
Calculated columns compute a value on read; rollup columns aggregate related records asynchronously.
One-to-many relationships use a lookup column on the child table to reference a single parent.
Many-to-many relationships are stored via a hidden intersect table pairing record IDs from both sides.
Cascading rules (Cascade All, Restrict, Remove Link, and others) control what happens to child records on delete, assign, or share.
The primary column should hold a unique, meaningful value since it drives lookup search and subgrid display.