Need advice on table relationships
Okay, so I've got a problem in determining how best to create and/or relate tables in my current database for my orthodontic office. I have four tables with the following relationships:
Referring Doctor Information-<Patient Information-<New Patient Examinations-<Growth and Development Examinations
Each doctor can have multiple patients, each patient can have multiple new examinations, and each new examination can have multilple Growth and Development Appointments (follow up appointments to see if a patient is ready for treatment). I need to include two other tables called Health History and Clinical Examination. I will be taking this information at three specific times: 1. At the new patient examination, 2. At Growth and Development Examinations and at random intervals. In summary, each patient can have many health history examinations and clinical examinations which may or may not be related to both the New Patient Examination and the Growth and Development Examination, however the New Patient Examination and the Growth and Development Examination will absolutely have a Clinical Exam and Health history associated. I currently have the Health History and Clinical Exam tables primarily related to the Patient Information table, however I would also like to be able to create new related records from portals on the Growth and Development Examination Table/New Patient Exam Table and create both clinical exams and health histories that are not related to either a New Patient Exam or Growth and Development Exam (e.g. random updates during treatment).