Dataverse data model fundamentals

Microsoft Dataverse is the shared, managed data platform that sits underneath Dynamics 365's CRM-side apps and the Power Platform. It looks superficially like a SQL database, but it's a higher-level model — with built-in security, audit, business rules, and APIs — designed to be the data layer of an application rather than just a store.

Tables

A table holds rows (records) of one logical entity — Account, Contact, Opportunity, plus tables you create. Tables have standard ones (Microsoft-provided), custom ones (you create), and virtual ones (data sourced from outside Dataverse but exposed as if local). Every table has a unique name, a display name, and a primary key (GUID by default).

The first modelling decision on any project: reuse the standard tables or build custom ones? The strong default is to reuse. Account, Contact, Case, and the activity tables carry years of platform behaviour — duplicate detection, hierarchies, Outlook integration, out-of-the-box app support — that a custom new_company table forfeits entirely. Build custom tables for concepts Microsoft genuinely doesn't model (a Vessel, a Policy, an Inspection), not for renamed versions of things it does. The second decision is ownership type, set at creation and permanent: user/team-owned tables participate in row-level security (ownership, business units, sharing), while organization-owned tables are all-or-nothing per role. Reference data wants org-owned; anything with a "whose record is this?" question wants user-owned.

Columns

Columns are typed: text, number, decimal, date, choice (option set), lookup (foreign key), file, image, customer (polymorphic to Account or Contact), and several others. Required-ness is enforced by the platform.

Relationships

1:N, N:1, N:N, and self-referential relationships are all first-class. The platform also supports polymorphic lookups (a Customer column on Opportunity that can point to either Account or Contact). Relationships drive cascade behaviour: deleting a parent can cascade, restrict, or remove links to children — cascading delete in Dataverse covers each option and its data-integrity implications, and N:N relationships covers the many-to-many case specifically. Beyond standard lookups, connection records model record-to-record relationships that don't fit the schema's own relationship types.

Choices and choice sets

Picklists are modelled as choices (local to one column) or choice sets (reusable across columns and apps). Global choice sets are how you avoid duplicated Yes/No/Maybe lists across the schema.

The recurring design argument: choice column or lookup to a reference table? Use a choice when the list is small, stable, and needs no attributes of its own (order status, priority). Use a lookup table when business users must maintain the list, when values carry data (a country with a VAT rate), or when the list will grow — adding a choice value is a schema change that travels through solutions and deployments, while adding a reference row is just data. Teams that hard-code volatile lists as choices end up shipping a solution deployment for every new value.

Calculated and rollup columns

Server-side computed columns: calculated (formula evaluated at read time) and rollup (aggregate across related rows, refreshed on a schedule) — calculated and rollup columns covers the trade-offs between the two. A third option, formula columns, evaluates a Power Fx expression at query time; see formula columns for how they differ from both.

Business rules

No-code field-level logic — set value, lock field, validate — defined per form. Run on the client and the server. For process automation beyond field-level rules, classic workflows and the process designer is the older, still-supported alternative to Power Automate, and real-time vs background workflows covers the timing choice both patterns share. Named operations exposed as callable messages — the building block behind many of these — are custom actions.

Security

Role-based security with security roles that grant table-level privileges (create, read, write, delete, append, append-to, assign, share). Business units scope rows; teams group users for shared ownership — see owner teams vs access teams for the distinction — and field-level security restricts individual columns.

Auditing and history

Audit is a setting per table and per column; every change is recorded, viewable per record.

APIs

Dataverse exposes everything through OData v4 and a SOAP variant, with first-class SDKs for .NET, JavaScript, and Python. For read-heavy analytical or reporting workloads that want SQL rather than OData, the Dataverse TDS endpoint exposes the same schema over the tabular data stream protocol, and XRMToolBox is the community tooling ecosystem most admins and developers end up running against this same schema day to day.

Storage tiers

Database (default, fast, expensive), file (attachments, cheaper), log (audit, cheapest). Managing tier usage is the main cost lever for Dataverse capacity. For high-volume, low-value data — telemetry, event streams, IoT readings — elastic tables trade relational features for scale and cost, and belong in the model discussion before someone pours ten million sensor rows into a standard table.

Solutions and naming

Everything above is schema, and schema travels between environments in solutions. Two habits prevent permanent regret. First, create a proper publisher with a meaningful prefix before making anything — the prefix is baked into every logical name forever, and a schema full of new_ columns marks a system nobody planned. Second, build in a dev environment and deploy as managed solutions; hand-editing schema directly in production works right up until it doesn't. The mechanics live in ALM with managed solutions.

Design mistakes to avoid

• Rebuilding SQL habits. Fully normalising into many narrow tables punishes you in Dataverse — every join is a lookup, every lookup is a form control and a security boundary. Model at the granularity users think in.
• One mega-table. The opposite failure: a single table with 400 columns serving five loosely related processes. Forms slow down, security becomes unmanageable, and every team's change breaks another's.
• Ignoring ownership until security design. Retrofitting row-level security onto org-owned tables means rebuilding them. Read the security model before finalising tables, not after.
• Skipping alternate keys for integration. If external systems reference records, define alternate keys on the natural identifiers up front — upserts against GUIDs alone force every integration to maintain its own mapping table.

The data model outlives every app built on it. Apps get rebuilt in a season; a table structure with production data has real gravity — which is why the fundamentals here deserve more design time than any screen. For how the model surfaces to users, continue with canvas apps vs model-driven apps.