|
| I am new to developing a Many-to-Many DB. The current design includes: tbl_patient, tbl_patient_visit, tbl_visit, and multiple assessments such as tbl_vital_signs, tbl_family_history and etc.
The PK in tbl_patient is pt_id with a FK in tbl_patient_visit being pt_id. Additionally, the PK in tbl_visit is v_id with a corresponding FK in tbl_patient_visit. Furthermore, the various assessment foreign keys are in the tbl_Visit with a separate table for each assessment.
All PKs are "auto numbers" with cascadeing foreign key.
However, the foreign key field in the child table does not update automatically. It has been recommended that I provide a trigger to maintain this function.
I can understand using a trigger, however, I then have a problem with the tbl_visit, which has all the assessments referenced as a forein key. I expect that all assessments would need to be completed at each visit in order for a row in the tbl_visit to update. This is a problem because not all assessments are completed at each visit. My thought was to make the FK "NULL", however the PK is an autonumber and must match in that it does not allow null.
Your assistance in this matter is much appreciated. |
|