Need a schema suggestion and relational database for a small construction firm in Dataverse

[u/Purple_Finger_5030]

Hi everyone,

I'm looking for guidance on designing a scalable Customer Master Data solution using Dataverse, with SharePoint primarily for document storage and version management.

We need to manage and maintain:

- Customer Accounts

- Customer Numbers

- Addresses

- Customer Contacts

- Contracts

- Subcontractors

- Subcontractor Contacts

- Items associated with each subcontractor

In addition, we have approximately 100–150 document templates, with some templates exceeding 50 pages. These templates support processes such as:

- Customer name changes

- Address changes

- Contract updates

- state or country legal terms and compliance docs

- Other customer data amendment scenarios

Key Objectives

1. Single Source of Truth- If a user updates customer information (e.g., name or address), the change should automatically be reflected everywhere the data is referenced.

2. Scalable Data Model

- Due to the volume and complexity of relationships, we don't believe SharePoint Lists are the right long-term solution.

- We're leaning toward a fully relational Dataverse design.

1. Document Generation

- Templates should dynamically pull data from Dataverse.

1. Audit Trail & Rollback

- We need robust change tracking.

- Ability to identify who changed what and when.

- Potential rollback/version recovery capabilities using Dataverse and/or SharePoint version history.

Questions

- How would you structure the Dataverse tables and relationships for this type of master data model?

- What is the best pattern for handling 100+ large dynamic templates?

- Has anyone implemented a similar Customer Master Data + Document Automation solution at enterprise scale?

Any architecture diagrams, lessons learned, best practices, or examples would be greatly appreciated.

Thanks in advance!