A maker creates a custom table for “Project” with 15 columns and a one-to-many relationship to Account. Six months later the business asks for projects under projects, projects across multiple accounts, and a per-project budget that rolls up to a portfolio. The original model was correct for the original requirement and inadequate for the second-quarter need. Data model decisions compound; the early ones constrain the late ones.
Tables
Standard tables (Account, Contact, Lead) come with Dynamics. Custom tables hold your own entities. Activity tables (Task, Phone Call, Email) integrate with timeline. The decision to extend a standard table versus build a custom table is the first model choice and the most reversible if you make it deliberately.
Extend a standard table when:
- Your concept is a specialization of the standard
- You want timeline, audit, and security to apply automatically
- The standard table's relationships are useful
Build a custom table when:
- The concept does not fit any standard
- The lifecycle is fundamentally different
- You need a clean schema for integrationsColumns
Text, Number, DateTime, Choice, Lookup, File. Choice columns (single or multi) replace picklists. File columns store binary attachments alongside records. The column type decision is hard to reverse; changing a column from Text to Choice loses the historical free-text data.
Column type guide:
- Text for free-form notes and identifiers
- Number for counts, money for currency, decimal for precision
- DateTime with timezone for events
- Choice for finite enumerated values
- Lookup for relationships to other tables
- File for documents under 128MB
- Image for photos and diagramsRelationships
1:N (one Account, many Contacts), N:1 (lookup from Contact to Account), N:N (many-to-many, intersect table). Cascade behaviors (delete, reassign, share) are configurable per relationship. The cascade behavior decision is where most modeling errors land; default cascade often deletes child records the user did not intend to lose.
Cascade settings:
- Cascade All: child follows parent in every action
- Cascade Active: only active children follow
- Cascade User-owned: only children owned by the same user follow
- Restrict: parent cannot be deleted while children existFor master-detail relationships (Order to OrderLine), use Cascade All with Restrict on Delete. For loose associations (Account to Contact), use Cascade None.
Business Rules
Declarative if-this-then-that on forms. Faster than JavaScript, runs server plus client. Use for simple field-level logic; escalate to Power Fx or plug-ins for complex rules. The business rule sits between the schema and the form; it enforces field interactions without code.
Business rule example:
If Type = Renewal then make Original Account required
If Status = Closed then make Resolution Reason required
Default Industry from Parent Account on createModeling Advice
Model your business, not the vendor. Extend Account, Contact, Lead if your business objects map to them. Build custom tables when standard ones do not fit. The most common mistake is forcing every concept into Account or Contact; the second most common is building a custom table for a concept that is genuinely a Contact in disguise.
Modeling questions to ask:
- What is the lifecycle of this entity?
- Who owns it and who can see it?
- What does it relate to?
- What changes drive notifications or workflows?
- What does it look like in five years?Choice Columns vs Lookup Tables
A choice column with 20 options performs better than a lookup to a 20-row table; a choice column with 200 options is harder to maintain than a lookup. The crossover is roughly 30 options. Above that, build a lookup table; below that, use a choice.
Naming Conventions
Adopt prefix conventions and stick to them. Custom tables prefixed with your publisher prefix. Custom columns named with the entity context. Solution-aware naming makes the schema readable to the next developer.
Examples:
- ctn_project (custom table, Contoso publisher)
- ctn_project_estimatedbudget (custom column, prefixed for readability)
- pjm_milestone_completed (Project Management module convention)Audit and Change Tracking
Enable audit on tables that drive compliance, financial reporting, or contractual obligations. Skip audit on high-volume operational tables; audit storage adds up fast. Use change tracking instead for integration-driven sync, which is lighter.
What to do this week
Map your top three custom tables to the standard tables, confirm the cascade settings make sense, and audit choice columns for any with more than 30 options. Document the naming convention on the wiki and require reviews on schema changes.
