Dataverse table design is the part of a Dynamics 365 project that nobody gets excited about, and it is the part that decides whether the next three years are smooth or miserable. Forms can be rebuilt in an afternoon. A badly designed data model can take months to unpick, because by the time you notice the problem there is live data sitting in it.
I have lost count of the projects where I have been brought in to fix slow views, broken reports or security that makes no sense, only to find the root cause sitting in how the tables were set up on day one. Here are the mistakes I see most often.
Building One Giant Table For Everything
The most common mistake is the kitchen sink table. Someone needs to track a new thing, cannot be bothered working out how it relates to the rest of the system, so they bolt another forty columns onto an existing table. Within a year the Account table has columns for contracts, onboarding, complaints and renewals, and nobody can explain half of them.
The fix is to ask what the thing actually is. If it has its own lifecycle, its own owner or its own security needs, it is probably its own table with a lookup back to the parent. Splitting it out early costs an hour. Splitting it out after eighteen months of data costs a migration project.
Use The Standard Tables Before Inventing Your Own
Dataverse already ships with Account, Contact, Case, Opportunity and plenty more. These come with built in relationships, views, timeline support and integration hooks that Microsoft and third party tools expect to find. Create a custom table called Customer and you have thrown all of that away for no good reason.
My rule is simple. If a standard table is roughly right, use it and extend it. Only go custom when the concept genuinely does not exist, and even then, check twice.
Getting Relationships Wrong
Relationships are where Dataverse table design gets properly interesting, and where people make expensive errors. A one to many relationship is easy. The trouble starts when someone needs many to many and picks the wrong approach.
Native many to many relationships are fine for simple tagging, like linking contacts to marketing lists. But the moment the relationship needs its own data, such as a start date, a role or a status, you need an intersect table of your own with two lookups. I have seen teams try to cram that extra data into text fields on one side of a native relationship, and it never ends well.
Pick The Relationship Behaviour Deliberately
Every relationship has a behaviour setting that controls what happens on delete, assign and share. The defaults are not always what you want. Cascade delete on the wrong relationship can wipe out a customer's entire history when someone deletes a parent record by mistake.
Take ten minutes per relationship and decide on purpose. It is boring, and it has saved me from some very awkward phone calls.
Ignoring Choice Columns, Calculated Fields and Rollups
Free text columns are the enemy of clean reporting. If a field has a known set of values, make it a choice column. Otherwise you will end up with "Manchester", "manchester", "Manc" and "Manchester " all in the same view, and your Power BI dashboards will be useless.
Calculated and rollup columns are worth knowing about too. A rollup that shows total open opportunity value on the account saves users opening five records to work it out. Just be sensible, because rollups run on a schedule and too many of them across big tables will cause pain. I covered the wider performance side in my piece on Dynamics 365 performance problems.
Forgetting About Security From The Start
Table ownership is a decision you cannot easily reverse. A table is either user or team owned, or organisation owned, and changing that later usually means rebuilding the table. If you think different business units will ever need to see different records, make it user or team owned from day one.
It also pays to think about how your table design lines up with your roles. I wrote about this in my Dynamics 365 security roles guide, and the short version is that security is far easier to design when the data model was built with it in mind.
Naming Things Badly
This sounds trivial and it is not. Table and column logical names are permanent once created. Display names can change, but the schema name you pick in a rush on a Friday afternoon will be in every plugin, flow and integration for the life of the system.
Agree a publisher prefix, stick to a naming convention and write it down. Future developers, including future you, will be grateful. It also makes your solutions much easier to manage, which ties neatly into good application lifecycle management.
A Quick Checklist Before You Build
Before anyone creates a single table, I run through a short list with the team. It takes an hour and it is the best hour of the project.
Does a standard table already cover this? What are the relationships, and does any of them need its own data? Who owns the records, and does that need to change by team or business unit? Which fields are really choices rather than free text? What will we need to report on in a year's time?
If you cannot answer those questions, you are not ready to build yet. Sketch the model on a whiteboard first and argue about it. Arguing about boxes and arrows is cheap.
Get The Foundations Right
Everything else in Dynamics 365 sits on top of the data model. Plugins, flows, apps, reports and integrations all inherit its strengths and its flaws. Spend the time up front and the rest of the project gets easier.
If you are planning a build or inheriting a system that feels wrong, I am happy to review the data model before it costs you more. That is a big part of my Dynamics 365 consulting work, and a model review is far cheaper than a rebuild.
