none
Normalising the tables which has millions of record RRS feed

  • Question

  • Hi there,

    I have a scenario to clarify regarding the normalisation. 

    There is a question and answer application. Question and answers are stored in separate tables. There will be a survey and lots and lots of people are expected to fill this survey. So I created a third table (QA table) and linked both question and answer using their IDs. There are only few questions (10-15), but there might be variety of answers and we are expecting lot of data to be generated (in millions maybe). The biggest possible answer type would be asset related. So starting from 50K to 500K or even 1-2 million. So to design a proper database I am trying to use this answer table to have all the possible values (50000, 50001, 50002..... 2million) saved in each row and assign ids for each value. So potentially this table will have 1-2 million records. The 'QA table' can then use the Ids from the answer table along with the question id to represent the user responses. We will have to index both question and answer tables. Write a function which will return the answer id if we send the answer text to it as input.

    Please validate this scenario and suggest any better ideas. Thanks for your help in advance.


    Regards, Laks

    Thursday, February 22, 2018 9:15 AM

Answers

  • I'm sure you're simplyfing things for some reasons.

    There are e.g. single-choice, multiple-choice, free-text, integer-valued, floating-value question/answer types (and some more).. How do you like to handle these?

    What kind of application environment?

    This is a case where I would seriously think about a document/object database first.

    And 2 million rows is not that much..

    • Marked as answer by Luckman81 Wednesday, March 14, 2018 12:08 PM
    Thursday, February 22, 2018 9:33 AM

All replies

  • I'm sure you're simplyfing things for some reasons.

    There are e.g. single-choice, multiple-choice, free-text, integer-valued, floating-value question/answer types (and some more).. How do you like to handle these?

    What kind of application environment?

    This is a case where I would seriously think about a document/object database first.

    And 2 million rows is not that much..

    • Marked as answer by Luckman81 Wednesday, March 14, 2018 12:08 PM
    Thursday, February 22, 2018 9:33 AM
  • Hi Stefan,

    Thanks!

    I am planning to handle the asset field as free to type integer fields and probably with some validation. Rest of the fields are going to be multiple choice.

    BTW the QA table will also have millions of record and please advise about having data stored in QA table or data stored in different table. If possible with pros and cons please.

    Thank you 


    Regards, Laks

    Thursday, February 22, 2018 9:46 AM
  • Also, I have not worked with Object database. Can you please tell me about this please ?

    We are using cloud infrastructure (windows azure).

    Thanks


    Regards, Laks

    Thursday, February 22, 2018 10:18 AM
  • Just to add to this, since we have the SQL server already setup, it would be easy to understand the best approach within SQL server.

    Please help. Thanks!


    Regards, Laks

    Thursday, February 22, 2018 10:25 AM
  • We are also planning to utilise Entity framework for this purpose. I suppose ORM tools like EF should be better for such scenarios. Correct ?

    Please advise.


    Regards, Laks

    Thursday, February 22, 2018 12:07 PM