Skip to main content

Notifications

Service | Customer Service, Contact Center, Fie...
Suggested answer

Contact to Contact to Account Table Relationship Design

Posted on by 5

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

  1. Clients (those who are participants in the Early Cancer Detection Programs)
  2. Providers (Doctors, Nurses, ...)
  3. 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

  • ThatGuy306 Profile Picture
    ThatGuy306 5 on at
    RE: Contact to Contact to Account Table Relationship Design

    Thanks Leah, that was what I would have done in the past. I was messing around with relationships last week and yeah it got ugly fast. However, I really think we should be using the Contact Entity for our Clients otherwise we will be missing out on most of the benefits that come with Dynamics CRM. It may be easiest to just create a Custom Provider and Facility Enitity however that would simplify the relationship issue a lot.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Contact to Contact to Account Table Relationship Design

    Hi ThatGuy306,

    My suggestion is to create three custom entities instead of using OOB account and contact entities.

    There are some OOB relationships between account and contact entities, creating custom relationships would make the scene confusing.

    1.Create custom entities:

    Create an entity in Dynamics 365 Customer Engagement (on-premises) | Microsoft Docs

    2.Build relationships:

    Types of entity relationships in Dynamics 365 Customer Engagement (on-premises) | Microsoft Docs

    (1) Clients can have many Providers, Providers can have many Clients

    N:N relationship between Client and Provider

    (2) Providers work out of many different Clinics

    N:N relationship between Clinic and Provider:

    (3)but one will be the Primary Provider; but one Clinic will be the Primary Clinic

    1:N relationship between Provider and Client, 1:N relationship between Clinic and Provider 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,912 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,355 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans