Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    14

    Unanswered: DB Design for Multiple Surveys

    I know this has been asked before and I've searched for it but don't see any answers so I'll have to ask it myself. Forgive me if it's a little convoluted.

    I'm working on a DB design for surveys (polls, quizzes, etc). There'll be multiple surveys consisting of multiple polls (individual questions) and the questions will be of different types - Yes/No, multiple choice, single answer, etc. No free-form text (thank god!). Users can also rate surveys and propose their own. I'll also need to keep records of all responses so I can score the surveys that are tests (as opposed to those that are just informational - no "correct" answer).

    So the basic entities I'm dealing with are Users, Surveys, Questions, Answers and Responses. Here's the diagram of what I've got so far:

    http://wkwork.smugmug.com/photos/488310674_Dij3f-O.jpg

    The problem is that the answers table should contain the possible answers to the question and the responses table should contain the actual responses given. So really the AnswerYesNo and AnswerInt should be in the responses table right? Then I wouldn't need the join between responses and answers, right?

    I guess my mind is running circles trying to sort out the difference between possible answers, correct and incorrect answers and the actual selected answers. There's a few ways to handle them but I'm not sure what the most efficient would be.

    Is that too much to ask?

  2. #2
    Join Date
    Jul 2007
    Posts
    14
    Ok I'm still working on this so I apologize for replying to my own thread but I added a table between Questions and Answers call CorrectAnswers. It has a FK to both other tables. That means I won't have a million duplicate rows in the Answers table saying "Yes" and "No" for each Yes/No question.

    There's going to be a lot of real-time reporting in my app so efficiency is my number one concern. Here's the revised design:

    http://wkwork.smugmug.com/photos/488340496_7Ynjg-O.jpg

    I removed a couple answer columns for different types and just made a single varchar column for conversion on the fly. It's still ugly but maybe a little more efficient than having to sort through 3 columns to find one answer... And added a "Common" flag for the answers. May use that to sort the wheat from the chaffe for selection lists and such. Maybe I just need an AnswerType table.

    I'm still not there though. I can feel it.
    Last edited by wkwork; 03-09-09 at 20:01.

  3. #3
    Join Date
    Jul 2007
    Posts
    14
    Yeah I knew I was on the wrong track. What if the answer is an integer? I can't have one row in my answers table for every possible number someone might use to answer a question!

    I may have to just scale back my normalization and live with the fact that I'll have one row for every possible answer of every single question. That irritates the perfectionist in me but maybe it's the only workable answer.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What does FLAG indicates? Yes/No, multiple choice, single answer, ... QUESTION, that type of information?

    Based on your second solution. :
    - remove the RESPONSE - ANSWER FK.
    - add a RESPONSE - QUESTION FK, like in your first solution.

    • You have a number of SURVEYs.
    • A SURVEY contains multiple QUESTIONs.
    • A QUESTION belongs to 1 and only 1 SURVEY (are you sure about this?)
    • A QUESTION has a number of possible ANSWERs.
    • An ANSWER belongs to 1 and only 1 QUESTION (not strictly true, but I would keep it that way).
    • A QUESTION can have a number of RESPONSEs.
    • A RESPONSE belongs to 1 USER and 1 QUESTION
    • A USER can only give 1 RESPONSE to a given QUESTION (enforce business rule with extra UNIQUE INDEX on UserId, QuestionId in RESPONSE).
    • At most one of those ANSWERS per QUESTION is correct (enforce business rule with extra UNIQUE INDEX on QuestionId in CORRECTANSWER).

    I would keep the three columns AnswerText, AnswerYesNo and AnswerInt (in both ANSWER and RESPONSE) of your first solution. It's a practical solution to the problem at hand.
    Based on FlagID, your program can render a selection list or radio button interface for multiple-choice and YesNo questions and an empty field for single answers.

    It will be easy to verify the responses versus the CORRECTANSWER.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2007
    Posts
    14
    Thanks Wim. The Flag table is for user generated surveys. So users can flag anything that's unclear, incorrect or violates the TOS. Pass the MaxFlags threshhold for a given problem, and that question is deactivated. If there are less than MinQuestions in the Survey that are active, then the whole Survey is deactivated. (Which reminds me I need an Active column on Surveys)

    * A QUESTION belongs to 1 and only 1 SURVEY (are you sure about this?)

    Yes. For reporting purposes, I need to compare one user's survey against another uniquely and cross feeding questions would just muddy the numbers I think.

    * A QUESTION has a number of possible ANSWERs.
    * An ANSWER belongs to 1 and only 1 QUESTION (not strictly true, but I would keep it that way).

    This is one of my sticking points. The answers Yes and No will probably belong to a lot of questions. I'd like to avoid replicating all those Yes and No answers (or single digit integers, or other common answers).

    * A QUESTION can have a number of RESPONSEs.
    * A RESPONSE belongs to 1 USER and 1 QUESTION
    * A USER can only give 1 RESPONSE to a given QUESTION (enforce business rule with extra UNIQUE INDEX on UserId, QuestionId in RESPONSE).

    Not necessarily. Multiple choice questions are par for the course. UserID / QuestionID can't be unique.

    * At most one of those ANSWERS per QUESTION is correct (enforce business rule with extra UNIQUE INDEX on QuestionId in CORRECTANSWER).

    Again, multiple choice questions are also possible.

    You're saying I should keep the columns classifying the answers (AnswerInt, AnswerYesNo)? That means every time I read an answer, I have to query both, eliminate the null value and accept the non-null as the final answer. That seems like a LOT of overhead because those answers are the core of my data and I'll be hitting them like crazy for every report.

    Also be aware that AnswerText is NOT for free-form text answers. That's the possible answer that will be shown to the user for selection. Like if the question is "What's you favorite color?", there might be 3 records in Answer with AnswerText "Red", "Green" and "Blue". See what I mean?

    Remember:

    ANSWERS are the options presented to the user for selection.
    RESPONSES are the selections chosen by the user.

  6. #6
    Join Date
    Jul 2007
    Posts
    14
    What if I separate "tests" from "surveys"?

    Surveys are just opinion or information based - there is no "correct" answer. If I separate the two out, then I've got a simpler structure for surveys and a more focused structure for tests.

    The more I think about it, the more that sounds like the missing link...

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The Flag table is for user generated surveys. So users can flag anything that's unclear, incorrect or violates the TOS.
    Make the FK optional, not mandatory.
    * A QUESTION belongs to 1 and only 1 SURVEY (are you sure about this?)

    Yes. For reporting purposes, I need to compare one user's survey against another uniquely and cross feeding questions would just muddy the numbers I think.
    Create a junction table SURVEY_QUESTION between SURVEY and QUESTION, let RESPONSE have a FK to SURVEY_QUESTION instead of to QUESTION.

    * An ANSWER belongs to 1 and only 1 QUESTION (not strictly true, but I would keep it that way).

    This is one of my sticking points. The answers Yes and No will probably belong to a lot of questions. I'd like to avoid replicating all those Yes and No answers (or single digit integers, or other common answers).
    Whatever you decide, just make sure that if you change a common answer, that you are not changing the answers of all the related QUESTIONS.
    * A USER can only give 1 RESPONSE to a given QUESTION (enforce business rule with extra UNIQUE INDEX on UserId, QuestionId in RESPONSE).

    Not necessarily. Multiple choice questions are par for the course. UserID / QuestionID can't be unique.
    Do you mean 1 USER can give 5 RESPONSES to 1 QUESTION? Perhaps in time he/she can do the same SURVEY multiple times. Expand your datamodel with a table, lets call it DOTEST, that stores the datetime the DOTEST was done, the USER who did it and the SURVEY done. Then replace UserID in RESPONSE with DoTestID. I still believe that per DOTEST, a USER can only give 1 RESPONSE to a given QUESTION. I agree a QUESTION can have multiple possible ANSWERS in the case of multiple choice.
    As you stated yourself :
    Remember:
    ANSWERS are the options presented to the user for selection.
    RESPONSES are the selections chosen by the user.
    * At most one of those ANSWERS per QUESTION is correct (enforce business rule with extra UNIQUE INDEX on QuestionId in CORRECTANSWER).

    Again, multiple choice questions are also possible.
    Unless you mean multiple choice QUESTIONs can have multiple CORRECTANSWERs, enforce this rune with a UNIQUE INDEX.
    You're saying I should keep the columns classifying the answers (AnswerInt, AnswerYesNo)?
    I recall what I said.
    What if I separate "tests" from "surveys"?
    Make a second data model in which you separate "tests" from "surveys", compare both data models, think about the programming and reporting implications. Only then decide.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jul 2007
    Posts
    14
    Create a junction table SURVEY_QUESTION between SURVEY and QUESTION, let RESPONSE have a FK to SURVEY_QUESTION instead of to QUESTION.
    Well I kinda see the answer as the basic unit of the data. Users select answers that belong to questions. Questions belong to surveys, etc. I don't see how I could track a user's answers for later analysis with only a link to a survey-question table.

    Do you mean 1 USER can give 5 RESPONSES to 1 QUESTION? Perhaps in time he/she can do the same SURVEY multiple times.
    No I mean a user can select multiple answers in response to one question. For example, I might have a question like "Which of the following were once presidential candidates?" with answers like "Ronald Reagan", "Walter Mondale" and "James Carville". A correct response would create two new rows in the response table containing the QuestionID for that question and the AnswerID for each of the correct answers.

    I'm not sure what the proposed DOTEST would accomplish though...

    I think I may not be explaining the problem adequately and part of it is that my design just doesn't "feel right" from a normalization perspective. I see the crux of the issue as the consolidation of disparate data types and an easier way to unify those types for reporting. But it's more complicated by the correct vs. incorrect answers as well as the test vs. survey answers.

    Maybe it would help to propose the db structure you would use given my design goals (which I think you understand)? Too much work?

  9. #9
    Join Date
    Jul 2007
    Posts
    14
    I think I found it. For anyone interested, I think the problem was that I was confusing the answers and the responses of course.

    Viewing the pre-fab answers as just properties of the question cleared it up in my head. So I made a response type table that'll say whether the user responded with one of the answers or with an integer or a Yes/No (bit) answer.

    The correct / incorrect thing is handled easily but making a "correct" bit column on the answers table.

    This feels right:

    http://wkwork.smugmug.com/photos/492685312_kcEDy-O.jpg

    Any thought, let me know. And thanks for the help.

Posting Permissions

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