Questionnaire database - multiple surveys, repeated over time

foxtrot123

Registered User.
Local time
Today, 08:21
Joined
Feb 18, 2010
Messages
57
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!
 

Users who are viewing this thread

Back
Top Bottom