Dynamics Community Forum Thread Details

Contact to Contact to Account Table Relationship Design

First this is my first time posting and I am definitely very new to Dynamics and the CDS. I am a noob so apologize if asking the obvious.

We are building a Application using Dynamics Customer Service to replace the legacy software used for managing clients for 3 Early Cancer Detection Programs. At it's heart it is very much a CRM thus why we went with Dynamics. Anyway, I haven't worn a Technical Architect Hat in a very long time but find myself jumping back into the role temporarily after my previous Architect quit and from what I can tell left a mess. I'm basically revisiting and questioning pretty much everything. Anyway to get to the point I'm looking for some advise on how best to handle the following.

We have 3 foundational Entities

Clients (those who are participants in the Early Cancer Detection Programs)
Providers (Doctors, Nurses, ...)
Clinics (locations were Doctors work out of and Clients visit)

In General the Relationship between these Entities is as Follows

Clients can have many Providers but one will be the Primary Provider and be the one who receives Test Result Correspondence
Providers can have many Clients
Providers work out of many different Clinics but one Clinic will be the Primary Clinic and as such the one were we send Test Result Correspondence

Back in the day without trying to work within the constraints of Dynamics and the CDS I would have simply done the following

Great a Provider Table, a Client Table, and a Clinic Table
Great a Provider-to-Client Relationship Table to handle a many-to-many relationship between Provider and Client were one is marked as Primary
Great a Provider-to-Clinic Relationship Table to handle a many-to-many relationship between Provider and Clinic where one is marked as Primary

However, I'm not entirely sure the best way to do this with the CDS and Dynamics Customer Service. My first instinct is to:

Store Providers and Clients in the Contact Table and add column, Contact Type, were the options are (Client or Provider)
Store Clinics in the Account Table

Where I am struggling is how to do the relationships.

Should I do a Many-to-Many thing between records in the Contact Table to link Providers and Clients? I'm thinking I should use a Custom Table so I can add the Primary Column to identify which one is Primary. I thought of just using the Connection Entity to create a Connection between Providers and Clients but it's use strikes me as confusing to the end users and there is a Business Rule were if they make one Provider the Primary the system should automatically unflag any previous provider that was marked Primary (not sure how to do that with Connections).

I think the Provider Clinic Relationship is more straight forward and I would do either something similar as done above between Providers and Clients but with would be between the Account Table and the Contact Table or I could just trust in the CDS Power Apps created many-to-many relationship?

Any advise would be appreciated as this is the foundational relationships so I want to make sure we do it in the best way for simplicity to understand, flexibility, and future scalability.

I should note that my Development Team is even less experienced than myself with Dynamics. They have solid SQL Server Relation Database experience so I'm trying to do this in way that makes sense to them.

Thanks