8 Replies Latest reply on Jun 18, 2014 12:35 PM by cdub5

    Database Schema for Survey Help!

    cdub5

      Title

      Database Schema for Survey Help!

      Post

           Hi,

            

           I've been lurking around the forums for a while now looking at various topics regarding database schema for Survey applications.

            

           However, despite the insight and templates posted on this forum I still cannot get my head round how to create a relational database for my application.

            

           Here's the scenario: I need to create a health and safety inspection application which will enable the user to carry out a survey of how well a specific location fares against a bunch of pre-set questions/criteria. So pretty much a checklist where each question is either a  pass or fail response. Currently the survey is paper based (I have attached an example to this post)

            

           As I understand it from other forum posts, the basic schema of a survey should be Subjects --> Responses <-- Questions <--Surveys

            

           But I need to have different Sections for Questions as well and i'm having trouble incorporating another table into my schema.

            

           Please find the image of my schema so far. The idea is that the user picks a Survey ID (there will only be one survey to choose form) and a LocationID (for wherever they are doing the survey).. then somehow allow the user to answer yes/no to a list of displayed questions. Could anyone tell me if i'm going in the right direction with this? (is the schema okay?) I have hit a brick wall and been stuck at this point for a while now!

            

      Current Paper Version

            

      FMP File

            

      Relationship Graph

            

            

           Thanks!

            

        • 1. Re: Database Schema for Survey Help!
          philmodjunk
               

                    But I need to have different Sections for Questions as well

               Please provide a detailed explanation of that.

               What is a "section"?

               What is the purpose to having your questions grouped in sections?

          • 2. Re: Database Schema for Survey Help!
            cdub5

                 Hi Phil

                 At the moment Questions are grouped into sections on the paper based version. Each section is a particular area of criteria that a location gets surveyed on. If any of the questions within a section get a 'fail' against them, the whole section gets a 'fail' regardless of the outcome of the other question asked within a section. 

            • 3. Re: Database Schema for Survey Help!
              philmodjunk

                   Then I suggest that you don't need InterviewSections and that you should rearrange your relationships to be:

                   Surveys-----<Questions>-------Sections

                   Surveys::_pkSurveyID = Questions::_fkSurveyID
                   Sections::_pkSectionID = Questions::_fkSectionID

              • 4. Re: Database Schema for Survey Help!
                cdub5

                     Thanks, that looks a lot better. So this it what i have now.  The idea is that a new record will be created in the Interview table when the user starts a survey on site. They pick what location they are at from the Location table. The __fk_SurveyID identifies the survey template.

                     I'm stuck however on how to populate the InterviewQuestions table with the list of records from the Questions table and give them the relevant _fk_InterviewID field..

                     Any ideas on how to make this work? Or if my thinking on this is even correct?

                      

                      

                      

                • 5. Re: Database Schema for Survey Help!
                  philmodjunk

                       It would be done with a script. And I would link a table occurrence of questions to InterviewQuestions instead of copying the question text.

                       #From a layout based on Interview...
                       If [ Not IsEmpty ( Questions::_fk_SurveyID ) // confirm that question records exist ]
                          Set Variable [$InterviewID ; value: Interview::_pk_InterviewID ]
                          Go To Related Record [Show only related records; From table: Questions ; Using layout: "Questions" (Questions) ]
                          Import Records [no dialog ; YourFileNameHere.fmp12 ; Add ; Windows ANSI ]
                          Go To layout ["InterviewQuestions" (InterviewQuestions) ]
                          Replace Field Contents [no dialog; InterviewQuestions::_fk_InterviewID ; $InterviewID ]
                       End IF

                       The Import Records step should import the needed data from the Questions table into the InterviewQuestions join table.

                  • 6. Re: Database Schema for Survey Help!
                    cdub5

                         Wow that's perfect thanks! Will this work on FilemakerGo? That's where most users will be inputting data. I read the import records script step doesn't have full functionality there.

                    • 7. Re: Database Schema for Survey Help!
                      philmodjunk

                           It should work as you are importing with target and source tables in the same file. If it doesn't there are ways to loop through the found set of records pulled up by the Go To Related Records to accomplish the same result one record at a time.

                      • 8. Re: Database Schema for Survey Help!
                        cdub5

                             Thanks a lot for your help PhilMod