1 Reply Latest reply on Jun 17, 2015 10:50 AM by philmodjunk

    Medical database - one to many relationship



      Medical database - one to many relationship


      I know this is simple but I could use some help. Whats the best structure for a rather basic medical database with two different kinds of 1:many relationships. First: one patient - many diagnoses. This can be static so at any given time each patient will only carry one list of diagnoses but I need to easily be able to search all the diagnoses fields at once with one simple search. Should it all be in one file and just use a concatenation field to search all the different diagnoses filed at one time or split into related files. Second: one patient - many hospitalizations. This I was just going to simply make two separate related files. Thanks   stylcouncl@aol.com

        • 1. Re: Medical database - one to many relationship

          Don't need multiple files. You need tables, table occurrences and relationships.

          On your First case. you've described a simple one to many relationship, that might be part of a larger many to many relationship.


          Patients::__patientID = Diagnoses::_fkPatientID

          A portal to Diagnoses could be placed on the Patients layout to show and to create/edit/delete diagnoses records.

          To find all patients with at least one Diagnosis named "xyz", you would simply enter find mode on the Patients layout and specify "xyz' in the Diagnoses::DiagnosisName field before performing the find. This is something that can be done manually or via a script.

          The above relationship could be set up as "many to many" if you want/need a single table of all possible diagnoses with one record of each type to link to your patients. In that case, you might set up this relationship:


          Patients::__pkPatientID = Patient_Diagnosis::_fkPatientID
          Diagnoses::__pkDiagnosisID = Patient_Diagnosis::_fkDiagnosisID

          This would allow you to set up a portal to Patient_Diagnosis on your Patient layout with _fkDiagnosisID formatted as a value list for selecting  a given diagnosis for the current patient.

          For hospitalizations, you can simply link Patients to Hospitalizations by PatientID just as shown for the tables in the above relationships. You might also link in a table of hospitals to hospitalizations if you need to keep track of some basic info on each hospital. You can use a different Tutorial: What are Table Occurrences? of Patients here if you plan on using a different patients layout to show and track their hospitalization data. That might be a workable example of an Anchor Buoy format to your relationships.

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          Caulkins Consulting, Home of Adventures In FileMaking