Data Collection Questionnaires

Question

Post reply

Data Collection Questionnaires

simflex-897410

Hall of Fame

Points: 3881
More actions
January 15, 2012 at 2:01 pm

#249083

Greetings Super Experts,
I need your expertise assistance, please.
We are tasked with coming up with Data Collection questionnaire for our company.
History: The company has over 49 departments. Each department has its own data collection survey. Some of the survey questions are more than 100 questions.
Design Question: I am trying to design the db before the front end coding and I wanted to make sure that the db is designed correctly.
Here is what I have so far.
TABLE: DEPARTMENTS
DeptId int primary key
DeptName nvarchar(50)
TABLE: QUESTIONTYPES
QuestionTypeId int Primary key
QuestionType nvarchar(255)
TABLE: QUESTIONS
QuestionId int primary key
DeptId int Foreign Key(DEPARTMENTS)
QuestionTypeId Foreign Key(QUESTIONTYPES)
Question_Descriptions nvarchar(255)
TABLE: ANSWERS
AnswerId int Primary Key
Answers nvarchar(2000)
TABLE: SURVEYS
SurveyId int primary key
QuestionId int Foreign Key(QUESTIONS)
AnswerId int Foreign Key (ANSWERS)
DeptId int foreign key (DEPARTMENTS)
This is what I have so far but here is what is needed.
- Each department has its own questions.
- At end of the survey, a tally is needed to show a breakdown of how the questions are answered.
Can you please tell me what additional changes I need to make this design complete, sort of?
Many thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

- At end of the survey, a tally is needed to show a breakdown of how the questions are answered.

TABLE: ANSWERS

AnswerId int Primary Key

Answers nvarchar(2000)

with a nvarchar answer...how do you propose to evaluate the "answers"?

..is is that there is an "answer"...or something else?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

J, thanks for your prompt response.

Each question will have a question Type. Is it radio button, checkbox, comment box?

I am not really sure if this is relevant but that's what I have in mind with question Type.

As for your question, each question must have an answer and so what I am hoping for is for experts like you to help with what you think I am missing or designed incorrectly.

Again, thanks alot for your prompt response.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 3

www.surveymonkey.com

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You have a few things not quite right.

The questions table does not (and should not have) a reference to department. The question belongs to a survey and the survery belongs to a department.

I don't think you want an AnswerID in your survey. A survery does not have answers. You should probably have something like a Responses table. Then a response belongs to a survey and an answer belongs to a response.

In other words.

TABLE: DEPARTMENTS

DeptId int primary key

DeptName nvarchar(50)

TABLE: QUESTIONTYPES

QuestionTypeId int Primary key

QuestionType nvarchar(255)

TABLE: QUESTIONS

QuestionId int primary key

DeptId int Foreign Key(DEPARTMENTS)

QuestionTypeId Foreign Key(QUESTIONTYPES)

Question_Descriptions nvarchar(255)

SurveyId : foreign key to Survey

TABLE: ANSWERS

AnswerId int Primary Key

Answers nvarchar(2000)

QuestionID foreign key to Questions

ResponseID foreign key to Response

TABLE: SURVEYS

SurveyId int primary key

QuestionId int Foreign Key(QUESTIONS)

AnswerId int Foreign Key (ANSWERS)

DeptId int foreign key (DEPARTMENTS)

New Table: Response

ResponseID int primary key

SurveyID foreign key to Survey

This type of architecture will let you your collection of surverys, each with a collection of questions. Then you have a collection of Responses, each with a collection of answers.

Or follow the previous post and go with one that is already written. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 5

Thanks Sean and Eric.

I actually, Sean, I had not sean Eric's response till now.

The SurveyMonkey link is written in php and I don't know php.

I did, however, download a copy of the survey, installed the sql server bit but it is so comprehensive and so convolulated that most of the tables are irrelevant to what we are trying to do.