I need a few survey table design ideas, please?

SQL Server Administration
1

Greetings experts,

I am trying to design a survey with 6 questions and some comment text boxes.

Users will be presented with 6 questions.

Each question has 4 choices.

4 for Excellent, 3 for Good, 2 for Fair, 1 for Poor.

I have 2 tables so far:

Questions table with:

qID int pk
qText - this will be the question text

Then another table called Choices.

This table has the following:
ChoiceID int PK
qID - fk for qID in Questions
Choices - This is where user selects either 4, 3, 2 or 1
gComments - This is for additional comments / suggestions

Here is where my issue lies.

If a user's choice is either 2 for Fair or 1 for Poor, then a comment pops requiring the user to explain why s/he thinks service is poor or fair.

This comment that pops up, is different from the gComments or additional comments or suggestions.

My question is how do I fit this comment into my table design so these comments that pop up can associated with a particular question where the response is Poor or Fair?

Thanks in advance or your assistance.

2

Add a [GAdditionalComments] column to your [Choices] table?

It would be NULL if the user rates the question as 4 or 3, and perhaps?? NULL if they don't bother to enter an additional comment.

3

Hi Kristen,

I think you perhaps you misunderstood my question?

I already have a field called gAdditionalComments. This is for an optional general comments once all survey questions have been answer.

The issue I am referring to is having one comment box for each question.
By default, this box is hidden. However, if user's choice is either 2 or 1, the box becomes visible so user can enter comment explaining why s/he rated the question fair or poor.

Since there are 6 questions, there will be 6 comment boxes, one for each question.

I just need help figuring out how to design it.

Thank you,

4

To the best of my understanding of your original question, and your clarification of your spec, my answer is to your question. Maybe my [gAdditionalComment] is the same as your existing [gComments] column though ...

[gComments] exists for each answer, to each question. Therefore it is possible to store a Comment against each question (i.e. under program control only where the user providing a rating of 1 or 2)

It seems that, in addition, you have a [gAdditionalComments] column that the user can answer, ONCE, for each questionnaire ... but I can't see that you have a table for that?

If I was designing a Questionaire system I would want it to be able to handle different questionnaires.

I would have a QuestionaireHeader table - that would have an QH_ID and a descriptive name.

Then a QuestionaireItems table containing the individual questions within a specific Questionnaire. That table would have a column for a (Foreign Key) link to QH_ID, and an ITEM column. Also the Text of the Question, and maybe the date/time when it was last changed.

Next a table of Questionnaire Choices / Answers. That would need a column(s) linking to the QuestionaireItem (either columns for both QH_ID and ITEM, or provide an additional Unique ID / Identity column in the QuestionaireItems table)

Then we need tables for the User's actual responses. A UserResponseHeader table - this would link to the QuestionaireHeader QH_ID column (so we know which Questionnaire the user was answering), and have its own unique ID / Identity, e.g. URH_ID and also hold the User's ID / Name, the Date they answered the questionnaire - whatever else you need about their "session". That could include an overall, optional, Comment about the whole thing.

Then a table of UserResponseItems which would have one row for each QuestionaireItem with a link to the UserResponseHeader URH_ID and also to the QuestionaireItem (again: either a combination of QH_ID and ITEM, or provide an additional Unique ID / Identity column in the QuestionaireItems table), and then finally the value of the Choice that they picked, plus also the Comment if their choice was 1 or 2.

5

Thanks again Kristen,

Just so I can follow your suggested design ideas better, I am using different names to try to represent your own table names.

I replace QuestionaireHeader with just Survey.
with following attributes:
SurveyID,
SurveyName

This way, there could be several questionnaires and an admin or user can decide what questionnaire to take. I think this is what your QuestionaireHeader is intended to do?

Then I will replace your QuestionaireItems with SurveyQuestions
This table will have following attributes:
SurveyID FK to Survey table
QuestionID
QuestionName

Then I will replace your Questionnaire Choices / Answers with SurveyChoices with following attributes:
QuestionID FK to SurveyQuestions table
ChoiceID
Choices - Different choices and in our case, four choices for each question

Finally, I will replace your UserResponseHeader with UserResponse with following attributes:
QuestionID FK to SurveyQuestions table
ChoiceID FK to SurveyChoices table
ResponseID
gAdditionalComments - This comment box is for each questionnaire but is optional
DateTaken - Date survey was taken
UserId - ID of user who took the survey - this is optional.

That's what I have come up with.

What is still not clear to me is what table to put gComments for EACH question where user's choice is either 2 for fair, 1 for poor.

Thanks for all your help

6

Here's some design ideas for you to consider. I made all the table names plural, as that is much more common; use either singular of plural, just don't mix them. Anticipated primary/unique key columns are in bold. Of course any/all of these may have additional columns as well.

Surveys ( SurveyID, SurveyName, DateCreated )

--allows the same q to be used on different surveys without duplication;
--also allows qs to be stored before they are assigned to a specific survey(s)
Questions ( QuestionID, QuestionName, IsFollowUpQuestion )

--identifies the specific Qs that make up a given Survey
SurveyQuestions ( SurveyID, QuestionID, DateAdded )

--FollowUpQuestionID is present only if there is a follow up q for that choice,
--in this case so far, for choices of 1 or 2
SurveyChoices ( SurveyID, QuestionID, ChoiceID, Choice, DateAdded, FollowUpQuestionID )

--a separate header table so the date, comments, etc., are only stored once
UserResponseHeaders ( ResponseID, UserID, SurveyID, AdditionalComments, DateTaken )

UserResponses ( ResponseID, QuestionID, ChoiceID, Choice, FollowUpComments )

1 Like
7

Sounds like you have one table for that job, where I split it into two?

If so that may where the confusion is.

Your "DateTaken - Date survey was taken, UserId - ID of user who took the survey - this is optional." should be in a "Response Header" table. i.e. that data occurs only once in the database, not on every response to any question.

Presumably a given UserID can answer more than one Survey? So you need a combination of User ID plus Survey ID (in the "Response Header" table) to identify "A specific user answering A specific Questionnaire" and then, in a separate "User Response Items" table you need to store their individual answers to the separate questions in that survey.

It is in that, "User Response Items", table that you can store a Comment against the 1, 2, 3, 4 rating value that the user provides.

Might be a bit difficult to grasp given the wordy-nature of this media ... Scott's answer with table / column definitions is probably an easier way to understand it. Please to see that Scott and I are saying much the same thing though :slight_smile: