Relational Database Question - Patients, Prescriptions and Pharmacies
So I made a project for a doctor's office and it came out quite well I think. I'm pretty new at FMPro, but the client liked it.
Anyway, I have 3 tables: patients, visits, prescriptions.
I have established relationships: one patient can have many visits; one patient can have many prescriptions.
But now I have to add the name of the Pharmacy the patient uses to the patient record. I am assuming that one patient can have only one pharmacy. This information can be modified of course if the patient changes pharmacies. But a patient should only have one.
A prescription could be filled at many pharmacies. But prescription records are related to the patient through a portal on the form.
I don't want to maintain a table of all the pharmacies in a given area.
My solution is to add the pharmacy name, address, phone, fax etc. to the patient record and then display those fields on the prescriptions tab of the form. I know that one pharmacy can be used by many patients so there will be duplication of pharmacy records but my approach seems the most practical to me at this point.
Can anyone suggest a better way.