Solved: Hierarchy of many-to-many relationships and tables...

mhomol · ‎10-25-2023

Hey all,

I'm once again turning to the community for some advice or guidance on the direction I see a database going, based on the rules I've heard thus far. I wanted to review a potential hierarchy here and see if anyone can confirm that it's a sound approach and what, if any, risks I might encounter within the context of a model-driven app.

At its core, the point of the system is to connect patients with potential studies that they might be interested in, based on their existing conditions. So you have 4 primary entities (tables) that need to come together:

Patients (the candidates for a study)
Physicians (the doctors they are currently seeing, who also must approve whether the patients can be contacted)
Studies (any available studies)
Locations (both the locations that physician see patients as well as where a study can be conducted)

Patients can have multiple Physicians and vice versa, so there's a Physicians-Patients mapping table.

Studies take place at Locations, and can be conducted at multiple sites at a time and locations can also have multiple studies going on at a time, so there's also a Studies-Locations mapping table as well. (There's also a Patients-Locations mapping table, but I don't think that really enters in here - more of a reporting thing)

As mentioned, the goal is to create a final association of all 4 entities to create what is essentially a screening opportunity. As I see it, I have 2 primary approaches to take here for the Screening table:

2 lookup columns to the Physicians-Patients table and the Studies-Locations tables. This feels like the correct approach, as it confirms the other relationships already exist, but now I'm traversing 2 layers of heirarchy for the actual related data. Is this going to limit me in a model-driven app? I guess this approach would also have to assume that the mapping rows already exist in the case of a dataflow as well. Am I right?
4 lookup columns to the individual tables, with business rules or realtime workflows that confirm that the 2 separate relationships exist before allowing an insert into my Screening table. This feels suboptimal and less performant on data entry and especially bulk data load, although the other doesn't feel great either for bulk data load

Are there other avenues I should be exploring and what are the risks for me with these approaches?

Bonus problem:

Additionally, physicians have to approve whether or not a patient can be contacted and screened for a study. So this almost implies a separate mapping table for tracking just this one Yes/No field - Physician-Patient-Study Authorizations. My current thinking here is to make this action a workflow that traverses the Screening table and sets a Can Contact field to Yes/No within the table and assume that Can Contact is always No at first. I can't think of a clean way of also including a traversal to this kind of mapping table when I already have all of the mapping mentioned above.

FLMike · ‎10-25-2023

@mhomol

Please note that this seems I know like a great writeup and its a great very initial level, so there are many things missing and way too many to explain all of them here, as I have been doing this for decades (even for MS for customers). I also personally created the first and most of the Health Care accelerator for Dynamics / Dataverse so at least I can provide some thoughts.

Here are some thoughts inline

@mhomol wrote:
Hey all,
I'm once again turning to the community for some advice or guidance on the direction I see a database going, based on the rules I've heard thus far. I wanted to review a potential hierarchy here and see if anyone can confirm that it's a sound approach and what, if any, risks I might encounter within the context of a model-driven app.

At its core, the point of the system is to connect patients with potential studies that they might be interested in, based on their existing conditions. So you have 4 primary entities (tables) that need to come together:
Patients (the candidates for a study)
Physicians (the doctors they are currently seeing, who also must approve whether the patients can be contacted)
Studies (any available studies)
Locations (both the locations that physician see patients as well as where a study can be conducted)
Patients can have multiple Physicians and vice versa, so there's a Physicians-Patients mapping table.
--or use the M:M relationship or you may wish to use Connections. It would depend on future features you want. Using the intersect table as you will work fine too (and N:N/M:M uses them it is but it's hidden from you. So, you have more control if you do it
yourself.

Studies take place at Locations, and can be conducted at multiple sites at a time and locations can also have multiple studies going on at a time, so there's also a Studies-Locations mapping table as well. (There's also a Patients-Locations mapping table, but I don't think that really enters in here - more of a reporting thing)

Before I go down I believe you might have (at least some):

M:M Patients to Studies / Studies to Patients
M:M Patients to Physicians / Physicians to Patients
M:M Studies to Studies (or at least a 1:N, as I have to believe some studies can be linked to either other studies that are active or parent studies)
M:M Studies to Locations / Locations to Studies
M:M Patients to Locations / Locations to Patients
M:M Physicians to Locations / Locations to Physicians
1:N Locations to Addresses (many locations can have multiple Addresses especially medical places)
-also 1:N phone numbers for addresses for locations

Also, I don't see anything here related to Results, where Studies can have multiple results and patients too.
M:M Study / Results (studies have many results)
M:M Patients to Results (patients can have many results from different studies)

Also for studies, do they have multiple phases, so you would need to track results by phase?

You mention screening below, but I don't really see any where that you are capturing the screening details. Those don't really go into the Patient Table, so I would add that because I am guessing that there is a 1:M relationship between a Patient and Screenings? before they actually make it into a study? This way you can track the outcome of a screening, which I dont feel is the patient table, especially since its many.

The better the break down the better you can do automated searches etc to match people. Later a study can say "give me a list of all the patients, who were accepted into studies that have XYX characteristics

As mentioned, the goal is to create a final association of all 4 entities to create what is essentially a screening opportunity. As I see it, I have 2 primary approaches to take here for the Screening table:
2 lookup columns to the Physicians-Patients table and the Studies-Locations tables. This feels like the correct approach, as it confirms the other relationships already exist, but now I'm traversing 2 layers of heirarchy for the actual related data. Is this going to limit me in a model-driven app? I guess this approach would also have to assume that the mapping rows already exist in the case of a dataflow as well. Am I right?
4 lookup columns to the individual tables, with business rules or realtime workflows that confirm that the 2 separate relationships exist before allowing an insert into my Screening table. This feels suboptimal and less performant on data entry and especially bulk data load, although the other doesn't feel great either for bulk data load
Are there other avenues I should be exploring and what are the risks for me with these approaches?

You definitely need to be careful on the level of indirect hierarchy as it is limited. Which means sometimes you won't be able to use the . (dot) notation to get to something even though the UI will have it right there for you. Instead, you would have to do a lookup, making the relationship, from a usage perspective, useless.

Bonus problem:
Additionally, physicians have to approve whether or not a patient can be contacted and screened for a study. So this almost implies a separate mapping table for tracking just this one Yes/No field - Physician-Patient-Study Authorizations. My current thinking here is to make this action a workflow that traverses the Screening table and sets a Can Contact field to Yes/No within the table and assume that Can Contact is always No at first. I can't think of a clean way of also including a traversal to this kind of mapping table when I already have all of the mapping mentioned above.

--You may need many things, some which are transcient and others that are not (in most states at least and for federal compliance)
1. Is a per study contact value of yes/no and also if they can be contacted for marketing etc versus results etc
2. A big red button (we called it at MS), where there is a single value that says DO NOT CONTACT ME PERIOD. Legally you cannot simply make them answer all of them separately but provide a way to turn off all communications (ok I am talking about US so I have no idea where you are but I'd check)

So you would go if the big red button pressed, dont contact, No? can we contact about this study etc

Anyway those are some of my thoughts, I think I'll stop there for now

View solution in original post

FLMike · ‎10-25-2023

@mhomol

Please note that this seems I know like a great writeup and its a great very initial level, so there are many things missing and way too many to explain all of them here, as I have been doing this for decades (even for MS for customers). I also personally created the first and most of the Health Care accelerator for Dynamics / Dataverse so at least I can provide some thoughts.

Here are some thoughts inline

@mhomol wrote:
Hey all,
I'm once again turning to the community for some advice or guidance on the direction I see a database going, based on the rules I've heard thus far. I wanted to review a potential hierarchy here and see if anyone can confirm that it's a sound approach and what, if any, risks I might encounter within the context of a model-driven app.

At its core, the point of the system is to connect patients with potential studies that they might be interested in, based on their existing conditions. So you have 4 primary entities (tables) that need to come together:
Patients (the candidates for a study)
Physicians (the doctors they are currently seeing, who also must approve whether the patients can be contacted)
Studies (any available studies)
Locations (both the locations that physician see patients as well as where a study can be conducted)
Patients can have multiple Physicians and vice versa, so there's a Physicians-Patients mapping table.
--or use the M:M relationship or you may wish to use Connections. It would depend on future features you want. Using the intersect table as you will work fine too (and N:N/M:M uses them it is but it's hidden from you. So, you have more control if you do it
yourself.

Studies take place at Locations, and can be conducted at multiple sites at a time and locations can also have multiple studies going on at a time, so there's also a Studies-Locations mapping table as well. (There's also a Patients-Locations mapping table, but I don't think that really enters in here - more of a reporting thing)

Before I go down I believe you might have (at least some):

M:M Patients to Studies / Studies to Patients
M:M Patients to Physicians / Physicians to Patients
M:M Studies to Studies (or at least a 1:N, as I have to believe some studies can be linked to either other studies that are active or parent studies)
M:M Studies to Locations / Locations to Studies
M:M Patients to Locations / Locations to Patients
M:M Physicians to Locations / Locations to Physicians
1:N Locations to Addresses (many locations can have multiple Addresses especially medical places)
-also 1:N phone numbers for addresses for locations

Also, I don't see anything here related to Results, where Studies can have multiple results and patients too.
M:M Study / Results (studies have many results)
M:M Patients to Results (patients can have many results from different studies)

Also for studies, do they have multiple phases, so you would need to track results by phase?

You mention screening below, but I don't really see any where that you are capturing the screening details. Those don't really go into the Patient Table, so I would add that because I am guessing that there is a 1:M relationship between a Patient and Screenings? before they actually make it into a study? This way you can track the outcome of a screening, which I dont feel is the patient table, especially since its many.

The better the break down the better you can do automated searches etc to match people. Later a study can say "give me a list of all the patients, who were accepted into studies that have XYX characteristics

As mentioned, the goal is to create a final association of all 4 entities to create what is essentially a screening opportunity. As I see it, I have 2 primary approaches to take here for the Screening table:
2 lookup columns to the Physicians-Patients table and the Studies-Locations tables. This feels like the correct approach, as it confirms the other relationships already exist, but now I'm traversing 2 layers of heirarchy for the actual related data. Is this going to limit me in a model-driven app? I guess this approach would also have to assume that the mapping rows already exist in the case of a dataflow as well. Am I right?
4 lookup columns to the individual tables, with business rules or realtime workflows that confirm that the 2 separate relationships exist before allowing an insert into my Screening table. This feels suboptimal and less performant on data entry and especially bulk data load, although the other doesn't feel great either for bulk data load
Are there other avenues I should be exploring and what are the risks for me with these approaches?

You definitely need to be careful on the level of indirect hierarchy as it is limited. Which means sometimes you won't be able to use the . (dot) notation to get to something even though the UI will have it right there for you. Instead, you would have to do a lookup, making the relationship, from a usage perspective, useless.

Bonus problem:
Additionally, physicians have to approve whether or not a patient can be contacted and screened for a study. So this almost implies a separate mapping table for tracking just this one Yes/No field - Physician-Patient-Study Authorizations. My current thinking here is to make this action a workflow that traverses the Screening table and sets a Can Contact field to Yes/No within the table and assume that Can Contact is always No at first. I can't think of a clean way of also including a traversal to this kind of mapping table when I already have all of the mapping mentioned above.

--You may need many things, some which are transcient and others that are not (in most states at least and for federal compliance)
1. Is a per study contact value of yes/no and also if they can be contacted for marketing etc versus results etc
2. A big red button (we called it at MS), where there is a single value that says DO NOT CONTACT ME PERIOD. Legally you cannot simply make them answer all of them separately but provide a way to turn off all communications (ok I am talking about US so I have no idea where you are but I'd check)

So you would go if the big red button pressed, dont contact, No? can we contact about this study etc

Anyway those are some of my thoughts, I think I'll stop there for now

mhomol · ‎10-26-2023

@FLMike , I can't thank you enough for your response. It's great to get input from someone with as much experience in both Dynamics and healthcare as you have. You're correct that I left out some important details for some of the other tables that are most likely necessary. I particularly love your callout of a ScreeningDetails table. I was planning on just having the details present in the Screening table itself, but it sounds like you're recommending putting the details into a child table. Is that correct?

As for the Screening table, I was originally thinking that the Screening table would be best to setup with Lookups to the Physician-Patient mapping table and the Study-Location mapping table, but after playing around with this, it seems like it doesn't get me much - I was going down this path because I thought it would make regular dataflows simpler but I don't think that's even the case, plus in a model-driven app, with this approach, I wouldn't be able to easily print out any relevant related field data from the actual source tables. So I've landed on making my primary Screening table a unique mapping of Patient, Physician, Study, Location. This gives me the ability to easily print out any relevant related data. I think it will make dataflows more complex, as they will need to ensure that the csv being uploaded is honoring the other mappings that exist (Physician to Patient and Study to Location), but this feels like the right approach. Would you agree? I will add that I'm considering alternative approaches to the CSV upload, like Azure Data Factory, as I'd like to be able to provide end-users with the capability to reconcile issues with their upload easily.

I still need to read through more of your response, so I will probably reply with more, especially as it pertains to the communication authorization. This has been one of the trickier pieces for me. It feels like it just belongs on the screening table but the problem is that they want to have these authorizations made even before patient is recommended for a study.

Again, I'm so thankful for your response, as I'm sure you're a very busy person.

mhomol · ‎10-31-2023

@FLMike , since you have worked so much in the healthcare space, I wanted to run by you one other topic. This solution that I'm describing above is essentially to help screen prospective patients for studies, on behalf of a healthcare provider - known as a partner. These partners would have their own data, but that data could definitely all be brought into a single environment, separated out by Partner. We want to ensure that partners cannot see other partner's data. Would you recommend having a single environment and leveraging business units/teams to security trim everything per-partner or would you build a different environment per partner?