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.
@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
