Dataverse Schema Design Power Platform Architecture

The Dataverse schema decisions you can't undo — and how to get them right first time.

A Microsoft Solutions Partner built the environment. The table had over 300 fields. Here's what that tells you about Dataverse schema design — and the decisions that look harmless in week one and become your biggest problem in month eighteen.

Consult Code Create · ~8 min read

You inherit a Dataverse environment. Or you come back to one you built eighteen months ago under delivery pressure. Something is wrong — forms are slow, queries time out, nobody can extend the model without breaking something else. Developers avoid touching it. The business works around it.

This isn't bad luck. It's the result of a small number of schema decisions made early that compounded quietly over time. The frustrating thing is that none of them were technically wrong at the time. They were fast. They were pragmatic. They worked in testing.

The cost of getting schema wrong is not paid immediately. It's paid later, with interest, usually by someone else.

The decisions

Five schema decisions that will cost you later.

Table bloat — everything on one entity.

The most common failure mode on inherited environments. Instead of thinking about the data model, someone added columns whenever a new requirement arrived. The result is a single table carrying the weight of an entire domain — hundreds of fields, no clear separation of concerns, and a form that takes four seconds to load.

Real world — Microsoft Solutions Partner delivery

We inherited an environment where a Solutions Partner had delivered a single core table with over 300 fields. No denormalisation. Related concepts — addresses, contacts, financial data — all flattened onto one entity. Form performance was unusable. Splitting the table required rebuilding every relationship, migrating data, and rewriting every flow and plugin that referenced it. Months of remediation for decisions that took minutes to make.

Currency fields where decimals would do.

Using a currency field type sounds like the right choice for any monetary value. It isn't — unless you actually need multi-currency support. Every currency field in Dataverse creates two columns by default: the value itself and a base currency equivalent. It also requires a currency lookup on the record. On a table with 30–40 monetary fields, that's 60–80 columns and a mandatory currency field bloating every single row.

Real world — the hidden column cost

The same environment had applied currency fields across the board — for every numeric value that represented money, regardless of whether exchange rates were relevant. The result was table bloat at scale, with dozens of redundant base currency columns serving no business purpose. If exchange rates aren't part of your requirements, use a decimal field. It's cleaner, lighter, and doesn't carry infrastructure you don't need. Converting currency fields to decimals once data is in them is a one-way door — it requires a full data migration.

Hard-coding relationships that need to scale.

Whenever you find yourself numbering fields — creditor_1, creditor_2, creditor_3 — that's the signal you need a related table with a one-to-many relationship, not additional columns on the parent. Hard-coded fields have a fixed ceiling. The moment the business needs one more than you anticipated, the model breaks and there is no incremental fix.

Real world — the seventh creditor problem

Seven creditor fields were added directly to the core table — creditor_1 through creditor_7. The assumption was that seven would always be enough. It wasn't. When an eighth creditor was needed, the model had no way to accommodate it without a structural change. A junction table with a proper one-to-many relationship would have made this a configuration change. Instead it became a schema rebuild. This came from a Microsoft Solutions Partner delivery.

Choice columns where lookup tables should be.

Choice columns are fast to create and easy to display on forms. That's why they get overused. The problems arrive when you need to associate additional data with each option, filter records across related entities, localise values, or allow the business to manage options themselves without a developer. At that point a choice column has no upgrade path — you have to migrate to a lookup table and update every reference.

Schema designed without the security model in mind.

This is the one that causes the most expensive rebuilds. Dataverse security operates at the row level via business units and teams. If the table structure doesn't reflect how data access needs to be controlled — which records belong to which part of the organisation, which teams need cross-unit visibility — you will discover the mismatch when the security model is implemented. Sometimes it can be worked around. Often it can't, and the schema has to change to accommodate it.

Getting it right

What to think about before you write a single column.

Denormalise intentionally — not accidentally.

Entity design before column design

Before adding a column, ask whether it belongs on this table or on a related one. Addresses, contacts, financial data, and status histories almost always belong on their own entities.
A table with 30 well-chosen columns on three related entities will always outperform a table with 150 columns on one. Both in performance and in maintainability.
Draw the entity model before you open the maker portal. Even a rough sketch on paper surfaces relationship decisions that are invisible when you're building column by column.

Choose field types for what they are — not what they look like.

Currency, decimal, integer — the choice has structural consequences

Use currency fields only when you need multi-currency support with exchange rate tracking. If you're storing a price, a fee, or an amount in a single currency, use a decimal.
Every currency field creates two columns in the database and requires a currency lookup on the record. At scale, across many fields, this is measurable overhead with no business benefit if exchange rates aren't in scope.
Calculated and rollup columns are useful for simple derived values but carry their own performance implications at volume. When a value is queried frequently, store it explicitly rather than computing it on every read.

Design for the maximum, not the current state.

One-to-many relationships over numbered fields

If a concept could ever have more than one instance — creditors, contacts, addresses, notes, attachments — model it as a related table from day one.
The cost of building a junction table upfront is measured in an hour. The cost of not building it when the business needs an eighth creditor is measured in weeks.
Ask the business what the maximum realistic number is, then double it, then use a related table anyway. Storage is cheap. Schema rebuilds are not.

Use lookup tables where the business owns the data.

Choice columns are developer-owned. Lookup tables are business-owned.

If the business will need to add, rename, or retire options over time without developer involvement, it needs a lookup table — not a choice column.
Lookup tables can carry additional data: a code, a description, an active flag, a sort order. Choice columns cannot. The moment you need any of these, the migration cost begins.
Choice columns are appropriate for genuinely fixed, developer-owned lists — statuses, types, categories that are defined by the system and unlikely to change. Everything else should be a lookup.

Design the schema with the security model in the room.

Security is not a post-schema concern

Before finalising any table structure, ask: which business unit owns this record? Which teams need read access across units? Does row-level security need to be enforced at the table level or the column level?
If the answers to those questions require a different table structure than the one you're building, change the structure now. It is significantly cheaper than changing it after data is in and integrations are built on top of it.
The security model post covers this in depth — but the principle is simple: schema and security are not separate decisions. They are the same decision made at different moments.

The unglamorous one

Naming conventions — cheap to get right, expensive to fix.

Nobody ever prioritises naming conventions. They get agreed on day one and then quietly ignored by the third sprint. The result is an environment where half the columns use the publisher prefix, half use new_, field names abbreviate in ways only the original developer understood, and nobody coming in afterwards can navigate the schema without help.

This matters more than it sounds. Naming convention inconsistency compounds across flows, plugins, integrations, and external API calls. Renaming a field in Dataverse doesn't rename the schema name — the underlying column name is permanent. You can change the display name but the logical name that every piece of code references is set at creation.

✓Agree a publisher prefix and use it everywhere from day one

✓Use full words in schema names — creditor_name not crd_nm

✓Document what each table and key column represents — one line is enough

✓Never use new_ prefix on a production environment

✓Review naming in code review before anything is merged

✓Assume someone unfamiliar will read this schema in 18 months

The cost of slowing down is measured in days.

None of these decisions are hard to get right. They are hard to get right under delivery pressure when a stakeholder wants to see something working and the path of least resistance is to add another column.

The environments that become unmaintainable were not built by bad developers. They were built by developers who were moving fast and not thinking about what the schema would look like at twice the size, with a different team, after eighteen months of changing requirements.

Take an extra day at the start. Draw the entity model. Ask the security question before it's inconvenient. The cost of slowing down at schema design is measured in days. The cost of not slowing down is measured in months of remediation — if it's fixable at all.

Coming next

"Your Dataverse security model is probably wrong — and you won't find out until it's too late to fix it cheaply."

Business units, teams, row-level and column-level security — and why the order you build them in matters as much as what you build.