Skip to main content
Question

Multiple Join Tables within an App? Mobile nurse example

Hello QB Community,

I'm trying to find the best way to go about multiple many-to-many relationships. To keep it brief,  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 Projects
  • 1 Nurse can have many Home Visits (and possibly many Home Visits within the same Project)
  • 1 Project can have many Nurses
  • 1 Project can have many Clinics
  • 1 Clinic can have many Nurses (Geographically associated)
  • 1 Clinic can have many Patients
  • 1 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!






 
    This topic has been closed for replies.

    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
    • Author
    • Registered
    • March 13, 2018
    Thanks for the feedback, I'll play around with the diagram some more and try it again in the app.
    Forum|alt.badge.img+5
    Troy -

    You might want to create a table with multiple joins ... a many-to-many-to-many type of thing.  
    One question first:  What is a project?  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.  We know these things are complex.  Feel free to reach out to us.

    Debbie
    Debbie Taylor CloudBase Services
    • TroyPatton
    • Author
    • Registered
    • March 13, 2018
    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
    • Author
    • Registered
    • March 19, 2018
    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,
    It is hard to say without getting investing time with you to understand your workflow, but I would say that you should have the relationships which are necessary to give the best user experience.  That is what matters most.  I would say that app performance is way secondary unless you have tables which hundreds of thousands of records.
    Terms & ConditionsAccessibility statement