October 14, 2005 at 10:10 am
Hello,
I need to design a SQL Server database for use in a large survey questionnaire but I'm not sure what's the best approach to design. The questionnaire will have well over 100 questions, and the answers for each question could be multiple choice, checkboxes or open ended types.
My way of thinking is that I could create a parent table for each question (with a respondant ID as primary key), and then a child table to store the answers for that question. But then there's the problem of creating too many tables for all the questions, and the "answers table" could be either storing one value (for the m/c), multiple values (for the checkboxes) or a text string (for the open ended answers).
Is there a simplified way of doing all this that maximizes efficiency?
Thanks
October 14, 2005 at 11:23 am
Use one table to record results like a TPS. You can adjust each column to fit the type of answer you expect to receive from the question, from boolean type to varchar 500 for essays or something. For checkboxes, I would use a binary column with one binary bit per checkbox: 1 = checked, 0 = not checked.
I don't think making multiple tables would be a good idea, but I don't have much experience with data structures.
I would have one table for answers, a second for respondants, and maybe a third for question descriptions?
Also, how will you be using this data once it's captured? Data that is queried heavily using huge datasets might be better off in a data warehouse type of environment for speed (although this type of structure is much less economical on DB size).
Definitely get more opinions, as I'm pretty new to SQL Srvr.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply