Difficulty implementing data relationships
I'm implementing a database for medical records. I will be using it to keep track of patients and procedures they received. Here are the requirements/constraints for my DB:
- Any single patient can have multiple procedures.
- There are 3 types of procedures: checkup, surgery, and imaging.
- All procedures share a date, patientID, and physician.
- Each procedure also has additional information specific to its type. (e.g. surgery might indicate a location, imaging has a modality)
- We want to be able to list *all* procedures a patient has had (listed by date) with the possibility to click through to see details
Here is how I first tried to implement the database (which failed):
- I created a Patients table with general patient information
- I created a Procedures table that listed the date, procedure type, referring physician, and patientID. This was linked to the Patients table by patient ID.
- I created Checkups, Surgery, and Imaging tables. These contained information specific to the procedure type. Each linked to the Procedures table based on PatientID, Date, and ProcedureType.
The problem with the above approach was that there was nothing to enforce data consistency between the Procedures table and the 3 procedure details tables. If an entry for a checkup was in the Procedures table, there was no easy way to make sure that an entry in the Imaging table didn't link to it.
FM detals: [FMPRO 10 on Mac. Deployed on a shared windows server using p2p sharing]
Sorry if this explanation is somewhat long-winded. I would appreciate any advice in how to set up my tables to keep the data consistent.