db design for survey with two sets of questions per survey??

[tlyczko]

Hello, I work for a non-profit organization, where I'm working on a
survey database.

I need to survey houses and their residents. Houses can be in different
categories, each category having its own questions, and then the
category determines what questions are asked of the residents.

So far I have:

tblSurvey: specific information about each survey, including what
category the house survey belongs to (seven possible categories of
house), survey date, who is doing the survey, etc.

tblHouseSurvey: data about the overall house survey, including
HouseSurveyID and the category of the house being surveyed

tblHouseSurveyResponses: questionID, questionResponse, HouseSurveyID
(1 to 1 relationship to tblSurvey)

tblQuestions: questionID, questionText

tblQuestionsCategories: table showing which questionID goes with which
questionCategory (every house is in only one category, but a question
can be in more than one category), a query is used against this table
to populate the survey questions in the form, not to forget that
comments can be made against each survey category (e.g. Safety). Not
all categories have all the same questions, or this table would not be
necessary...

tblPersonSurvey: data about each person's survey

tblPersonSurveyResponses: contains questionID, questionResponse,
PersonSurveyID
(1 to MANY relationship to tblSurvey, which links together all the
survey responses for both house and people in each house)

I looked at Mr. Hookom's excellent survey tool but I am not sure it
applies to this kind of survey (it doesn't seem to allow the above
table setup), would someone be willing to comment???

I am fairly experienced with Access, I just want to make sure my
tables/database design is headed in the right direction.

Thank you,
Tom

 

[John Nurick]

Hi Tom,

Always start with the real-world entities and relationships you are
modelling. Sound like you have a fairly complicated situation, with
something like this needed:

Houses (easy: tblHouses)

House categories (tblHouseCategories). If each house is in one category
there's a 1:M relationship between categories and houses, implemented by
a HouseCategory foreign key field in tblHouses.

People (tblPersons) who live in houses. If no person can be a resident
of more than one house, there's a 1:M relationship between houses and
people, so you'd have a HouseID foreign key field in tblPersons. If a
person could possibly count as a resident of more than one house, that's
a M:M relationship to be implemented via a junction table.

Then there are presumably:

Surveys, i.e. sets of questions asked of people at various times in
relation to houses or residents (tblSurveys).

Questions (tblQuestions)

Presumably some questions are appropriate for more than one survey and
more than one category , so there's a many-to-many relationship between
Questions and Surveys, implemented in a junction table, * indicates a
field is, or is in, the primary key:
tblQuestionsSurveys
SurveyID*
QuestionID*
HouseCategory*
SortOrder (field to control the order in which the
questions are presented)

Then there are Responses (i.e. sets of answers from a person or
regarding a house to a survey)
tblResponses
ResponseID*
Respondent (foreign key into tblPersons.PersonID)
HouseID
Interviewer
ResponseDateTime
...

and Answers (answers to single questions)
tblAnswers
QuestionID*
ResponseID*
Answer

 

[tlyczko]

Where I'm getting stuck is the 'junction table' that knows which
questions to assign to a survey of either a house or of a person based
on whether the question is a house or person question, and what
category that house or person is in. Questions asked about houses are
not asked about persons and vice versa, but the category names for the
survey questions are the same.

I Googled many-to-many and junction tables etc. but didn't find much
that helped me understand this. I should look again.

The original design had separate tables for all the 'house' stuff and
all the 'person' stuff. Maybe I need to go back to working with things
that way, but there are 7 categories for a house survey.

I had had a field in tblQuestions, with all the category codes in it
(any combination of codes up to 7), separated by commas, and another
field to show whether it is a person or house question. Maybe this will
be easier to work with, since the only reason I need to differentiate
is for the Access forms per se, I have other tables that store the
PersonID or HouseID, the QuestionID, and the QuestionAnswer text...
I will keep working at it.

Thank you,
tom