Advice on how to build data model with Many-to-Many relations

(2) Share

Report

Posted on by ar87

353

Hi all,

I understand the concept of many-to-many relationship and have general idea how to design my database. However, while still building the logical data model, I would love to get some advice from the community and before move on with the actual database creation see if I have everything under control.

Long story short, I have three entities that have many-to-many relationships between them. Those entities are: Hospital, Study and Doctor.

I am thinking to define Study entity as junction table, here is why:

One or more doctors can conduct one or more studies. At the same time that doctor(s) can conduct one or more studies at different hospitals
One or more hospitals can order one or more same studies, so those studies can be conducted at different hospitals as well

Two other entities and relations are directly connected to Study in the following way:

ResearchCenter creates Study (1:N)

Study is defined by Protocol (1:1)

Here is the draft of only that part of data model:

Does that make sense to define Study as the junction table? If yes, I am not sure how it is done in Dataverse, since quick google search for "composite key in Dataverse" pointed to "alternate key". I will do a research on that, but maybe if someone could say if those two are the same?

If defining Study as junction table is not a good idea, what would be better approach? Create separate junction table? Or?

This data model will be used in both Power Apps and Power BI, and I just wanna be sure that I planned and designed it as best as I could so in future I don't have to make any major changes.

Thnx!

Best

All responses (6)

Answers (0)

ar87 353 on at

Like (1)

Report

Advice on how to build data model with Many-to-Many relations

Thnx Joel for the advice! I did build a model following that approach.

@CU22072148-2 I used MS Visio to build the logical model and then replicated that in Dataverse manually. Not sure that Dataverse has an option of logical data model import.
Joel CustomerEffective 3,224 on at

Like (2)

Report

Advice on how to build data model with Many-to-Many relations

Yes--make study a junction table. Any time you need a N:N style relationship that needs additional data points, take the "manual N:N" approach.
CU22072148-2 on at

Like (1)

Report

Advice on how to build data model with Many-to-Many relations

Hi,

I am interested in knowing what software you are using for the data map. I am struggling to find one that works will with Power Platform to design my model-driven application.

Appreciate your time.

Ronda
ar87 353 on at

Like (1)

Report

Advice on how to build data model with Many-to-Many relations

Thank you @bscarlavai33 and @ivan_apps for your suggestions.

However, my data model got quite complex now and new requirements are introduced. Let me try to explain it:

Instead of Hospital being direclty related to Study as explained firstly, now I am introduced with another entity called HospitalStudy. HospitalStudy should be a junction table between Hospital and Study. Anyhow, the challenge here is that also Doctors do not look at Study entity anymore but to HospitalStudy in a many-to-many relationship. Same goes for Patients, Patients are in a many-to-many relationship with HospitalStudy entity. I was wondering if the right approach to this would be to add two additional junction tables for HospitalStudy-Doctor relation and for HospitalStudy-Patient relation? If yes, is it advisable to relate two junction tables directly one to another?

Finally there is another table that should work as an aggregation table containing all data regarding the SiteStudy (Study protocols, SiteStudy configuration (budgets and fees, code, name, etc.), Doctors and Patients).

To summarize:

Hospital -> Study (M:N) = HospitalStudy junction table

Doctor -> HospitalStudy (M:N) = DoctorHospitalStudy junction table?

Patient -> HospitalStudy (M:N) = PatientHospitalStudy junction table?

StudyLogs -> aggregated table of all data related to a study, and that is: Study protocols, SiteStudy configuration (budgets and fees, code, name, etc.), Doctors and Patients

Is this approach doable? How will creation of such relationships affect PowerBI reporting? I tried replicating that in PowerApps and I was able to relate those tables in a way I explained above, but PowerApps app main purpose is to allow users to enter necessary data for Studies, Doctors, Patients, whereas PowerBI will be used for reporting and I don't want to provide my colleagues with difficulties if not necessary.

Thanks!
bscarlavai33 608 Super User 2025 Season 1 on at

Like (2)

Report

Advice on how to build data model with Many-to-Many relations

I agree with the design you have. It makes sense to have Study as a custom junction table. I use this approach often. Looks good to me.
ivan_apps 2,187 Super User 2025 Season 1 on at

Like (2)

Report

Advice on how to build data model with Many-to-Many relations

Yes alternate keys can make a combination of different columns an additional unique identifier aside from the GUID.

I’m not sure you need it unless you are trying to ensure the combination of this data to be unique. Simply defining an intersect table or a star schema won’t need an alternate key unless your requirements make it so.