Learn how to design production-quality Dataverse schemas using the right relationship types, lookup configurations, and choice columns. This lesson goes beyond the basics to cover cascade behavior, referential integrity, and the lookup-vs-choice decision that determines your app's long-term maintainability.
You're building a project management app for a consulting firm. Projects have clients, clients have contacts, projects have tasks, tasks have assignees, and every project needs to track its status, priority, and billing category. If you model this in a spreadsheet, you'll have columns like "Client Name" repeated in every project row, status values that are free-text and inconsistent, and no real way to navigate from a task back up to the client without doing a VLOOKUP. It works until it doesn't — usually right around the time someone types "Active " with a trailing space.
Dataverse solves all of this through a structured relational data model with built-in relationship types, lookup columns that enforce referential integrity, and choice columns that constrain categorical data to a controlled set of values. But getting that model right requires understanding when to use which tool and why — because the choices you make here ripple through your forms, views, business rules, and security model.
By the end of this lesson, you'll be able to design a production-quality Dataverse schema from scratch. You'll understand the three relationship types and how to implement each one, how lookup columns work under the hood, when to use choice columns versus lookup tables, and how to avoid the most common modeling mistakes that come back to haunt you later.
What you'll learn:
You should be comfortable with Dataverse fundamentals — tables, columns, rows, environments, and solutions. If you need a refresher, the Dataverse Fundamentals: Tables, Columns, and Rows Explained for Power Apps Makers lesson covers everything you need before this one. You should also have access to a Power Apps environment with Dataverse provisioned, and ideally a solution already created to contain your work.
Before you open Power Apps Studio or the Power Apps Maker portal, you need to think in terms of relationships — not columns. A data model is really a map of how entities relate to each other, and Dataverse gives you three relationship types to express that map.
This is the most common relationship type you'll work with. One record in Table A can be associated with many records in Table B, but each record in Table B belongs to exactly one record in Table A.
In our project management example:
When you create a 1:N relationship in Dataverse, it creates a lookup column on the "many" side of the relationship. So when you define "One Project has many Tasks," Dataverse adds a lookup column called something like crf_project on the Task table. That lookup column stores the GUID of the related Project record.
This is the mechanism worth understanding clearly: the lookup column lives on the child table and points up to the parent. The parent table doesn't get a new column — it gains the ability to query related child records through the relationship.
Many records in Table A can relate to many records in Table B. The classic examples:
Dataverse implements N:N relationships by creating an intersect table (sometimes called a junction or bridge table) behind the scenes. You don't manage this intersect table directly — Dataverse handles the rows in it automatically when you associate and disassociate records.
Note
Native N:N relationships in Dataverse are elegant but limited. You can't add extra columns to the intersect table. If your relationship needs to carry its own data (for example, a "Project-TeamMember" relationship that needs to track "Role on Project" or "Hours Allocated"), you need to create your own custom intersect table with two 1:N relationships and store the extra data there.
A table can have a relationship with itself. This comes up more often than you'd expect:
Dataverse includes a built-in hierarchical relationship type for this — when you mark a self-referential relationship as hierarchical, you unlock special hierarchy-aware functions and visualizations in model-driven apps.
A lookup column is the physical implementation of a 1:N relationship on the child table. Understanding how to create and configure them well is the difference between a model that feels natural to use and one that confuses your users.
Navigate to your solution in the Power Apps Maker portal (make.powerapps.com). Go to your child table — let's use the Task table. Select the Relationships tab, then choose Add relationship > Many-to-one.
You'll specify:
crf_projectid — note Dataverse appends id to lookup column names automaticallyThis is one of the most overlooked configuration decisions when building relationships. Cascade rules define what happens to child records when the parent is deleted, assigned to another user, or shared.
The built-in behavior presets are:
| Behavior | Delete | Assign | Share |
|---|---|---|---|
| Parental | Cascade (delete children) | Cascade | Cascade |
| Referential | Restrict (block delete if children exist) | No cascade | No cascade |
| Referential, Restrict Delete | Restrict | No cascade | No cascade |
| Custom | You define each action individually |
For our Project → Task relationship, Parental behavior makes sense: if you delete a Project, its Tasks should go with it. But for a Project → Client relationship, you probably want Referential, Restrict Delete — you don't want deleting a Client to cascade and destroy all their Projects.
Warning
The default behavior in Dataverse is Referential, which means deleting a parent record sets the lookup column on child records to null — it doesn't delete the children or prevent the delete. This is often not what you want. Always review cascade behavior before saving a relationship.
By default, a lookup column on a Task form lets users search and select from every record in the parent table. In a real app with hundreds of projects, that's a poor experience and a potential security issue.
You can configure relationship filtering directly on the lookup column in the form editor. This lets you filter the lookup to only show records that match a condition — for example, only showing Projects where the Status is "Active." This filtering is done at the form level, not the data model level.
For more sophisticated filtering — like showing only projects that belong to the same client as the current user's last selected client — you'll configure dependent lookups in the form, which we'll cover when we get to forms in a later lesson.
Choice columns are Dataverse's mechanism for controlled categorical data. Instead of letting users type "High," "high," "HIGH," or "Hi" into a text field, you define a fixed list of values and users pick from a dropdown.
Every choice option has two components: a label (what users see) and an integer value (what's stored in the database). When you filter on a choice column in Power Automate, Power Apps formulas, or FetchXML, you're filtering on the integer value — so those numbers matter more than they look.
This is a decision that looks minor but has significant long-term consequences.
Local choice columns are defined within a single table. The option set values only exist on that table. You create them inline when adding the column.
Global option sets (also called global choices) are defined at the environment level and can be referenced by multiple tables. You create them under the Choices section in your solution, then select "Use existing global choice" when adding a choice column to a table.
Here's when to use each:
| Scenario | Use |
|---|---|
| "Status" values unique to one table | Local choice |
| "Region" used across Client, Project, and Invoice tables | Global option set |
| "Priority" with different meanings per table | Local choice per table |
| "Industry" used on Client and Contact tables | Global option set |
Key insight
If you think two tables might share a choice column in the future, build it as a global option set now. Migrating a local choice column to a global one later requires data migration work. The cost of building it global from the start is almost nothing.
When you create a choice column, Dataverse auto-assigns integer values starting from 1 (or from a publisher prefix range). Resist the temptation to accept sequential integers without thinking.
Consider a Project Status choice column:
Not Started = 100000000
In Progress = 100000001
On Hold = 100000002
Completed = 100000003
Cancelled = 100000004
This looks fine. But what if six months later you need to add "Pending Approval" between "Not Started" and "In Progress"? If your Power Automate flows or plugins check the integer value directly (which happens more than you'd like in enterprise environments), inserting a value breaks your existing logic.
A better practice: use gaps of 10 or 100 in your integers, which requires creating the choices manually rather than accepting auto-generated values.
Not Started = 100
Pending Approval = 150 ← added later, fits naturally
In Progress = 200
On Hold = 300
Completed = 400
Cancelled = 500
You can set custom integer values when creating choices in the Maker portal by editing the value field in the choice editor.
Dataverse also supports multi-select choice columns (called "Choices" plural in the column type dropdown). These let users select multiple values from the same option set.
Use cases:
Warning
Multi-select choice columns cannot be used as filter criteria in all Power Apps contexts. They're not fully delegable in canvas apps and have limited support in some view filter configurations. If you need to filter records based on a multi-select value frequently, consider whether a N:N relationship to a lookup table would serve you better. The lookup approach is more powerful but more complex to build.
Let's put this all together by designing the full schema for our consulting firm's project management app. This is the kind of multi-table design you'd actually build in production.
Here are our tables and their core columns (excluding standard columns like Created On, Modified On, Owner):
Client
Project
Task
Project Team Member (custom intersect table — replaces native N:N)
Tag (for categorizing Tasks)
Task Tag (intersect table for Task ↔ Tag)
Notice that instead of a native N:N between Project and System User, we built a Project Team Member table. This lets us store Role, Allocation, and date range on the relationship itself. That's information the native intersect table can't hold.
The tradeoff: you need to manage the Project Team Member records explicitly (create them, deactivate them, query them). With a native N:N, Dataverse handles the intersect automatically. The custom approach is more powerful but requires more design work.
Tip
When you create a custom intersect table like Project Team Member, give it a meaningful primary column — like an auto-number column called "Member ID" (e.g., PTM-00001) — rather than leaving the primary column as a text field that users have to manually fill. This makes records identifiable in lookup fields and audit trails.
Here's the recommended order for building this schema, which matters because you can't create a lookup to a table that doesn't exist yet:
Building in dependency order prevents you from having to come back and add relationships after the fact — though you can always add relationships later if needed.
Let me walk through a scenario that trips up most first-time data modelers.
You've built the Client → Project relationship with Referential behavior (the default). A user tries to delete a client record. Dataverse allows the delete — and sets the crf_clientid lookup column on all related Project records to null. You now have orphaned Project records with no client.
Is that what you wanted? Almost certainly not.
Your options:
Option 1: Use Restrict Delete behavior. The delete fails if child records exist. The user gets an error message and must either reassign or delete the child records first. This is the safest option for business-critical relationships.
Option 2: Use Parental (Cascade Delete). The parent delete cascades and removes all children. Use this only when the children are genuinely meaningless without the parent — like deleting audit log entries when a source record is removed.
Option 3: Use deactivation instead of deletion. Dataverse tables support a built-in Active/Inactive status. Instead of deleting a Client, you deactivate it. Related Projects remain intact and still have a valid client lookup. Business rules can prevent creating new Projects against an inactive Client. This is often the most elegant production approach — especially when you need an audit trail.
Key insight
In most enterprise Dataverse implementations, records are rarely hard-deleted. Design your data model assuming that deactivation is the primary "remove from active use" mechanism, and deletion is reserved for truly erroneous records. This simplifies your cascade configuration significantly.
Standard lookups point to exactly one table. But sometimes you need a lookup that can point to either a Client or a Contact or a Partner — depending on context.
Dataverse supports this through Regarding columns, which are polymorphic lookups present on tables like Activity (Email, Phone Call, Task, etc.). You can also create Customer type columns, which can point to either an Account or Contact record.
For custom tables, polymorphic lookups are available but less common. Most of the time, if you find yourself wanting a polymorphic lookup, it's worth reconsidering your data model — often a cleaner design with explicit separate lookups (one for Client, one for Contact, one nullable each) serves better than a polymorphic lookup.
This is the most common architectural decision you'll face in data modeling, and the wrong choice is surprisingly easy to make.
Use a choice column when:
Use a lookup column (to a separate table) when:
A concrete example: Task Status is almost always a choice column — it's "Open, In Progress, Blocked, Done" and that list is stable. But Project Category in a consulting firm might be a lookup table if each category has an associated billing rate, a responsible practice lead, and its own KPIs. The moment a value needs its own data, make it a table.
If you're building model-driven apps and want to understand how these decisions affect security and data access, the Power Apps Security: Roles, Sharing, and Data Permissions lesson covers how security roles interact with your table structure — and a well-designed data model makes security configuration dramatically simpler.
Build the project management schema described above in your own Dataverse environment. Follow this sequence:
Step 1: Create Global Option Sets
In your solution, navigate to Objects > Choices > New choice. Create:
crf_priority with values: Low (100), Medium (200), High (300), Critical (400)crf_region with values: North America (100), EMEA (200), APAC (300), LATAM (400)Step 2: Create the Tag Table
New table named "Tag" (schema name: crf_tag). Set the primary column to "Tag Name." Add a local choice column called "Category" with values: Technical, Process, Client-Facing, Internal.
Step 3: Create the Client Table
New table named "Client" (schema name: crf_client). Add columns:
crf_region — or create a separate global choice for industry)Tip
When creating a lookup to System User, the column type is "Lookup" and the related table is "User" (the internal Dataverse name for System User). This lookup works just like any other — users can search for colleagues by name when filling out forms.
Step 4: Create the Project Table
New table named "Project" (schema name: crf_project). Add:
crf_priority)Step 5: Create the Task Table
New table named "Task" (schema name: crf_task). Note: Dataverse has a built-in "Task" activity table — name yours something unambiguous like "Project Task" to avoid confusion.
Add:
crf_priority)Step 6: Create Project Team Member Table
New table: "Project Team Member" (schema name: crf_projectteammember). Set the primary column to an auto-number with format PTM-{SEQNUM:5}.
Add:
Step 7: Create Task Tag Intersect Table
New table: "Task Tag" (schema name: crf_tasktag). Auto-number primary column.
Add:
Verification: After building the schema, navigate to each table's Relationships tab and confirm the relationships are listed correctly. For the Project Team Member table, you should see two Many-to-one relationships — one to Project and one to User.
Once your schema is in place, you'll want to think about how users interact with it. The Model-Driven Apps vs Canvas Apps: When to Use Which Platform lesson can help you decide which app type best fits a relational schema like this one.
You'll see schemas where the Project table has a "Client Name" text column instead of a Client lookup. This feels simpler at first. Then the client changes their name, and you're updating it in 47 places. Or two projects have slightly different spellings of the same client, and your reports are wrong.
Fix: Always resolve repeated entities into their own table with a lookup relationship. If data describes a real-world entity that can change independently, it deserves its own table.
Status fields? Great as choices. Industry? Maybe. But "Sales Territory," "Product Line," or "Cost Center" — these often start as choices and then someone says "I need to add a manager to each territory" or "I need each product line to have a target margin." Now you're stuck.
Fix: Apply the "attributes test" before creating a choice: does this value need its own data? If yes, even hypothetically in the near future, make it a table.
Using the default Referential behavior on every relationship and discovering orphaned records when users delete parent records.
Fix: Review cascade behavior for every relationship you create. Document your decisions. For most business data, Restrict Delete is the safest default.
You create a native N:N between Project and User, then realize you need to track the role each user plays on the project. Native N:N can't hold that data.
Fix: You'll need to remove the native N:N and replace it with a custom intersect table. This is painful if records already exist. Think through whether you might need relationship-level data before committing to a native N:N.
You create a "Status" choice column on five tables. Each table's Status column has different values. Six months later, you're building a report and your query returns "Status" columns from multiple tables — and they're completely different option sets that happen to share a name.
Fix: Use table-specific naming for local choice columns: crf_clientstatus, crf_projectstatus, crf_taskstatus. For truly shared choices (like Priority), use global option sets with a single well-named definition.
A user reports that when they try to set a lookup field on a Task form, certain Project records don't appear in the search results.
Possible causes:
To check: go to the lookup column's configuration on the form and look at the "Related Records Filter" setting. Also navigate to Power Apps Security: Roles, Sharing, and Data Permissions to verify the user's role includes read access to the related table.
You try to delete a Project and get an error: "The object cannot be deleted because it is associated with another object."
This means a related record exists and the relationship cascade is set to Restrict Delete. This is actually correct behavior — it's protecting your data.
Fix: Either delete or reassign the child records first, then delete the parent. Or, reconsider whether deletion is appropriate at all — deactivating the record instead might be a better business process.
Your data model decisions directly affect query performance at scale. A few principles:
Lookup columns are indexed automatically. When Dataverse creates a lookup column, it creates an index on that column. Filtering and sorting by lookup columns is efficient. This is one reason why lookup tables outperform free-text fields for categorical data — you're filtering on an indexed foreign key, not doing a text scan.
Choice columns are stored as integers. Filtering on a choice column compares integers, which is extremely fast. If you're filtering millions of rows by status, a choice column will outperform almost any other approach.
N:N relationships add a join. Every time you query through a many-to-many relationship, Dataverse joins the intersect table. For deep hierarchies or complex queries, this compounds. Keep your queries as flat as possible where performance matters.
Avoid deeply nested related records in canvas apps. In canvas apps, accessing Project.Client.AccountManager.FullName through multiple levels of related records can cause delegation issues. For model-driven apps, Dataverse handles these traversals server-side and they're generally fine. If you're building canvas apps that need to traverse multiple relationships, the Canvas App Delegation Deep Dive lesson covers strategies for keeping queries performant.
You now have a complete framework for designing a production-quality Dataverse data model. Let's recap the key decisions:
Relationship types: Use 1:N for most parent-child relationships (implemented via lookup columns on the child). Use native N:N for simple associations with no relationship-level data. Use custom intersect tables when the relationship itself has attributes.
Lookup columns: Always review cascade behavior — Restrict Delete is the safest default for most business data. Configure lookup filtering at the form level to improve UX. Consider deactivation over deletion as your primary "removal" workflow.
Choice columns: Use for small, stable categorical data that doesn't need its own attributes. Build as global option sets when shared across tables or likely to be shared in the future. Use integer gaps (10s or 100s) when creating custom values to accommodate future additions.
Schema design principles: Build tables in dependency order. Name columns specifically to avoid ambiguity across tables. Apply the "attributes test" before choosing choice vs. lookup table. Document your cascade behavior decisions.
From here, you'll want to build on this foundation by designing the forms and views that expose this data model to users, adding business rules that enforce data quality without code, and configuring security roles that respect your table structure. The Connecting Power Apps to SharePoint, Excel, and Dataverse: A Complete Integration Guide lesson is worth reading if you need to migrate existing data from spreadsheets or SharePoint lists into your new Dataverse schema — a very common real-world scenario once you've designed the model.
The data model you build today becomes the foundation everything else stands on. Time spent getting it right — understanding relationships, choosing the right column types, thinking through integrity rules — pays compound returns throughout the entire lifecycle of your app.