June 9, 2015 at 7:29 am
I need to create a survey application and store answer in a SQL table.
There are about 50 questions.
Most questions are check box like
[check box ] No [check box] Yes, if yes, specify [Text box]
Where can download a sample database?
June 9, 2015 at 8:44 am
I've seen some places like this with various models set up, but I don't see surveys there: http://www.databaseanswers.org/data_models/. There is a questionnaire, but it's limited in what it shows: http://www.databaseanswers.org/data_models/student_questionnaires/index.htm
What I would suggest is you list a few things you need, and break them up into questions and answers. We can help you design things, but it would be helpful if you give some of it a go in terms of your requirements.
Typically I'd start with something like this:
CREATE TABLE questions
(
questionid INT IDENTITY(1, 1)
PRIMARY KEY
, questiontext VARCHAR(2000)
);
GO
CREATE TABLE survey
(
surveyid INT IDENTITY(1, 1)
PRIMARY KEY
, surveyname VARCHAR(500)
, active BIT
);
GO
CREATE TABLE SurveyQuestions
(
surveyid INT
CONSTRAINT surveyQuestions_Survey_fk
FOREIGN KEY REFERENCES dbo.survey ( surveyid )
, questionid INT
CONSTRAINT surveyquestions_questions_fk
FOREIGN KEY REFERENCES dbo.questions ( questionid )
, questionorder TINYINT
, active BIT
);
GO
CREATE TABLE QuestionAnswers
(
QuestionAnswerid INT CONSTRAINT QuestionAnswers_PK PRIMARY KEY
, questionid INT
CONSTRAINT questionAnswers_questions_fk
FOREIGN KEY REFERENCES dbo.questions ( questionid )
, AnswerText VARCHAR(1000)
);
GO
CREATE TABLE SurveyAnswers
(
userid INT
, surveyid INT
, questionid INT
, questionanswerid INT
);
The last item probably needs a constraint with all of those as keys. It could have questionid removed, but I find it sometimes reduces joins I might need. You can decide how you determine who can answer, if they can answer more than once, do I need dates, etc.
June 9, 2015 at 12:14 pm
adonetok (6/9/2015)
I need to create a survey application and store answer in a SQL table.There are about 50 questions.
Most questions are check box like
[check box ] No [check box] Yes, if yes, specify [Text box]
Where can download a sample database?
Quick question, how big is the survey population (audience)? There are quite few examples out there which are rather easy to find if one searches, also services like SurveyMonkey etc. which might be just what you are looking for. Although a survey may look simple at the first glance, it can quickly become rather difficult and complex to handle, especially if there is any branching or other complexities in the design.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply