Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities - Lets Get Started!
🌸May 8th | You're Invited: Community Spring Festival Awards Celebration🌸
May 14th | Power Platform Community AMA: Power Apps Edition
News and Announcements icon
Community site session details

Community site session details

Session Id : PNcpi4AfNupuBZoUq7rbhJ
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / Advice on how to build...
Power Apps - Microsoft Dataverse
Unanswered

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

Like (2) ShareShare
ReportReport
Posted on 16 Jul 2024 21:02:45 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:

sdedic_0-1721164964882.png

 

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

 

  • ar87 Profile Picture
    ar87 353 on 23 Jul 2024 at 19:46:02
    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 Profile Picture
    Joel CustomerEffective 3,224 on 23 Jul 2024 at 16:53:04
    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 Profile Picture
    CU22072148-2 on 23 Jul 2024 at 15:06:29
    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 Profile Picture
    ar87 353 on 23 Jul 2024 at 11:41:16
    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 Profile Picture
    bscarlavai33 583 Super User 2025 Season 1 on 23 Jul 2024 at 01:31:24
    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 Profile Picture
    ivan_apps 2,187 Super User 2025 Season 1 on 22 Jul 2024 at 19:44:45
    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. 

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

News and Announcements

Welcome to the Power Platform Communities - Lets Get Started!
🌸May 8th | You're Invited: Community Spring Festival Awards Celebration🌸
May 14th | Power Platform Community AMA: Power Apps Edition

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Microsoft Dataverse

#1
ankit_singhal Profile Picture

ankit_singhal 11 Super User 2025 Season 1

#2
mmbr1606 Profile Picture

mmbr1606 9 Super User 2025 Season 1

#2
stampcoin Profile Picture

stampcoin 9

Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans