I have a table called tbl_Main_Patient it includes:
tbl_Main_patient: PatID, LName, FName, MName, Sex, Address, etc.
I realize that a patient can have zero or more Medications and Medications can have zero or More Patients. M:M Am I better off including a MedicationID into the tbl_Main_Patient join to a table tbl_Med_info joined to a tbl_Med.
I have 6 more possibilities similar to Medications, such as, Diagonsis, Insurance, Studies, Equipment.
Would an alternative be to create a Patient_Detail table which has MedID, EquipmentID, PatientID, DiagID, joined by PatientID to the tbl_Main_Patient. Then the Patient_Detail will join to other relevant tables.
If anyone can be of assistance, I would be greatful.
Last edited by Leventcos21; 08-03-04 at 18:21.
Your "tbl_Main_Patient" looks essentially like a basic customer information table. A patient/customer will often have multiple visits, which may mean new and/or modified medications and procedures. Your idea for a "Patient_Detail" table is along the right track.
Try thinking of it more like a retail sales application. Instead of a "Patient_Detail", create a "Patient_Visit". Build it like a sales invoice: Cust_ID, Visit_ID, Date, Insurance, Medications, Procedures, etc.. Also, you may need to include medicine dosage instructions for each visit.