4 Replies Latest reply on Feb 27, 2015 7:01 AM by terry

    Relate a multi-level location to people


      Hi folks,


      Filemaker dabbler here using FMPA13.  I’m trying to help a friend of mine who has Stage 4 cancer organise all the medical information she has.  I'm in the design phase right now, so haven't built an actual database yet.  I think I’ve been looking at this too long and am suffering brain freeze. 


      I need a bit of help in how best to structure the database in regards to the treatment location where the medical service is provided.

      The sticking point right now is how best to relate the treatment location to a possible main location with a possible sub location and then assign that to a provider.


      A provider may have more than one treatment location, so I assume that I'll need a join table for that.


      I’ve sifted through her medical records and come up with three possibilities for a treatment location:  The treatment location (actual place where the medical service was provided) could be one of three types:


      1. a stand-alone treatment location; 

      Provider <—— JOIN ——> Treatment Location


      2. A treatment location at a main location; or

      Provider <—— JOIN ——> Treatment Location <——- Main Location


      3. A treatment location at a sub location of a main location.

      Provider <—— JOIN ——> Treatment Location <—— Sub Location  <——- Main Location


      A treatment location inherits the Sub Location address and contact information if there is one and the Main Location address and contact information.  


      It’s this possible Sub Location that’s causing my brain freeze.  There isn’t always one.  I thought about using a placeholder record in a Sub Location table with a value of “none” and a calculated field in the Treatment Location table which would assemble the name for things like value lists so that the treatment location would not display the “none” value.   This seems counterintuitive to me.


      I also thought about relating Main Location and Sub Locations to the Treatment Location using separate foeign keys for each.  I'm not sure what's the best way to go. And this also brought up another question for me - Is there a way to design this location part to make it flexible in case somewhere down the road, yet another level of a location is required?


      To complicate matters, support personnel could exist at any of the location levels.  To my knowledge, support personnel are only at one location, so there’d be no need for a join table, however, I think in the interests of making it flexible, I may add one.


      Support Personnel <—— Treatment Location <—— Sub Location <——- Main Location

      Support Personnel <—— Sub Location <——- Main Location

      Support Personnel <——- Main Location


      I’m not sure if either of these approaches are correct or if one is better than the other.  Any advice, pointers, comments, would be greatly appreciated.




        • 1. Re: Relate a multi-level location to people

          On the face of it I think the way I would proceed if this were my task would be to create tables for Providers, Locations and Support Personnel with relational links between them all. So a provider might be a company or organisation which delivers a type of treatment, say X-radiology or MRI; this treatment is available at one or more locations, one of which will be the main location—or possibly the main location is just head office and not a treatment location; and at each location there are support personnel.


          To link these entities to one another I think you would need join tables all round, as they are all potentially many-to-many. A provider may offer more than one type of treatment. There may be more than one types of treatment available at a treatment location. Support personnel may work at more than one treatment location.


          To link locations I think a join table would be needed to link Location to Location, with each link record designating the type (Main, Sub, etc)—although you may be able to simplify this part, depending how complex it all is. For example, if a location is and only is Head Office and never a treatment location it would be simpler than if the main location for a provider is, say, a hospital which is also a treatment location in some circumstances (eg. a particular type of treatment).


          I hope that's of some help.

          • 2. Re: Relate a multi-level location to people

            See if the attached helps.


            It sounds to me that the locations should be a self join.

            • 3. Re: Relate a multi-level location to people

              Thanks so much for clarifying with the pdf of the self join.  I kept thinking that if I did a self join, I would have to  relate more than one record for a three level location but just twigged that if I picked a second level location, the second level of location is already related to the first location.  



              • 4. Re: Relate a multi-level location to people

                Thanks for replying.  It certainly is turning out to be far more complex than I originally thought but that's okay as it is a great learning experience with FMPA.