Multiple Join Tables within an App? Mobile nurse example

Question

Hello QB Community,I'm trying to find the best way to go about multiple many-to-many relationships. To keep it brief,&nbsp; my company has mobile nurses that complete home health projects for pharmaceutical companies on a national level. The Nurses visit patients 'homes to collect samples, adminster drugs, and complete health assessments. The way our business is set up, we have the following relationships:1 Nurse can have many Projects1 Nurse can have many Home Visits (and possibly many Home Visits within the same Project)1 Project can have many Nurses1 Project can have many Clinics1 Clinic can have many Nurses (Geographically associated)1 Clinic can have many Patients1 Patient can have many Home Visits (Nurse association isn't as important on this level)My initial thought was to have one, large join table that housed these different relationships but I had limited success with that approach. I have attached my proposed diagram to visualize the different levels of operation. Blue tables are external parties, Green tables are my proposed join tables, and the Yellow table is my internal nurses.Has anyone had any luck using multiple join tables in this fashion? Can I still relate "Clinics" and "Nurses" if "Projects" is still the parent table intermediary? What qualifys as a look up vs summary field in this scenario?Are there limitations or risks to multiple join tables? Any help or thoughts are appreciated!&nbsp;

quickbasecoachd · Answer

Troy,
Your diagram seems to match your explanation of the relationships.

Quick Base can handle way more complexity that your diagram so no problem there. Many join tables are OK.

You can choose to make a relationship where 1 Clinic has Many Nurses. That will help because when you go to make a Home Visit, you will be able to use a Conditional drop down to choose the nurse as it can be filtered down to only showing the nurses associated with that Clinic.

As for what a summary field is in this scenario, it is still still a summary like a total or count of records below it, but it cannot easily summarize up any text fields like nurse's names. But that can be done with reverse relationships if the expected number of records to be "summarized" is reasonably small, like say 6 child records.

troypatton · Answer

Thanks for the feedback, I'll play around with the diagram some more and try it again in the app.

debbietaylor · Answer

Troy -You might want to create a table with multiple joins ... a many-to-many-to-many type of thing.&nbsp;&nbsp;One question first:&nbsp; What is a project?&nbsp; If it's a contract OR a set of visits for multiple patients, you might need to change the diagram do that patients OR patient visits are directly related to the project.CloudBase Services has built apps to track home nursing visits for large programs in Massachusetts, New Hampshire and Vermont.&nbsp; We know these things are complex.&nbsp; Feel free to reach out to us.Debbie

troypatton · Answer

Hi Debbie,Technically, a project is a contract that we have in place with a pharma company to see multiple patients. Each patient is enrolled at their local clinic and we receive patient documents through the clinic as well.From my limited understanding of QuickBase, fundamentally, should I be creating as few relationships as possible to keep things less congested? Or, does it make more sense to relate everything as much as possible but then format the UI/tables extensively to keep things in check?I see a great amount of benefit in conditional dropdowns and I understand those require more interconnectedness between tables.Thanks,Troy

troypatton · Answer

Does anyone else have any suggestions for my last post? Trying to find the most beneficial way to build multiple relationships in the planning stages of my app.

As many relationships as possible vs. as few relationships as possible?

Thanks,

Forum Discussion

Multiple Join Tables within an App? Mobile nurse example

Related Content

Building a report from multiple tables

Using Grid Edit to Edit Multiple Records at Once

Lookup multiple items from the same table.

Edit Multiple Records at Once with Grid Edit

Question Regarding Alex's Trained Model Example