Dataverse for Developers: Structuring Enterprise Data

What is Dataverse

Microsoft Dataverse is the data platform behind the Power Platform. Unlike a SharePoint list or a traditional SQL database, it combines relational storage with native layers of security, business logic, and integration.

If you’re building enterprise applications with Power Apps, understanding Dataverse is no longer optional.

Why use Dataverse instead of SharePoint Lists

SharePoint Lists work well for simple scenarios but hit limitations quickly:

• 12 lookup limit per view in SharePoint
• Limited delegation in Power Apps with SharePoint as a source
• No granular control over record-level permissions
• No native N:N relationships between lists

Dataverse solves all these problems with a full relational model, unlimited views, and native Security Roles.

Table modeling

When creating tables in Dataverse, follow these practices:

Publisher prefix naming

Every custom table and column should use your publisher prefix. If your prefix is pc, your tables will be pc_Project, pc_Task, etc. This prevents conflicts with third-party solutions.

Most useful column types

• Choice: For fields with fixed options (Status, Priority)
• Lookup: For 1:N relationships between tables
• Currency: For monetary values (auto-formats and converts currencies)
• Calculated/Rollup: For automatically derived fields

pc_Project (Table)
├── pc_name (Text, Primary Column)
├── pc_description (Multiline Text)
├── pc_status (Choice: Active, Paused, Completed)
├── pc_startDate (Date)
├── pc_endDate (Date)
├── pc_budget (Currency)
└── pc_owner (Lookup -> systemuser)

Relationships between tables

Dataverse supports three relationship types:

1:N (One-to-Many): A Project has many Tasks. Create a Lookup in the child table pointing to the parent.

N:N (Many-to-Many): A Project can have multiple Members, and a Member can participate in multiple Projects. Dataverse automatically creates an intersection table.

N:1 (Many-to-One): Many Tasks belong to one Project. This is the inverse of 1:N.

Security with Security Roles

The Dataverse security model operates at four levels:

1. Organization: Access to all records
2. Business Unit: Access to records in the same business unit
3. Parent-Child BU: Includes child units
4. User: Own records only

For each table, define Create, Read, Write, Delete, Append, and Assign permissions in the appropriate Security Role.

Integration with Power Apps

In Power Apps, connecting to Dataverse is native. Use Filter() and LookUp() with full delegation:

Filter(pc_Projects, pc_status = 'Active' && pc_owner = User())

Unlike SharePoint, all query functions are delegable in Dataverse, including Search(), Sort(), and CountRows().

Integration with Power Automate

Create flows that react to Dataverse changes:

• When a row is added: Trigger when a new record is created
• When a row is modified: Trigger on updates
• Perform a bound/unbound action: Execute custom logic

Final best practices

• Always use solutions to package tables, apps, and flows together
• Define alternate keys for integration scenarios
• Use Business Rules for simple validations (no code required)
• Enable auditing on critical tables
• Plan your Business Unit structure before implementing security