Dynamics 365 data models
Proper data modeling is critical for scalable Dynamics 365 implementations. My approach includes:
21 tables
The core of Field Service: work orders and everything around them, with the real Dataverse logical names and lookups.
Entity relationship design
- 1:N relationships: Parent-child relationships with cascading behavior configuration
- N:N relationships: Many-to-many associations using relationship tables
- Self-referencing: Hierarchical data structures (e.g., account hierarchies)
- Polymorphic lookups: Customer and regarding fields that reference multiple entity types
Data security & compliance
Multi-layered security model using security roles and privileges:
- Business units: Organizational hierarchy for data segmentation
- Security roles: Privilege-based access control at entity and record level
- Field-level security: Protect sensitive fields from unauthorized access
- Hierarchical security: Manager access to subordinate records
- Audit & change tracking: Compliance with data retention and audit requirements
Performance optimization
I apply these practices for optimal system performance:
- Indexing strategy: Indexing through alternate keys and Quick Find/Dataverse search columns; avoid leading wildcards and filters on calculated columns
- Query optimization: Efficient FetchXML and QueryExpression design
- Asynchronous processing: Background jobs for long-running operations
- Caching: Strategic use of caching for reference data
- Bulk operations: ExecuteMultiple and batch API calls for high-volume scenarios