July 26, 2015 at 7:24 pm
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.
July 26, 2015 at 8:53 pm
For starters, your names are HORRIBLE.
Please take a look at your tables and names. Spend a few minutes and make them meaningful. I mean meaningful to someone other than yourself.
As for your structure, do you plan on having an answer table? That's the logical entity to store this data.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 26, 2015 at 9:17 pm
First of all, thanks for your response/
Well, I didn't think that Questions table is bad.
How about SurveyQuestions with the attributes that I indicated/
Then SurveyChoices with the attributes I listed.
Finally, SurveyAnswers table with following:
AnswerId
qID - questionID, FK to SurveyQuestions table
ChoceID - FK to SurveyChoices table
dateTaken - Date survey was taken
Now, back to my original question.
When a user chooses either 2 for Fair or 1 for Poor, a textbox pops up asking the user for comments.
How do I fit this textbox into my table design?
July 27, 2015 at 8:12 am
Ok, the questions table is not bad? What is qID? qText? Would Question_ID make more sense to someone? How about Question_Text?
In code, nobody but a programmer ever looks at it. But in the database, EVERYONE looks at it. Give everyone the ability to look at a field and understand what it is and what it does.
As for your structure:
1.
How do I fit this textbox into my table design?
Does the structure of the database drive the design of the program? Or does the UI drive the design of the database? If its the latter, then your design process is somewhat flawed. Describe and implement the business rules of your project. Design the database. Then, design the UI. The databases purpose is to enforce the business rules.
2.
When a user chooses either 2 for Fair or 1 for Poor, a textbox pops up asking the user for comments.
Isn't this an answer, or to be more specific, a PART of an answer?
As i see it, the tables may be as follows:
Survey table = Defines the survey.
Questions table = Defines the questions that are part of a survey.
Answers table = Stores the answers to the questions of a survey.
Answers_Text (bad name!) = Stores the user entered text related to one or many questions.
You could certainly make the answers text part of the answers table, but it's optional and does not apply to every answer.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 27, 2015 at 9:50 am
Thanks Michael.
Your ideas about the Answer_Text table makes sense.
Of course, I will change the name.
The only clarification I would need, if you don't mind is that since I have 6 questions and could grow, and since each question has its own comments box that is made visible based on user's response, how will just one comments field in Answer_text table be enough?
In other words, if a user answers fair or poor for every question and the comments box pops up for comments, will that one field be sufficient?
July 27, 2015 at 10:16 am
I have 6 questions and could grow, and since each question has its own comments box that is made visible based on user's response, how will just one comments field in Answer_text table be enough?
QUESTIONS do not have a comments box associated with them, ANSWERS do.
Right now, you have one survey consisting of 6 questions. All of these 6 questions may or may not contain additional data provided by the user. This is determined by the answer provided.
So, right now, you would have:
One record in the survey table.
6 records in the questions table.
When a user completes the survey, you will also have:
6 records in the answer table
0 to 6 records in the answer_text table.
I am missing something? Where you planning on making the answer table a pre-defined set of possible answers? In that case, where were you planning on saving the actual answers?
There are some gotcha's in that are not taken into consideration in the design.
A few scenarios to consider:
If a user answers X to question 2, then skip to question 10.
If a user answers X to question 2, then X number of additional questions appear that need to be answered.
A question requires an answer, and an additional set of "sub answers" then appear. This may differ based upon the answer.
Questions worded like: "Based upon the answer you provided on question 10, would you say that this is A. Important, B.Not Imnportant.
And so forth.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply