10 Replies Latest reply on Jun 2, 2016 12:01 AM by erolst

    Need help designing database for complicated relationships

    iimpact

      Hello,

       

      I have slowly started building my database but have come across a needed relationship that I am not sure I know how to handle.

      Currently, the database is designed as follows:

       

      • Method layout
        • Add new methods to Method table
      • Sample layout
        • Add new Samples to Sample table
        • Associate 1..n Methods to Sample
      • Report layout
        • Add new Report to Report table
        • Need to associate Report to selected "Sample and Method" combination

       

      I am currently having trouble associating a Report to a Selected number of Samples (based on Method). See diagram below:

      Screen Shot 2016-05-31 at 11.15.35 PM.png

      What I am looking to do is in the Report layout, be able to generate a new Report for a given Method (let's say Method 1), and then have that Report associated with n Samples (Sample 1, 2, and 3). A Method <--> Report relationship is not unique, as I need to be able to assign a different Report to Method 1 with associated Samples 4, 5, 6.

       

      Here is my current DB schema. I have assigning multiple Methods to a single Sample working fine, but am having trouble trying to layout the desired relationship described above.

       

      Screen Shot 2016-05-31 at 11.10.18 PM.png

        • 1. Re: Need help designing database for complicated relationships
          coherentkris

          I would say that we need more details about what the solution is trying to do.. Specifically what objects is the database trying to handle.. sample of what, report of what, method for doing what?

           

          You might want to look at data normalization

          Database normalization - Wikipedia, the free encyclopedia

          and data modeling

          Data modeling - Wikipedia, the free encyclopedia

          • 2. Re: Need help designing database for complicated relationships
            Mike_Mitchell

            Agree with Kris. You appear to be fixating on layouts and functions rather than on database entities. Figure out what "things" you're trying to track first, then build tables to correspond to that.

             

            But off the top of my head, it looks like you need a three-way join table between Report, Method, and Sample. Each record in that table corresponds to the unique combination of the three entities.

            • 3. Re: Need help designing database for complicated relationships
              iimpact

              Thank you for the response, I'll provide try and provide more information.

               

              The database is used to to keep track of analytical (Chemistry) data for a lab. A Sample is what is going to be tested using a specified Method. After each test, there will be a lab Report for that Sample + Method combination.

               

              A typical scenario might be:

              1. A scientist receives 3 samples from lot 100
              2. Each sample is logged in FileMaker as
                1. Sample 1, lot 100, stability 5C
                  1. Test Method 123 is assigned to the Sample
                2. Sample 2, lot 100, stability 25C
                  1. Test Method 123 is assigned to the Sample
                3. Sample 3, lot 100, stability 60C
                  1. Test Method 123 is assigned to the Sample
                  2. Test Method 321 is assigned to the Sample
              3. Samples are tested using their applicable Methods
              4. Assign a Report (based on the Method) for Test Method 123 for Samples 1, 2, 3

               

              Models look something like:

              • Sample
                • PK_Sample
                • Sample ID
                • Lot Number
                • QC Number
              • Method
                • PK_Method
                • Method ID
                • Name
                • Type
              • Report
                • Report ID
                • Description
                • Author
                • Date
                • Comments

               

              I hope this provides you with enough information, and thanks for taking your time to help.

              • 4. Re: Need help designing database for complicated relationships
                iimpact

                Thanks Mike,

                 

                I went ahead and provided more information in the response above.

                 

                I was initially going to go with a 3-way Join table, but got caught up on how it was going to be populated.

                Since a Sample + Method will be created and assigned as a pair first, this will leave an empty Report FK in the table until it's time to create a Report. This is fine, but once the Report is created for a particular pair, I was not sure how to assign it's PK to the 3-way Join table's FK based on the pair selected. Additionally, since there could be multiple Samples using the same Method, it would be preferred that when generating a Report, the user will be able to select which Samples to assign that Report to.

                • 5. Re: Need help designing database for complicated relationships
                  erolst

                  Try:

                   

                                                    Method

                                                        I

                                                        ^

                  Sample --< SampleInReportUsingTestMethod >-- Report

                   

                  This three way join table is the core of your model; you can filter its records using any criteria from itself, or its parents – e.g. find all records for Report A where Method 123 was applied.

                   

                  Or, if the contents of a “report“ are only determined after the fact (testing), use

                   

                  Sample --< SampleInReportUsingTestMethod >-- Method

                   

                  then on creating a Report, let the user select SampleInReport records based/filtered on/by a Method, and simply store the IDs of the selected join table records with the Report record, so you can call and display that report with a simple GtRR.

                  • 6. Re: Need help designing database for complicated relationships
                    Mike_Mitchell

                    Then yes, you will need a three-way join table to connect Report + Method + Sample. You may also need a separate join table for Method + Sample, but I think you should be able to work with just one.

                    • 8. Re: Need help designing database for complicated relationships
                      Mike_Mitchell

                      All of that can be handled through scripting. And should be.

                      • 9. Re: Need help designing database for complicated relationships
                        iimpact

                        Since a Report can be generated after a Method, I went with our second suggestion. Everything seems to work fine (filtering Samples based on Method), but how am I going to do this:

                        simply store the IDs of the selected join table records with the Report record

                        To me, it seems like I would then need another table to store the join record(s) PKs with the associated Report PK. I guess my question is how would I store multiple join record PKs with a single report?

                         

                        Here is what I currently have:

                        Screen Shot 2016-06-01 at 6.22.46 PM.png

                        • 10. Re: Need help designing database for complicated relationships
                          erolst

                          iimpact wrote:

                          To me, it seems like I would then need another table to store the join record(s) PKs with the associated Report PK. I guess my question is how would I store multiple join record PKs with a single report?

                           

                          Create another join table. 

                           

                          But that doesn't seem necessary here. Since all you want to know is that these SampleTests belong to that report, you could just store them as list in a Report field.