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.