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

    Database Schema for Survey Help!



      Database Schema for Survey Help!




           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





        • 1. Re: Database Schema for Survey Help!

                    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!

                 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!

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


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

              • 4. Re: Database Schema for Survey Help!

                     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!

                       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!

                         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!

                           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!

                             Thanks a lot for your help PhilMod