Thread: help setting up questionnaire database

Hey everyone its peebman2000, i'm currently building a .Net questionnaire application. I've coded some of the other functionality of the app, now I need to build core part of the app.

I'm having trouble decideing on how to build the database for the questions and answers for the questionnaire part.

I thought of building it this way:
Table:
Question(ques_id, question_text)

Answer(ans_id, answer)''''this hold all possible 250 answers user can choose from''''''

User(user_id, username)

Questiontoanswer(quesans_id, ques_id, ans_id)'''this holds the questionid along with the possible answer id

Questionuseranswer(ques_user_ans_id, user_id, quesans_id)''''this table is used to look up the question and the posssible answer the user selected

This could seem to work, but coding wise i don't feel this is the best set up for the questionnaire application, because each question has either a partA and Part B.

My question is does anyone have a suggestion on the best way to build a question and answer database in SQL?

Let me know and I appreciate any help, thanks.

Hey thanks jmurrayhead, you are right I should have had the whole database designed before I began, but I didn't.

I like this set up, let me see if I can work with this.

Thanks.

Originally Posted by jmurrayhead

For future reference, it's usually best to start of with the database design BEFORE beginning your coding

Anywho, I've built some type of questionaire with this type of setup:

questionaire
questionaireID int seed
title varchar(100)

questions
questionID int seed
questionaireID int [relationship with questionaire.questionaireID]
title varchar(255)

answers
answerID int seed
questionID int [relationship with questions.questionID]
answer varchar(255)
isCorrect bit

userAnswers
userAnswerID int seed
questionID int [relationship with questions.questionID]
answer varchar(255)
userID int
responseDate datetime

You may want to have an additional table to store which questionaires a user took, when it was taken, and if they were completed, as well. Hope this gives you some ideas

Hey, nice schema, jmh! Two questions:

1. Is the "bit" data type efficient in such a case? I know it's not likely to make any practical difference, but I would assume that if you only have one such field in a record, it's still going to occupy at least a byte of storage, because of boundaries. Just a curiosity question.
2. This looks solid for a multiple-choice questionnaire. Have you done questionnaires that allow other answer types, such as multiple checkboxes or user text answers?

Years ago I remember seeing a script for a schema for a flexible questionnaire, allowing a mixture of multiple-choice, yes-no, checkboxes and open text. It was a doozy!

I like don94403 2nd question. My questionnaire is like what don is talking about in his 2nd question, there some multiple choice answers and alot of text answers in my questionnaire.

I haven't yet began building the database yet, because I was focus on the reminder emails, but i'll be building the dateabase this week.

Any other ideas on a questionnarie database with numerous text answers as well as multiple choice answers let me know.

Thanks.

Originally Posted by jmurrayhead

Thanks Don The bit field in this scenario is to indicate whether, out of the available answers for a question, which is the correct answer. It appears to be logical to me, but I would love to hear any other suggestions on the matter.

You forced me to resort to RTFM -- lots of things changed between MySQL 4.0x and 5.0x, but for much of the time, BIT and BOOL and BOOLEAN are synonyms for TINYINT(1). It appears to me that BOOL and BOOLEAN make better choices, because they can be tested == or != False or True, as well as 0 or 1, making scripts perhaps a bit (no pun intended) easier to read. Other than that, I guess there's no difference, although maybe, to be absolutely catholic across all versions, TINYINT(1) may be the most reliable to use.

Indeed, this design was intended solely for multiple choice. I've never actually done one with to use different answer types, however, I have thought about a few schemas that might work.

I've occasionally thought about this problem space, but never actually designed a questionnaire database. Seeing the number of questionnaires on the net, though, I don't think they need one more expert in this area!

Hey Jmurrayhead, I would be interested in hearing your ideas on schemas or tables for a questionnaire that has several different answer types? (i'm using your first response, but dealing with several different answers i'm trying to set that part up)

Below is some of the questions I'm working with:


Quest#6:
What is the main product or service for your job?(please answer below)


How often do you peform these roles in your work group?
Answer selections are:
Little or None ---- Occasional ----- Frequent

Work supports or assists others in the group:

Produces the product or delivers the service to the group:

Reviews processes and procedures for the group:

and so on.


Another question is, (question #7)
What is your knowledge base? (please choose all that apply)
And give an example for each selection:

*The astrick represents a checkbox

*Budgeting
example (textbox)

*Bookkeeping
example (textbox)

*Accounting
example (textbox)

One more question, (question #8)
Which type of contacts to you communicate with?
check all that applies:

Give out information: (Select either State workers, Federal works, General public, Disabled public)

Explain information: ''

Settle problems: ''

Listen and comfort: ''

etc,;, etc.(there are more, but didn't list)

For this question the user can choose all 4 possibly for each phrase.

There are 30 questions with several answers for each, I was thinking of just create 30 separate tables, each question has a table that stores the userid, question id and the users answers.

But I don't know how efficient that is with I/O, because in the app I'll have to read from all 30 tables to display the user answers.

I don't know, any suggestions, dude.