5 Common Mistakes in Dataverse Modeling
The data model is the foundation of every Power Platform project. A well-designed Dataverse model ensures fast applications, reliable reports, and easy maintenance. Unfortunately, in practice we regularly see the same mistakes recurring. In this article, we discuss the five most common mistakes and provide concrete advice on how to avoid them.
Mistake 1: Too Many Fields in One Table
One of the most common mistakes is creating tables with dozens or even hundreds of columns. This often happens when all information that is even remotely related to an entity is stored in the same table. The result is a cluttered table that performs poorly and is difficult to maintain.
The solution is normalization: distribute related but distinct information across separate tables. A customer table does not need to also contain all order information, communication preferences, and financial data. Create separate tables and link them through relationships. Rule of thumb: if a group of fields is only relevant in a specific context, it deserves its own table.
Mistake 2: Incorrect Use of Choice Column vs Lookup Table
A common dilemma is the choice between a choice column (option set) and a lookup table. Many makers default to a choice column because it seems simpler, but this often leads to problems in the longer term.
Use a choice column when:
- The list is short and rarely changes (for example: status values, gender, priority)
- You do not need to store additional information per option
- The list should be modified by an administrator, not by end users
Use a lookup table when:
- The list is long or changes regularly
- You need additional fields per item (for example: product categories with description and image)
- End users should be able to add items themselves
- The data needs to serve as a dimension in reports
A good data model is like the foundation of a house: you cannot see it, but everything you build on top of it depends on it. Invest the time to get it right from the start.
Mistake 3: No Attention to Security in the Data Model
Security is often treated as an afterthought, while it should be an integral part of your data model. Dataverse offers a powerful security model with security roles, business units, teams, and field-level security. If you do not think about this at an early stage, it can become very complex to configure afterward.
Our recommendations:
- Define security roles in parallel with the data model, not afterward
- Use business units to model organizational boundaries
- Implement field-level security for sensitive data such as salaries or social security numbers
- Test your security model with realistic scenarios before going to production
- Document your security architecture so it is transferable
Mistake 4: Insufficient Indexing
As your dataset grows, performance becomes an increasingly important concern. A common mistake is not creating indexes on columns that are frequently used in searches, filters, and sort operations. Without the right indexes, queries that were initially fast can slow down dramatically when the table contains thousands or millions of rows.
Identify the columns most frequently used in views, dashboards, and Power Automate queries. Create appropriate indexes for these columns. Keep in mind that too many indexes can also be detrimental, as each index must be updated with every write operation. It is all about finding the right balance.
Mistake 5: No Strategy for Historical Data
The fifth common mistake is the lack of a strategy for historical data. Many organizations keep all records indefinitely in their active Dataverse tables, which leads to growing datasets that negatively impact performance and increase storage costs.
Develop a clear data strategy that answers the following questions:
- How long should active records be available in Dataverse?
- When are records archived and where to?
- What data must be retained for compliance purposes?
- How is archived data made available for reporting?
Dataverse now offers built-in archiving features and integration with Azure Data Lake for long-term storage. Take advantage of these to keep your active dataset manageable.
Conclusion
A good data model is the foundation for every successful Power Platform project. By avoiding these five common mistakes, you lay a solid foundation for scalable, performant, and maintainable applications. At Breathbase, we help organizations design and optimize their Dataverse architecture. Contact us for a review of your current data model or guidance on a new project through our consultancy services.
