5 Replies Latest reply on May 19, 2010 6:11 PM by fgimenez

    Difficulty implementing data relationships



      Difficulty implementing data relationships


      Hi all,



      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:



      1. Any single patient can have multiple procedures. 
      2. There are 3 types of procedures: checkup, surgery, and imaging. 
      3. All procedures share a date, patientID, and physician. 
      4. Each procedure also has additional information specific to its type. (e.g. surgery might indicate a location, imaging has a modality)
      5. 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):


      1. I created a Patients table with general patient information
      2. 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.
      3. 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.



      Francisco Gimenez


        • 1. Re: Difficulty implementing data relationships

          Define a ProcedureID field as a serial number and use it to link to your Checkups, Surgery and Imaging tables.


          You might also consider merging checkups Surgery and Imaging data into a combined table as this may simplify the design some types of reports. You can simply combine fields and only use those fields appropriate for each type of procedure. You can even use different layouts for each procedure type so you can hide fields not required for that type from the user.

          • 2. Re: Difficulty implementing data relationships

            Thanks for the help!


            I was looking into doing the combined "checkups, surgery, imaging" table, but my problem was the following. If somebody wants to browse all checkups, wouldn't they see a bunch of empty fields where there are entries for "imaging" or "surgery"? Is there some way to filter records based on they layout somebody is using?


            • 3. Re: Difficulty implementing data relationships

              You can create a layout that lets them browse each type of record and show only the fields you want to show.


              There are a number of ways to filter records for view in a portal too.


              You can do this with portals, global felds and tab controls very easily.

              First you create a calculation field in your procedure record that combines patientID and procedure type

              Then you set up 3(or as many as you need) calculation fields that have global storage that

              combine the patient id wtih the various types of procedures ie(nnn&"surgery")


              You create relationships from the procedure table to these global fields

              Then on a tab control you can place a portal for each type of relationship so they can see them filtered

              You can even have one that is all by simply linking procedure table to patient by patient id


              What you show in each portal is up to you.


              Its simple and effective and with the tab names equal to type very intuitive for the user.

              • 4. Re: Difficulty implementing data relationships

                with filemaker advanced you can even use custom menus to replace the standard "show all records" step with your own script where you can set up a find that only finds records appropriate for the current layout. Your users won't even know the other records are there unless they look at the total record count in the status bar.

                • 5. Re: Difficulty implementing data relationships

                  Thanks! That makes a lot of sense. I'll try out that solution and keep you posted on the results.