I am a filemaker/database creator noob so please forgive my ignorance. And thanks in advance for any help you are able to provide. I apologise for how long this is but it looks worse than it is, most of it is just informative so you might be able to follow my train of thought.
Basically I am just trying to determine how to properly seperate information on tables and create relationships. I am creating a database for a medical office working with several therapists. I will need it to keep client info, name, address etc, referral info such as date of referrals, by whom, etc, any contact that is made with the client, whether a letter was sent, a phone call was made, etc and of all appointments offered by each of the different therapits.
Am I correct in assuming filemaker can achieve all this in one database? I would like the screan to be broken up something like the screen shot I made below as an example. Now that is just an idea, if someone thinks it won't work or has a better idea I'm open for suggestion as I haven't begun the design process ye
So far I have the following tables and fields listed out ready to create:
Clients: ClientID, First Name, Last Name, Full Name, Address, D.O.B., Age, Mother, Father, Telephone, Mothers Mobile, Fathers Mobile, Team, Diagnosis, , GP(Doctor) Name, GP Address, GP Phone ,PHN(Nurse) Name, PHN Address, PHN Phone, Medical Card No. PPS No, EIT(one of two teams) Referral Date, EIT Referred By(select from drop down list from referrers table), EITDate Received, EIT Date Accepted, EIT Date Ineligible, EIT Date Discharged, EIT MDT Date, 5-18 (Second team) Referral Date,5-18 Referred By(Select from drop down list from referrers table), 5-18Date Received, 5-18 Date Accepted, 5-18 Date Ineligible, 5-18 Date Discharged
Referrers: ReferrerID, First Name, Last Name, Full Name, Address, Phone
Speech Thearpists: Speech ID, First Name, Last Name, Full Name, Address, Phone (Also a table like this for Psychologist, OT, and PHN)
GP's: GPID, GP Name, Gp Address, GP Phone
Contacts: Contact ID, contact Type, Date of contact, reason for contact
Where it gets tricky (For me) and I am lost is with the correct type of relationships and linking various info to portals, etc.
I would like to give each of the therapists their own table that collects information pertaining to them as well so that it may be easier to collect stats. That's why on my screen shot below I have tabs set aside for psychologist, physiotherapists, etc.
I forgot to show in my screen shot example that I would also have a tab for contacts where the fields listed above for my contact table. This information would then feed onto the portal shown in my screen shot. one of the areas I am unsure about is if I go to the letters tab, choose an appt letter and send that, I want it not only to appear in the appts portal, but also as a form of contact. Do I need to do that in two different steps? i.e. send the letter it will show in appts portal and then fill out the contacts layout to get it to appear on contact portal.
Basically, I am unclear as to whether I need all the different tables I mentioned or have a made too many? not enough (should EIT and 5-18 have their own table)? Two, the proper way to create the relationships, etc. Im assuming that I put a clientID field onto the referrers table and link clientID on client table with client ID on referrers table. (that's a big assumption as the field is going to be a drop down menu referring to the referrer table) etc, things like that.
I'll leave it at that for now, and start working on it up to that point. I really hope I didn't confuse people more by providing too much info.