4 Replies Latest reply on Feb 10, 2011 9:47 AM by JoppeFierens

    Creating a relational databse



      Creating a relational databse


      Good afternoon, 

      I have a question about a specific database I need to create:

      I will need :
      a table with patients (patient number - initials - age )
      a table with the medical history of each patient (patient number - additional data)
      a table with the procedures (patient number - choice between 3 procedures - additional data)
      a table which lists all the info from 1 specific procedure.

      should I link the patient number of the patient table to all the other tables ? (one to many)
      how do I create the link between procedure and patient (every procedure will be done once per patient)  ?

      I appreciate your input,


        • 1. Re: Creating a relational databse

          In addition to linking patients to their medical history, you will likely need this relationship:


          Patients::PatientNumb = Patient_Procedure::PatientNumb
          Procedures::ProcedureID = Patient_Procedure::ProcedureID

          You'd define one record in Procedures for each procedure this medical practice can perform.

          Patient_Procedure would document what procedure was performed on a given patient. This is called a many to many relationship with Patient_Procedure functioning as the Join table linking the two tables.

          A portal to Patient_Procedure can be placed on a Patients layout that includes fields from Procedures to list the procedures performed on that patient. A portal to Patient_Procedure can also be placed on a Procedures layout that includes fields from patients to list all the patients on which a given procedure has been performed.

          • 2. Re: Creating a relational databse

            Thank you for your quick answer. I have made the relations between patients and procedures as showed below. Is this also the correct link to the other tables?

            Relations made

            • 3. Re: Creating a relational databse

              My original suggestion was to only link Patient and Procedure to Patient_Procedure. PatientID would be a serial number defined in Patient. ProcedureID would be defined in Procedure. Procedure records would link to Patient_Procedure only by the ProcedureID number.

              Example: Say one of your procedures is "Colonoscopy". It might have the serial number 235 in the Procedure table. If Patient 123 recieves a colonoscopy, a new record would be created in Patient Procedure with 123 in PatientID and 235 in ProcedureID. Thus, this new record records the fact that Patient 235 received a colonoscopy. Data, such as the date when the colonoscopy was performed on this patient, would be stored in fields defined in Patient_Procedure. Data common to all colonoscopies would be recorded in fields defined in the procedures table.

              With regards to the other tables you are linking to Patient_Procedure:

              I have no idea what AE is and so can't say anything about it one way or the other.

              I would not link Med. Hist. to Patient_Procedure unless I planned to create a series of records for the same patient--one for each procedure performed. If that were the case, I'd probably dispense with the History table completely and just put those fields in Patient_Procedure.

              Linking the Conc Med table would make sense if you wanted to see a list of all meds linked to the procedure for which they where prescribed. That would require a different link, however. I'd define a third field, Patient_ProcedureID in Patient_Procedure as an auto-entered serial number and use it to link to the Conc Med records so that I can see a list of Med records for each Procedure performed on a given patient. If you only want to link the records by PatientID, I'd link this tale directly to the Patient table.

              Here's  download link to a demo file you can examine. It matches "contracts" to "companies", but if you were to rename them Patients and Procedures, it would provide a decent example of what I described in my original post to this thread.


              • 4. Re: Creating a relational databse

                thank you for the example and the information.

                The AE and Med History table should only be there so I can check the medical history of one patient based on the patient number. Thanks a lot for your help!