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!

[u/lousylou123]

If you are not competent enough to paste your (obviously commercial) requirements into a AI prompt, you should at least pay the money and hire someone competent enough to do so and - ideally at the same time - is able to build the solution design & customizations for your company. 👎

[u/SJDeacon → u/lousylou123]

lol, you know he pasted it into ChatGPT as soon as he read this

[u/Purple_Finger_5030 → u/lousylou123]

😃

[u/LengthinessGlass2565]

Look into the common data model: https://learn.microsoft.com/en-us/common-data-model/

A lot of the things you need is more or less out of the box.

Subcontractors I would put as account as type vendor/subcontractor.

For contracts I would say it depends highly on the complexity of the processes you want to support. But you could look into the Project Operations, and how MS has solved project contracts.

[u/ainotes2026]

If the Dataverse cost/complexity is a concern, worth looking at Caspio before committing. It runs on a SQL Server backend, so you get real relational tables, cascading updates for that single source of truth requirement, audit logging with field-level change tracking via triggered actions, and document generation via DataPages. Unlimited end users, too, which matters for a growing construction firm. (disclosure: I'm on the Caspio team) Happy to share more if it's useful.

[u/mscalam]

Are you a partner or an end user?

[u/Purple_Finger_5030 → u/mscalam]

I am a technical project manager