Columns

Good column (field) design is essential for building a clear, maintainable, and consistent data model. Well‑named columns improve usability, reporting, integration, and long‑term support. This guidance explains how to define columns in a consistent way across DfE solutions.

Naming conventions

Use consistent, meaningful names for all columns. Good naming helps users understand what a column is for and helps developers identify columns in code and integrations.

Best practice:

  • Use plain English, clear and descriptive names (e.g. “Provider type” rather than “PType”)
  • Use singular names (e.g. Application Status, not Application Statuses)
  • Avoid abbreviations and internal programme jargon
  • Do not include the table name in the column name
  • Use consistent patterns (e.g. Start Date, End Date, Application Reference)

Technical schema name:

  • Use the dfe_ publisher prefix unless there is a specific environment requirement
  • Schema names should be lowercase and meaningful

Microsoft guidance emphasises reviewing schema names to ensure they are clear and predictable for API or automation references.

Column descriptions

Column descriptions help everyone understand the purpose of a column, especially when working across multiple teams or after long periods of time.

Best practice:

  • Provide a meaningful description for every non‑system column
  • Describe the columns purpose and how it should be used
  • Include any business rules or constraints users should be aware of

All columns should have a meaningful description to allow data dictionaries to be created and maintained.

Choosing the right column type

Dataverse offers a range of column types. Choosing the correct one ensures the right behaviour and avoids future migrations.

Common column types and when to use them:

Text (single or multiline)

Use for free‑text information such as names, comments, references or descriptions.

Number (whole, decimal, floating point)

Use for numeric values such as counts, scores, or measurable attributes.

Date/Time

Use for any date or time‑based logic.

Yes/No (Two‑options)

Use for clear binary choices such as Is active? or Requires approval? Avoid using text columns for yes/no values.

Choice columns

Use for small, finite sets of predefined options (e.g. Status, Application type). Ensure options are unambiguous and meaningful.

Lookup columns

Use for linking to another table (e.g. Provider, Person, Case). Lookups allow reuse of shared reference data and maintain data integrity.

Lookup columns are also sometimes used instead of Choice columns when values may change and there is a requirement to make it more configurable as reference data instead of a hardcoded set of values.

Choice columns vs lookup columns

Choosing between a choice column and a lookup column affects reuse, relationships and reporting.

Use a choice column when:

  • The list of options is small and fixed
  • Options do not require additional data
  • Options do not need to be shared across multiple tables

Use a lookup column when:

  • The values may change over time
  • The value represents a real entity, such as an organisation, team, region or programme
  • The item has additional attributes (e.g. provider name, UKPRN, address)
  • You want to avoid duplicating values across the system

Lookup columns ensure data consistency and prevent hardcoding lists of values across different tables.

Relationships between tables

Relationships define how data connects across the system. Microsoft’s Dataverse guidance highlights the importance of designing relationships as part of an overall structured data model.

Best practice:

1:N relationships

Use when one record relates to many others (e.g. an Organisation with multiple Applications). This is the most common and recommended structure.

N:N relationships

Use sparingly. Only use when both sides can relate to multiple items and no additional data exists on the link itself. This type of relationship does come with limitations around form design and can be confusing for users when searching data.

Relationship configuration

Choose the appropriate behaviour:

  • Parental → Child records are reassigned or deleted when the parent record is reassigned or deleted
  • Referential → Child records remain but the link clears
  • Restrict delete → Prevent deletion when related records exist
  • Cascade → Propagate changes such as owner or delete

Use and test cascade behaviours carefully - they can have unintended consequences with data being unintentionally deleted.

Additional best practices

Avoid unnecessary columns

Before creating a new column, check if an existing one already captures the same information.

Keep columns generic

Avoid creating columns that only one team can use unless absolutely necessary.

Use standard (OOTB) patterns

Whenever possible, follow established Dynamics 365 design conventions (e.g. using “Status” and “Status Reason” columns).

Review security

Check whether the column contains sensitive information and whether additional permissions or column‑level security is needed.

Consider reporting

Consider how the column will be used in Power BI or exported reporting. Choice columns, for example, produce both text and numeric values which may impact reporting design.

Further reading

Create new data columns in Dataverse (opens in a new tab) is Microsoft’s primary guidance page for developers working with columns.