9 Replies Latest reply on Jan 26, 2013 7:24 AM by WendellNeeley

    Need advice on table relationships

    WendellNeeley

      Title

      Need advice on table relationships

      Post

           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).  

      Screen_Shot_2013-01-24_at_2.59.46_PM.png

        • 1. Re: Need advice on table relationships
          philmodjunk

               Do you understand what table occurrences are?

               Do you know that your relastionship graph can contain multiple occurrences of your Patient Health History--each linked to different occurrence of other tables?

               Thus, you can link one occurrence of Patient Health History to New Examinations, a different occurrence of this table to Growth and Development and a third occurrence directly to Patient Information.

          • 2. Re: Need advice on table relationships
            WendellNeeley

                 I know what they are, but I'm pretty sure that I don't understand how they work.  I have read your tutorials and watched several video tutorials to try and get this sorted out, but I still don't think I get it.  

                 If I link an occurrence of Patient Health History to New Patient Examination how should I link it?  Should it be with a primary key/foreign key?  If so, how will the tables know that the records are related if I haven't told them somehow?  

                  

            • 3. Re: Need advice on table relationships
              WendellNeeley

                   Also, do you think I have the original table occurrences in the right relationships?

              • 4. Re: Need advice on table relationships
                philmodjunk

                     Link all the occurrences of this new table by Patient ID. Plus any secondary match field you need to link it to s specific record in a table other than Patient Information.

                     Specifically for New Patient Exams:

                     New patient Examination D...::_PatientIDfk = NpExam_Patient Health History::_PatientIDfk AND
                     New Patient Examination D...::__NewExamIDpk = NpExam_Patient Health History::_NewExamIDfk

                     The sole purpose of:

                     New patient Examination D...::_PatientIDfk = Patient Health History::_PatientIDfk

                     is so that if you create a new record in a portal to NpExam_Patient Health History on a New Patient Examination layout, the needed PatientID is also entered into the matching fk field so that it automatically appears in a portal to Patient Health History on your Patient Information layout.

                     I haven't made a detailed study of all your table occurrences/relationships shown so cannot tell you if they are all correct. Often, you can't tell if they are correct just from the relationship graph anyway as the way in which you use the data in these fields often determines the relationships needed.

                • 5. Re: Need advice on table relationships
                  WendellNeeley

                       That works well for the New Patient Exam relationship but I'm still having troubles with the Growth and Development relationship.  There's no PatientIDfk to relate Growth and Development and the new occurrence of Health Hisotry or Clinical Exam.

                  • 6. Re: Need advice on table relationships
                    philmodjunk

                         A good point and a detail that I hadn't noticed.

                         There are several ways around that. One way is to define a calculation field in growth and development that copies the PatientID from the New Examinations table for use as that missing match field. This should work even though the field will be an unstored calculation field.

                    • 7. Re: Need advice on table relationships
                      WendellNeeley

                           I did NewExamIDfk = NewExamIDfk and that seemed to work.  

                      • 8. Re: Need advice on table relationships
                        philmodjunk

                             It works, but won't link the data back to a specific record in Patient Information.

                             Remember, the additional field matching by PatientID is so that any new record you create in a portal to Patient Health History is also directly linked to the correct record in Patient Information.

                        • 9. Re: Need advice on table relationships
                          WendellNeeley

                               Thanks again for the advice, Phil.  As usual, you are full of internet awesome.