Jump to content

Survey Database Schema Help!


This topic is 2599 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies



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 (in my case Locations) --> 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 attached zip file with the schema I have so far and PDF of the current paper version of what I am trying to accomplish. 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!




H&S report Paper.pdf


Link to comment
Share on other sites

My approach to a project like this was to to think of Survey/Sections/Questions as a kind of library or template. Then, when you fill out a survey for a given location, that structure is essentially replicated as Interview/InterviewSections/Responses. What I think you're missing here is the table that replicates the Survey, what I called the Interview. Either that or v.versa and you're concept is reversed from mine, or else you're thinking of Location as the Interview.


Also, I didn't use a join table for sections/questions, I just made section an attribute of the question. I.e., store the section ID in the question.


One of the hardest parts of this project was naming things so I didn't get confused.


Good luck and welcome to the forums.

  • Like 1
Link to comment
Share on other sites

  • Newbies

Thank you for your reply. I can see now how the join table between Questions and Sections is redundant.

Your approach of using the Question/Sections/Surveys tables as a template sounds like the best way to make this work.

When you talk about an Interview table which replicates the Survey table how would this work? Would this be another table occurrence of Survey with an 'X' relationship between the two occurrences?

Also, how does the Questions table become replicated as the Responses table in your approach?

Many thanks for pointing me in the right direction!

Link to comment
Share on other sites

First we need to establish we're talking about the same thing. When I say Survey I'm talking about the generic template: a collection of questions that have yet to be asked.


When I actually ask the questions, I create a record in the Interview table, which contains the person's name, date, etc., and the Survey ID. I suppose you could think of Interview as a join between the person (interviewee) and the survey.


I do a Find in the Questions table for the questions that belong to that Survey, then I import those questions into the Responses table. Each response record would contain the interview ID and (even though it's redundant) the survey ID.


This does end up creating a lot of Response records, but it makes reporting flexible and fairly straightforward.

Link to comment
Share on other sites

I do a Find in the Questions table for the questions that belong to that Survey, then I import those questions into the Responses table.


Another option is to show the relevant questions (in list view or in a portal) and create the related responses by entering the answer opposite the corresponding questions. The advantage here is that questions that weren't answered (in the case of a checklist, unchecked items) do not have a redundant response record. However, this is a little more difficult to set up.




Personally, I'd leave the Survey as a paper-based document, and then do the collation of the results in something like Excel or proper data analysis software ... although I have done survey collations in FileMaker, it is not really a good data analysis tool.


Specifically, what data analysis tool available in Excel would you find missing here?

Link to comment
Share on other sites

For my project, we wanted to be able to tally responses from multiple surveys. But, not every question was asked in every survey. IOW, each question might have a different denominator. I actually went a little out of normalized and set up separate boolean fields for yes, no, and unknown. All the questions are imported as "no" by default. (We assume every question was asked.) Then we only have to enter the yes's and unknowns. I found this setup makes it much more straightforward to analyze the results: you just add 'em up!

Link to comment
Share on other sites

  • Newbies

Hi, sorry for the delayed reply, i've been away for a few days.


Thanks for all the replies. Fitch- i think i understand now about the Survey - Section - Questions being a template now


I like the idea of doing a find and the importing the Questions i need for the Interview, however the user will be using FilemakerGo to do the surveys on and i don't think the Import Records function works on there.


I've updated my relationship graph (see below) adding in the tables you suggest. Is this along the lines of what you were describing? And is there a way you can think of making this application working without the Import Records function?




comment - When you say your method ins more difficult to set up.. how much more difficult?



Link to comment
Share on other sites

I like the idea of doing a find and the importing the Questions i need for the Interview, however the user will be using FilemakerGo to do the surveys on and i don't think the Import Records function works on there.
I don't see why not - but then I am basing this on reading the help only, not on an actual attempt.


comment - When you say your method ins more difficult to set up.. how much more difficult?


Approximately 13.451 non-numerical vague quantifiers more.


To get a more accurate reading, we need to establish what parameters a response should record. I am not getting a clear enough picture from looking at your RG; IMHO, you should not need the InterviewSections and InterviewQuestions tables. A Response should be related directly to a Question, and possibly to an Interview. The role of the Interview is not quite clear either: it doesn't seem to hold any information of its own, other than a LocationID.




There's an interesting demo by BruceR here, that's worth investigating.

Edited by comment
Link to comment
Share on other sites

This topic is 2599 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Sinky
      I have a calculation field in a portal which shows the value according to ActivePortalRowNumber. Each row has a special determined value.
      When I manually enter the value in the field before the calculation field and thereby make a particular row active, my calculation field automatically shows the designated value. Great!
      But, if when I do the import from Excel, all data gets nicely imported and the portal shows the imported data but the calculation field is without value. I guess the importing process is not making a portal rows active so the calculation is not working.
      Any ideas how this can be solved?
      Many thanks!
    • By Tony Diaz
      The tables.
      The Genre data is already arranged like this:
      Genre::ID (Unique Key)

      The Platform data is 'simple'. ID and Name. But each item could be multiple platforms.  (Example #1 vs. Example #2)
      An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples.

      It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selection from. Most have 4-6 of them.

      Example 1: Items::Table on the left, Genre::Table on the right, with some Genre Categories (Genre, Perspective, Pacing, Gameplay, Interface, Setting) and their sub-options.
      This one has just one sub-option per category.

      Example 2:  The Gameplay Genre Category has two sub-options related to it.

      Example Genre Table content:

      Just cracking the surface on One to One and One to Many relationships, I don't think this scenario is quite covered this way.

      Would each of those Genre Categories be portals showing only their related category ID?

      I presume that I would add fields to the Items::Table so I can pull related records:
      But those only support one relationship.
      Would I make value lists from those Genre Categories and Platforms and set them as tick box fields?
    • By stan111
      I use self portal on my Clients layout to serve two things:
      1. display all the records and
      2. quick navigate among them. 
      This portal is not displayed in Webdirect. 
      Is it possible to make it work?
    • By Guy_Smith
      This should be easy, but my brain just won't cooperate!
      I'm moving a bunch of stuff and want to inventory what is in each moving container.  I have a very simple parent-child relationship with the parent record having the container number and category of parts/equipment/supplies and the child records describe each piece of equipment in the container.  I have a portal with the child records showing for each container, but want to put anatto-entered item number for each piece that has the container number followed by a dash and then followed by a serialized number for each item in that particular container.  For example, I would have container 1 labeled "Glassware" and Container 2 labeled "Chemicals".  In Container 1's portal I'd like to see:
      Item    Description          Qty
      1-1      Beakers, 500ml    4
      1-2      Beakers, 250 ml   2
      and in Container 2's portal I'd like to see:
      Item    Description          Qty
      2-1      Potassium           4 g
      2-2      Sodium                26 g
      I'd like to auto-fill the item numbers, but can't figure out how to reset the serialization for each parent record.
      Any help is greatly appreciated.
      Thanks and keep yourselves safe!
    • By Michael Stevens
      I have a parent table with 3 child tables.  In the child table, there can be many related fields.  I need to export only one row of data for each record in the parent table.  This would require that a new column be created for each of the many values related in the parent to the child.

      For Instance:
      Noah Justin has 3 Email Address NJ@Test.com, NJ2@Test.com, and NJ3@Test.com

      When I do an Excel Export from FM I get
      Column 1 .       Column 2
      Noah Justin    NJ@Test.com

      I need:
      Column 1 .     Column 2 .        Column 3 .          Column 4
      Noah Justin  NJ@Test.com  NJ2@Test.com  NJ3@Test.com

      I'm sure it's pretty common and there is an easy solution to this.  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.