locked
Need help in Database Design RRS feed

  • Question

  • Hi,

    I am working on SQL server 2000.

    I have scenario like I need to show the predefined answer/result based on the answers provided by the users for a series of questions from Front End (ASP.NET Web page) for the given category.

    The sequence of questions may vary based on the selected answer of previous question.

    Eg:

    1. Question 1

    Answers:

    a) Option A

    b) Option B

    c) Option C

    User Action(Lets Say, the user selects the answer for the Question 1 from Dropdown):

    Case 1:

    Option A: Then show Question 2.

    Case 2:

    Option B: Then show Question 3.

    Case 3:

    Option C: Then show Question 2.

    This will go like Survery until the Final question arrives.

    Similarly, the sequence of trailing questions may vary from 1....n.

    After the Final question, predefined result should be displayed on the page based on the answers provided by the users for the series of asked questions.

    (For each category, different set of questions)

    For this scenario, I have create a sample DB design as follows,

    Category_Master
    Category ID
    Category Value

    Question_Master
    Question Id
    Question
    Answer_Type_Id

    Category_Question_Xref
    Category ID
    Question ID

    Answer_Type_Master
    Answer_Type_ID
    Answer_Type (Radio, Dropdown)

    Answer_Master
    Answer ID
    Answer_Value_ID
    Answer_Value

    Question_Answer_Xref
    Question_Id
    Answer_Id

    Question_Answer_Value_Referenced_Question_Xref
    Question_ID
    Answer_ID
    Answer_Value_ID
    Referenced_Question_ID -- This will show me which question needs to be displayed after the user selects an answer for the given question

    Others are Self explanatory.

    Here is my question.

    How can I relate the different set of questions and its answer to arrive the final answer?

    (Let me say, I have a seperate "Final Answer" table to hold the different answer values to be displayed on screen)

    Since number (1... n) of questions are vary based on the answer selection of previous question and category, its not possible to have a predefined columns in tables for these details to map the required Final answer.

    Is there any suitable DB design solution for this scenario?

    XML may not be a suitable option since I'm using SQL 2000 and also I need to Map and hardcode the different set of question & its answer values and final answer.

    Kindly help me to solve this scenario. Thanks.



    Monday, June 20, 2011 4:38 PM

Answers

  • Ah, so Answer_Id is an answer group, because the same answers are always grouped together the same way, no matter the question.

    Can an answer be a different answer type for a different question? (eg yes/no radio for question 1, but yes/no dropdown for question 2)

    If Referenced_Question_ID "will show me which question needs to be displayed after the user selects an answer for the given question", doesn't that limit the questions to one path each (per answer), no matter which category they are in? Or is that your question?

    Isn't Question_Answer_Xref redundant, as it can be derived from Question_Answer_Value_Referenced_Question_Xref? If so, the Answer id concept itself is not needed, being the contents are spelled out in detail in Question_Answer_Value_Referenced_Question_Xref. Unless there is a default path, and only the exceptions to the default are listed there.

    Further along the same reasoning, assuming that AswerId isn't required, why even have an Answer_Master?

    Just playing around, here's what i would propose as the schema:

    Question_Type(Name (PK)) --Radio, Dropdown
    Question(Id (PK), Text, Question_Type (FK))

    Question_Answer(Question (PK, FK), Answer_Text (PK))

    Category(Name (PK), Description)
    Category_Question(Category (PK, FK), Question (PK), Answer_Text (PK), Next_Question)
    FK: (Question, Answer_Text) => Question_Answer
    FK: (Category, Next_Question) => Category_Question

    For the final answer, leave Next_Question NULL.


    Tuesday, June 21, 2011 11:17 AM
    Answerer
  • >repetitive answers like yes/no...Integer columns in tables will perform better than Text columns

    An INTEGER is 4 bytes, Yes is 3 bytes, No is 2 bytes. Though, perhaps SMALLINT will work for you (+/-32,768) in which case you would save a byte on the Yes replies.

    If the answers are short, i don't think it will perform (recognizably) better. Plus, using an id just adds a new TABLE to save space. Considering all the complexity added to support that, is it really worth it? How much space are you saving? What performance gains are expected?

    If the answers are long, they are probably not reused.

    If the answers are long, and they are reused, perhaps then a lookup TABLE is worth it (with all its complexity).

    Because the answers are in two TABLEs (choices and paths) there is also a possibility the TABLEs will fall out of sync, and orphaned ansewrs will be floating in the path TABLE. Because of that, i am strongly feeling to not reuse answers. I don't see any benefit to it.

    Now, if the answers could be in more than one language...
    Tuesday, June 21, 2011 10:29 PM
    Answerer
  • I was suggesting a redesign as it would not only normalize the data, it would--i think--obviate this problem you are asking to resolve. But that's just wishful thinking. :)

    If the issue has to do with different paths due to different categories, wouldn't adding Category to Question_Answer_Value_Referenced_Question_Xref solve that issue? With category in the record, it would clearly separate paths by category. For the path to stop (final answer) keep Referenced_Question_ID NULL.

    If that does not resolve the issue, i am not fully understanding it. So, please explain it once more.


    Wednesday, June 22, 2011 1:07 PM
    Answerer

All replies

  • Can a question be in more than one category?

    Can an answer be used for more than one question?

    What is the difference between Answer_Value_ID and Answer ID?

    Monday, June 20, 2011 6:07 PM
    Answerer
  • Can a question be in more than one category?  -- Yes

    Can an answer be used for more than one question? -- Yes (It depends like Yes/No values)

    Difference between Answer_Value_ID and Answer ID:

    Let me assume, I have a question 1 for the given category. For this question 1, I'm having a list of values available in dropdown, on which any one can be selected as an answer.

    So I group the List of Values of answer under Answer_Id and For each value I provided the Answer_Value_Id.

    As mentioned in the example in my post,

    Answer_Id     Answer_Value_Id     Answer_Value

    1                  1                           Option A

    1                  2                           Option B

    1                  3                           Option C

    Hope this answers your question.

    My Answer Type will be only 2 options. Either Radio or dropdown. So No free text values as answers.



    Tuesday, June 21, 2011 7:25 AM
  • Ah, so Answer_Id is an answer group, because the same answers are always grouped together the same way, no matter the question.

    Can an answer be a different answer type for a different question? (eg yes/no radio for question 1, but yes/no dropdown for question 2)

    If Referenced_Question_ID "will show me which question needs to be displayed after the user selects an answer for the given question", doesn't that limit the questions to one path each (per answer), no matter which category they are in? Or is that your question?

    Isn't Question_Answer_Xref redundant, as it can be derived from Question_Answer_Value_Referenced_Question_Xref? If so, the Answer id concept itself is not needed, being the contents are spelled out in detail in Question_Answer_Value_Referenced_Question_Xref. Unless there is a default path, and only the exceptions to the default are listed there.

    Further along the same reasoning, assuming that AswerId isn't required, why even have an Answer_Master?

    Just playing around, here's what i would propose as the schema:

    Question_Type(Name (PK)) --Radio, Dropdown
    Question(Id (PK), Text, Question_Type (FK))

    Question_Answer(Question (PK, FK), Answer_Text (PK))

    Category(Name (PK), Description)
    Category_Question(Category (PK, FK), Question (PK), Answer_Text (PK), Next_Question)
    FK: (Question, Answer_Text) => Question_Answer
    FK: (Category, Next_Question) => Category_Question

    For the final answer, leave Next_Question NULL.


    Tuesday, June 21, 2011 11:17 AM
    Answerer
  • Can an answer be a different answer type for a different question? (eg yes/no radio for question 1, but yes/no dropdown for question 2) -- Yes. So Only I placed Answer_Type_Id in Question Master.

    If Referenced_Question_ID "will show me which question needs to be displayed after the user selects an answer for the given question", doesn't that limit the questions to one path each (per answer), no matter which category they are in? Or is that your question?  -- Thanks for this question. I will add Category_ID in Question_Answer_Value_Referenced_Question_Xref table.

    Isn't Question_Answer_Xref redundant, as it can be derived from Question_Answer_Value_Referenced_Question_Xref? If so, the Answer id concept itself is not needed, being the contents are spelled out in detail in Question_Answer_Value_Referenced_Question_Xref.

    -- This will cause the repetitive answers like yes/no to get populated more in tables. The same text values will be repetitve in Answer_Text column as per proposed schema. I feel, Integer columns in tables will perform better than Text columns while we assign Keys (FK or PK) on those columns.

    And we are deviating from the actual question.

    I have no issues until the current design. My question is I have to display a result based on the series of questions and its answers provided by the users. How can I map these? 

    After the user selects the answer for the final question, I have to collect all the question ID's and its corresponding Answer Value Id's and then based on these I have to display the result.

    Lets say, The Final Result is stored in a table.

    ResultID     Result Text

    1               Final Result: Some Text based on the user answers.

    So, How should I map the series of question Id's and its Answer Value Id's to the Final ResultID?




    • Edited by Sivakumarrd Wednesday, June 22, 2011 8:39 AM
    Tuesday, June 21, 2011 12:26 PM
  • >repetitive answers like yes/no...Integer columns in tables will perform better than Text columns

    An INTEGER is 4 bytes, Yes is 3 bytes, No is 2 bytes. Though, perhaps SMALLINT will work for you (+/-32,768) in which case you would save a byte on the Yes replies.

    If the answers are short, i don't think it will perform (recognizably) better. Plus, using an id just adds a new TABLE to save space. Considering all the complexity added to support that, is it really worth it? How much space are you saving? What performance gains are expected?

    If the answers are long, they are probably not reused.

    If the answers are long, and they are reused, perhaps then a lookup TABLE is worth it (with all its complexity).

    Because the answers are in two TABLEs (choices and paths) there is also a possibility the TABLEs will fall out of sync, and orphaned ansewrs will be floating in the path TABLE. Because of that, i am strongly feeling to not reuse answers. I don't see any benefit to it.

    Now, if the answers could be in more than one language...
    Tuesday, June 21, 2011 10:29 PM
    Answerer
  • Thanks for your explanation Brain.

    Currently I am looking for the solution on my issue. Kindly help on that. Thanks.

    Wednesday, June 22, 2011 6:39 AM
  • I was suggesting a redesign as it would not only normalize the data, it would--i think--obviate this problem you are asking to resolve. But that's just wishful thinking. :)

    If the issue has to do with different paths due to different categories, wouldn't adding Category to Question_Answer_Value_Referenced_Question_Xref solve that issue? With category in the record, it would clearly separate paths by category. For the path to stop (final answer) keep Referenced_Question_ID NULL.

    If that does not resolve the issue, i am not fully understanding it. So, please explain it once more.


    Wednesday, June 22, 2011 1:07 PM
    Answerer
  • Hi,

    I am not looking answer for my Final question. Its basically search kind of stuff. The user have to answer all the questions and (on Final question arrives) onclick of submit button  the Final results should be searched and display it on screen.

    How can I map these question with answers and search the final result (table having these values).

     

    Tuesday, June 28, 2011 4:02 PM
  • By "map these question with answers" do you mean, given a category, find the final question in that category? Are you looking for T-SQL query to do that? Or is this a question of design?

    Tuesday, June 28, 2011 6:13 PM
    Answerer
  • Sorry for my type error. I mean, I need to collect all the questions and its answers (provided by users) including the last question.

    The user should click the submit button.

    Then pass those processed array(question,answers)/XML as parameters to the database to do a search on the table(Final Search Result).

    The Table (Final Search Result) contains only the SearchResult column.(Here I need to add column for the Mapping of SearchResult and (Q&A) collection) 

    Is that possible in SQL 2000?

    Wednesday, June 29, 2011 10:28 AM
  • I do not know SQL Server 2000, so i do not think i can answer the question.

    Though, i am still confused as to what the question is.

    1. Are you passing the XML already?
    2. What is the SearchResult COLUMN for? That is, what data does it store?
    3. Are you asking how to add the XML to the TABLE or what should be stored?

     

    Wednesday, June 29, 2011 11:27 AM
    Answerer