Survey Database Schema Help!

[Bruce_Gibbon]

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 (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!

Thanks

 

 

H&S report Paper.pdf

HS FORM.zip

[Fitch]

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.

[Bruce_Gibbon]

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!

[Fitch]

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.

[Helpful Harry]

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.

[comment]

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?

[Fitch]

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!

[Bruce_Gibbon]

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?

 

http://www.4shared.com/download/Iheqd_hOba/relationshipgraph.jpg?lgfp=3000

 

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

 

Thanks!

[comment]

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 June 18, 2014 by comment