Round robin survey design (1 Viewer)

[PeterOC]

Hi I'd like some advice on the design of a database.

I've got a survey that goes out to 4 people. Each survey is asking the people to rate aspects of the other 3. For instance an architect might be scoring a contractor in terms of health and safety. There are 13 possible questions but not every question applies to every survey combination.

What i've done is to have a table with:
ResponseID - ProjectID - Name(person filling in survey) - ScoringWho (the role of the person they're scoring) - Q1 - Q2 - Q3 ... Q13

As far as I can see the alternative is to have 1 row with every possible combination of response. But it means that every time data is entered 3 rows have to be filled. What's the better way to go about this?

 

[jzwp22]

Your table has repeating groups: Q1 - Q2 - Q3 ... Q13 which indicates that your table is not normalized. The better approach would have all of the questions as records in a table and not as fields in a table

tblQuestions
-pkQuesID primary key, autonumber
-txtQuestion

It sounds like a survey is sent to a person and in that survey the person is actually evaluating multiple other people. That one person (filling out the survery) to many persons (being evaluated) describes a one-to-many relationship which is handled with 2 tables. First a table to handle who the survey is sent to (the one filling out the survey). I assume that this survey is related to a particular project

tblProjectSurvey
-pkProjSurveyID primary key, autonumber
-fkProjID foreign key relating back to the project this survey pertains to (your project table?)
-fkPersonID foreign key to a table that holds the people; in this case the person the survey is being set to
-dteSurveySent date the survey was sent

As already mentioned, the survey is used to evaluate several other people

tblProjectSurveyDetail
-pkProjSurvDetailID primary key, autonumber
-fkProjSurveyID foreign key to tblProjectSurvey
-fkPersonID foreign key to table that hold the person being evaluated.

For each person being evaluated you will have multiple questions (another one-to-many relationship) and there will be 1 corresponding response to a question (from what I understand)

tblSurveyDetail
-pkSurveyDetailID primary key, autonumber
-fkProjSurvDetailID foreign key to tblProjectSurveyDetail
-fkQuesID foreign key to tblQuestions
-Response

I wasn't sure what you meant by this statement:

As far as I can see the alternative is to have 1 row with every possible combination of response. But it means that every time data is entered 3 rows have to be filled. What's the better way to go about this?

It sounds like you have a set number of possible responses, if so, then you can have a table to hold the responses

tblResponses
-pkResponseID primary key, autonumber
-txtResponse

Now you can change the tblSurveyDetail as follows to capture the response


tblSurveyDetail
-pkSurveyDetailID primary key, autonumber
-fkProjSurvDetailID foreign key to tblProjectSurveyDetail
-fkQuesID foreign key to tblQuestions
-fkResponseID foreign key to tblResponses


If you have only certain allowed responses based on the question, then that would require a slightly different approach. I will wait to hear back to see if that is what you need.

 

[MarkK]

I'd have a person table, survey table, question table at a minimum. And probably two survey tables, one that defines a surveys structure, and one that records when it happened and with who and so on.
Also, in data almost always avoid repetition, so anything that looks like Q1, Q2, Q3 is many records in a table, not many fields in a record.

 

[PeterOC]

Thanks very much for the replies.

I did think that as the 13 questions were different that meant that there wasn't repetition and the table was normalised... but yes, I can see the error of my ways and I wont do it again.

I will give you're suggested table design a go jz. The responses are simply ratings from 1 to 5 and 0 for N/A so I don't think I need a responses table or at least I can decide on this later once I have the rest in place.

What I meant by the quoted text is that on every survey Person A is rating Persons B, C and D so in my current table design 3 rows would be populated with the results of one survey. In order to facilitate only having one row updated the table would have to cater for every possible rating combination. That's probably not much clearer is it?

Thanks once again. I'll let you know how I get on.

 

[jzwp22]

What I meant by the quoted text is that on every survey Person A is rating Persons B, C and D so in my current table design 3 rows would be populated with the results of one survey. In order to facilitate only having one row updated the table would have to cater for every possible rating combination. That's probably not much clearer is it?

I think I understand what you are saying. If you decide to go with a table structure similar to what I proposed, you should be able to construct a main form with subforms in order to accomplish the survey of multiple other people.

 

[PeterOC]

One thing I'm struggling with is that for every Project there are 5 possible roles but in some projects 1 person can be responsible for 2 of these roles. What I have at the moment is:

tblProjects:
-pkProjID
-ProjectName
-blnLive boolean - whether the project is live or not

tblProjectSurvey:
-pkProjSurveyID autonumber
-fkProjID (links back to tblProjects)
-fkPersonID
-dteSurveySent

tblContacts
-pkContactID
-ContactName
-ContactEmail
-fkRoleID

tblRoles
-pkRoleID
-RoleDescription

But this isn't ideal as where there is 1 person occupying 2 roles I have to enter them twice in tblContacts.

What I was thinking of is:

tblProjects:
-pkProjID
-ProjectName
-blnLive boolean - whether the project is live or not

tblProjectRoles
-pkRoleID
-fkProjID (links to tblProjects)
-fkContactID (links to tblContacts)
-fkRoleID (links to tblRoles)

tblContacts
-pkContactID
-ContactName
-ContactEmail

tblRoles
-pkRoleID
-RoleDescription

Any thoughts on this? My concern is that in tblContactRoles there's a lot of fk's on the one table.

 

[jzwp22]

Your second version is better. I did make one adjustment in tblProjectRoles. The number of foreign keys is not a concern as long as it is logically correct

tblProjects:
-pkProjID
-ProjectName
-blnLive boolean - whether the project is live or not

tblProjectRoles
-pkProjectRoleID
-fkProjID (links to tblProjects)
-fkContactID (links to tblContacts)
-fkRoleID (links to tblRoles)

tblContacts
-pkContactID
-ContactName
-ContactEmail

tblRoles
-pkRoleID
-RoleDescription