Thread: help setting up questionnaire database

Originally Posted by buck1109

This is really interesting, for I'm doing the same thing, or attempting to, with multiple check boxes and text fields. Initially, I had it down to a single Answer table, but as the variety of input types was requested, it looks like perhaps a table for each different type of input(?).
I'd also be interested in the example mentioned earlier in the thread, which did combine different input types.
Thanks again.

Hi Buck, welcome to DeveloperBarn

I think it might take a few tables to support the different answer types.

For the example design that I posted, you would add a QuestionTypeID column to the Questions table, which would indicate whether it's a multiple choice, single select, etc. This would be a key in a tabled called QuestionTypes.

I think you're correct in that there may need to be a table to store the answers for each type of question. I've had a few glasses of wine tonight, so now is not the time for me to be designing databases, but now you've intrigued me. So I'll be back with what I think would be a good design

Determining the correct data type is important, indeed. For example, in SQL Server, you have the following: SQL Server Data Types and Ranges (SQL Server 2005 and SQL Server 2000)

Notice the difference in size. If you're only going to have numbers as high as 10, then tinyint would be the best choice. If you're going to have a variable length text field, meaning that it could be any length up to 255, for example, then you would use varchar(255). If you have a fixed-length text field, meaning that it will always have 10 characters, then char(10) would be the data type to use.

As far as having a separate answer table for each question, that doesn't follow database normalization rules. (Read this: Relational Database Normalization Basics - Database Design Help)

Also, see our blog section. don94403 wrote a blog on database schema design, which you may find helpful.

Originally Posted by buck1109

Glad to be in this group!

We're glad to have you

Originally Posted by don94403

The most important rule is to avoid using any numeric type for a data element that is descriptive, rather than a measure of some sort. For example, beginners often make things like zip codes or phone numbers numeric data types. Very wrong! If you're not going to operate with arithmetic, or compare in a numeric way, use some text data type. Beyond that, make sure that whatever data type you use will be big enough to hold any data that you might ever want to store in that field. Don't worry about "saving storage space" unless you're likely to have many millions of records in a table.

Thanks don94403 - much appreciated! The "descriptive vs measurement" aspect is one which I've found to be the most difficult to determine (or predict) at times. Whether the data will be needed in the future to perform computations (for example, in my current project, a question on the form asks "how much of course credit is allocated toward topic x in your course?"). While that might be used for computation, this project primarily uses it in the descriptive manner.

Originally Posted by don94403

I'd be cautious about doing that. That would very likely complicate your application script tremendously, because every question would have a different table structure, making it difficult to develop common routines and functions. I would recommend, instead, a single table (as jmh recommended) that might include one or a few fields that are used only with some question types, or perhaps better, a separate table for, say, "other" that is joined to the question by a foreign key. The same approach can be used to join multiple choice answers to the appropriate question.

If data is gathered in one data-type and later required to function as another data-type, can this be achieved via application coding? I have experienced Javascript which will 'convert' a numeric string to an integer, for example, so perhaps this would be the answer if such a scenario were to be the case in the future?

Thanks for your recommendation for using a separate table for "other" items. I've sketched a preliminary draft of my table design here and would gladly welcome comments or suggestions. Many thanks.

Code:

Questionnaire.ID
Questionnaire.StreamID
Questionnaire.QuestionID
Questionnaire.AnswerID

Stream.ID
Stream.Stream

Question.ID
Question.Question

Answer.ID
Answer.Answer
Answer.Answer2
Answer.Answer3

Answer_Other.ID
Answer_Other.Answer_Other
Answer_Other.Answer_ID (FK)

There are multiple streams per questionnaire, and there can be multiple questions per stream. There is only one answer per question.
(The same 10 questions are used for each of the 8 streams, but with the name of the stream 'plugged in' to the question at the same point in the text, in the application level).

The Questionnaire table contains stream, question, and answer FK's.
The Stream table contains 8 stream topics
The Question table contains 10 questions
The Answer table contains 1 answer per question
The Answer_Other table contains the "other" text detail to expand upon a yes or no question - related to the Answer table through the Answer_ID FK.

Thanks for the generous offer! That would be really interesting.
As for "rank," I'm not sure I follow.
Perhaps this isn't a questionnaire in the truest sense of the term, in that the questions have no rank of importance. (Is that what you referred to?)

Thanks