Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    5

    best db table design for 107 question 'questionnaire'

    Hi all, just need a bit of advice on database design. I understand database design but haven't done it in a while so here's the need:

    I need to store the answers to 107 questions. Each question will have a yes answer and a no answer, but the no answer then follows with a followup question. Most of the questions will go 2 (possibly) 3 levels down (2nd and 3rd question but not all.

    My employees will pull up the questionnaire to fill out the answers given by the clients (they'll select the drop down associated with the question as yes or no. In the end, we can pull a report based on the customer's id and the answers associated with it.

    Would this be the most obvious design:

    1. table for employee info
    2. table for customer info
    3. table for list of questions (essentially 107 rows... lookup table?)
    4. table for 2nd level questions
    5. table for 3rd level questions

    Would I need a table just for the answers to the questions associated with a specific client?

    Any direction to move in (if I'm way off) is greatly appreciated!

    Thanks,

    James...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Meh. Looks ok to me. With three levels of questions you might consider a recursive table, but id depends upon whether you feel up to the more complex sql required.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2013
    Posts
    5
    Thanks Blindman! The 2nd and possible 3rd question will simply be a yes or no question (and their choice (e.g. a no) will involve an error code being saved) so not very complex.

  4. #4
    Join Date
    Apr 2013
    Posts
    5
    Blindman, I just thought of something - for my table of questions, how is their answer stored (since the question table is just questions)? Their answers would be stored in a separate table associated only with them, so would the 'customer table' contain their own info and the answers to the 107 questions? As separate fields for the 107?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You can have a nearly identical set of table to store answers.
    Of course, the more complicated the schema becomes, the more incentive you have for switching to a recursive table structure. Personally, I'd use the recursive option, but I'm very comfortable writing recursive SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2013
    Posts
    5
    blindman, ignoring the recursive table option for a moment, assuming there's no multiple levels for each question, what i don't understand is if i'll have 107 fields in a table holding all the answers (for every question) with the unique id of the table being the user's unique account number) or if each question would be a separate table? what's more efficient or a better design?

  7. #7
    Join Date
    May 2013
    Posts
    4
    We've had quite a lot of success using Limesurvey for surveys. You can see breakdowns of results on Limesurvey and extract the data to applications such as Excel if you want to do further analysis.

    May be worth a look as it can be tailored as you want it, and is easy to send out to who you need to have sight of it (and send reminders if needed!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •