locked
Designing a Q&A application with normalization RRS feed

  • Question

  • Hi there,

    We have a requirement to have few question (around 30 -40) going out gathering certain user information. We have variety of answers for all those questions. So there are few design decisions need to be taken and following are the options we have. can you please suggest and comment on the following options:

    Option 1: The Question is stored in a table and answers are stored in another table (all possible values for the questions. So we have answers related to assets and the maximum would be 2 or 3 million). Because of the requirement the answer table will have millions of record. Every user response is actually saved in a different table which will have question ID and the answer ID from the answers table. The number of user responses is expected to be in thousands daily... so the user response table will also have millions of records in few weeks.... It will grow on 

    Option 2: The Question is stored in a table. The Answers table will have answer values, but not all type of answers. So there are answer IDs used for these answers. very user response is actually saved in a different table which will have question ID and the answer ID from the answers table for those answers stored in the answers table. But for answers specific to Assets, the answer is stored directly in the user response table. So the User response table will have a combination of IDs from Answer table and direct answers entered by users.

    As per normalisation, It would be more appropriate to go with Option 1, but there is the Option 2 which will help us not having to save the asset values in the table and use IDs of those values.

    Please help us to understand which option is efficient. We use sql server for this web application built in .NET.


    Regards, Laks

    Tuesday, March 13, 2018 5:54 PM

Answers

  • Option 2 is good if asset answers are plenty in number and cant be generalized i.e unique answer count is very high.

    If distinct list is less, you can even think of making them into sparse columns.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Luckman81 Wednesday, March 14, 2018 12:10 PM
    Tuesday, March 13, 2018 6:28 PM

All replies

  • Option 2 is good if asset answers are plenty in number and cant be generalized i.e unique answer count is very high.

    If distinct list is less, you can even think of making them into sparse columns.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Luckman81 Wednesday, March 14, 2018 12:10 PM
    Tuesday, March 13, 2018 6:28 PM
  • Thanks, Visakh

    Regards, Laks

    Wednesday, March 14, 2018 12:11 PM