Out-of-the-box vs. custom tables

  • 15 minutes

This unit describes the standard, out-of-the-box tables that are used in the configuration, along with custom tables and the purpose for which they are being used. This information matters because Microsoft Dataverse has many common tables and, as a general rule, a custom table should not be created if a standard table already exists that addresses that purpose. The reason is because if you overload the configuration with many redundant tables, you'll negatively impact the performance of the system, ultimately making the system more difficult to use (many redundant-sounding tables will confuse users in Advanced Find). Each custom table should serve a specific purpose. This topic will also help you identify the most-used tables and if you are at risk of overloading tables.

Determine whether to replace standard tables with custom tables

Occasionally, configurators will consider replacing standard functionality with custom tables. For example, configurators will often reason that if they need a sales opportunity but need a simpler form than the standard opportunity form, that a custom table might be easier. However, you should consider the options that you might be giving up by using a custom table instead of a standard table. Using an out-of-the-box table ensures greater alignment with core platform features. Because additional features are added regularly, standard tables make it easier for you to benefit from new features when they are released. For example, if you decided to replace the standard opportunity table with a custom opportunity table, you won't be able to use Sales Insights and other AI features.

For industry-specific tables, consider using Common Data Model. In addition to the metadata system, Common Data Model includes a set of standardized, extensible data schemas that Microsoft and its partners have published. This collection of predefined schemas includes tables, attributes, semantic metadata, and relationships. Microsoft is working closely with representatives from various industries to make Common Data Model more relevant to them by creating industry accelerators.

Industry accelerators are foundational components within Microsoft Power Platform and Dynamics 365 that enable independent software vendors (ISV) and other solution providers to quickly build industry vertical solutions. The accelerators extend Common Data Model to include new tables to support a data schema for concepts within specific industries. Microsoft is currently focused on delivering accelerators for the following industries:

  • Automotive
  • Financial services (including banking and insurance)
  • Education (including higher education and K-12)
  • Nonprofit
  • Manufacturing
  • Media
  • Communications

Do not re-create accounts and contacts

When deploying Dynamics 365, you will frequently track multiple types of companies, organizations, and contacts in the system. Some types represent customer/client organizations, some might be support and advisory organizations, such as accountants and legal firms, while others might be miscellaneous types of organizations, such as trade associations.

Consequently, you will need to determine how to manage multiple categories of company relationships. The most common approach is to use the account table for all organization types and then use a column like relationship type or custom choice columns to flag companies by their type or category. Views can be filtered based on the type of company, and business rules can conditionally show or hide column and form components based on type.

To benefit from a standard integration with finance and operations apps by using dual-write, your best option is to use the default tables and columns that are added by the dual-write core solution to your Dataverse environment.

Another approach is to create custom tables for each type of company. One reason that is commonly cited is that people might need to use accounts for another reason in the future, so they don't want to customize the account table.

Before re-creating the account table as a custom company table, you should strongly consider the options that you are giving up by creating a custom table, such as:

  • Multiple addresses - The account table has a unique address capability that supports multiple addresses. The first two addresses are displayed on the company form, but these address records live in the related customer address table. While you can create a custom address table that is tied to a custom company table, re-creating the unique logic where the addresses are stored in the related table and are displayed on the form means that table views would require development. If you need multiple addresses, use the account table.

  • Contact hierarchy - Accounts are the parents of contacts. Activities that are related to contacts roll up to the parent account record. This hierarchy can't be replaced by a custom company record. You can create additional relationships with custom company tables, but the standard account/contact relationship can't be replaced. If the company has contacts with their primary company relationships to this type of company, or if you want to roll up activities from contacts to companies, use the account table.

  • Company tables - The standard map control in model-driven apps doesn't support custom company tables.

  • Hierarchical relationships - Relationships between parent/child accounts and the standard hierarchy visualization and roll-up of child account activities to the parent account only work with standard account tables.

  • Customer column - Dynamics 365 includes a special type of polymorphic lookup column called a customer column. This column allows a record to be linked to a company/account or a contact. Dynamics 365 doesn't allow for custom tables to be selected from polymorphic lookup columns.

  • Marketing - Marketing lists can only work with contacts, accounts, and leads, not custom tables. Microsoft Dynamics 365 Marketing can send to accounts and contacts, but not custom company tables.

In almost every situation, the account table should be used for company records of all types, with the following exceptions:

  • Minor types of companies that are not relational and have minimal attributes. Think of a type of organization with no contacts and address and that only exists for lookup purposes.

  • Unqualified or unverified companies that are imported from business cards or web forms that you don't want to pollute the account table. For these situations, you could use the lead table.

Determine whether to repurpose system tables

Consider a scenario where you have a business requirement that is similar to opportunities, but it isn't a sales opportunity. In this scenario, you need to determine whether to repurpose system tables or create new tables.

The following sections explain situations that you should consider before repurposing system tables.

Consider the future

The future of Dynamics 365 is moving much faster than ever before, so using tables in non-standard ways can cause problems if Microsoft changes the table that you are using. Also, if you choose to repurpose a seldom-used system table, such as contracts, you risk the chance that Microsoft will elect to deprecate that table in the future. Custom tables aren't deprecated. Also, if you repurpose a system table, you might have issues if you later need that table for its intended purposes. Scenarios have occurred where customers have repurposed a case and, when they needed case management at later time, had to address it with custom tables because the standard case table was already used for dramatically different purposes.

Consider the overhead

Numerous system tables have certain columns that can't be removed from the forms. For example, some columns on tables, such as opportunity, case, and campaign, can't be removed from the form. While you can hide these columns, having several locked columns on the form can add overhead to your environment configuration. 

Consider the user experience

If your use case is less than 50 percent in line with the standard table functionality, a custom table will typically give users a simpler user experience than scaling down a more complex system table. It's also possible to add business process flows to any table, including custom tables, which can make a custom table user experience as good as, or better than, repurposing a system table.