SharePoint lists got me a long way, but the moment an app needs real relationships, server-side validation, and row-level security, a list starts fighting you. That's where Microsoft Dataverse earns its place — a managed, relational data platform purpose-built for the Power Platform. If you can model a database, you can model Dataverse; the trick is learning what it gives you above a plain table.
The vocabulary (it changed — use the new words)
Microsoft renamed the core concepts in 2020. The classic CDS/CRM terms still appear in the API, but in Maker Portal you'll see:
| You'll hear / see | Older term | It is… |
|---|---|---|
| Table | Entity | A set of rows (like a SQL table) |
| Column | Field / Attribute | A typed property of the table |
| Row | Record | A single item |
| Choice | Option set | A fixed pick-list of values |
A table is more than columns
When you create a Dataverse table, you don't start from empty — every table gets a primary key (a GUID, auto-generated) and a primary name column (the human-friendly label shown in lookups and search). On top of that you add your business columns, each with a real data type:
Table design: a GUID primary key, a primary name column, and typed business columns including a Choice and a Lookup.
Relationships: the whole point
Dataverse relationships are first-class and referentially enforced. There are three shapes:
- Many-to-one / one-to-many (1:N) — implemented as a Lookup column on the "many" side. A Contract has one Account; an Account has many Contracts.
- Many-to-many (N:N) — Dataverse maintains a hidden relationship (intersect) table for you; no junction table to build by hand.
- Self-referential — a table related to itself (e.g., a parent Contract amendment).
1:N relationship: the Lookup lives on Contract (the many side) and points back to Account.
Crucially, a 1:N relationship carries behaviour: choose Parental cascade and deleting an Account deletes its Contracts (and cascades assignment, sharing, and reparenting); choose Referential and the child simply keeps a reference. Getting this wrong is how people accidentally delete data — decide cascade rules deliberately.
Choices: local vs global
A Choice column is a fixed value set. You can define it locally (used by one column) or as a global choice reused across many tables. Reuse the global one for things like Status or Region so the options stay consistent everywhere and you maintain them in one place. Each option stores an integer value behind its label — handy to remember when you filter in Power Fx or query the API.
Logic that lives with the data
Dataverse pushes logic down to the data layer, so it runs no matter which app writes the row:
- Calculated columns — derive a value from other columns in the same row (or related parent), evaluated on read.
- Rollup columns — aggregate child rows (SUM/COUNT/MIN/MAX), e.g. Total contract value on an Account. They refresh on a schedule (roughly hourly) or on demand — not instantly.
- Business rules — no-code conditional logic (set a value, show/hide, make required, validate) that runs server-side and in model-driven forms.
Security is built in — and granular
This is where Dataverse leaves SharePoint lists behind. Access is governed by security roles that grant privileges (Create/Read/Write/Delete/Append/Assign/Share) per table, scoped by ownership: User, Business Unit, Parent-Child Business Unit, or Organization. Combined with row ownership and optional record-level sharing, you get true row-level security without writing a line of code.
Security role "Contract Manager":
Contract table → Read/Write/Append : Business Unit
Account table → Read : Organization
→ A manager sees every account, but only edits
contracts owned within their own business unit.The takeaway
Model Dataverse like the relational platform it is: typed columns, deliberate relationships with the right cascade behaviour, global choices for shared pick-lists, and logic (calculated, rollup, business rules) pushed into the data layer. Add the built-in role-based, row-level security and you have a foundation that a SharePoint list simply can't match for serious line-of-business apps. Get the model right and Power Apps, Power Automate, and Copilot Studio all just plug in on top.