Questionnaire database - multiple surveys, repeated over time

[foxtrot123]

I'm developing a database to manage survey data collected for a study.

Individuals are seen for four visits and at each visit they complete a battery of surveys. Some surveys are repeated at each visit; some are new (but the survey schedule is predetermined).

I'm going to store the survey responses in a normalized fashion: tblQuestions, tblResponsesList, tblResponses, and tblSrvRspns, where tblSrvRspns holds data about the individual respondent.

How should I model the requirement of multiple visits and multiple surveys (some of which are repeated)?

Here's what I have so far:

1. A many-to-many between tblVisits and tblSurveys:

tblVisits
VisitID (PK)
VisitNumber

tblSurveys
SrvID (PK)
SrvName

tblVisitsSurveys
VisitSurveyID
VisitID (FK to tblVisits)
SrvID (FK to tblSurveys)

Attached is a screenshot of what I have so far:



My question is how to model which survey each person has completed, for each visit? I suspect it involves adding a link between tblVisitsSurveys and tblSrvRspns. Also, at some point I need a table whose PK consists of 3 fields: VisitID, SurveyID, and RspnsID (at the very least in tblResponses).

Any advice is much appreciated!

 

[foxtrot123]

Any ideas?

 

[jdraw]

You might want to take a look at "At Your Survey".
see http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html