locked
Databas Design RRS feed

  • Question

  • User1232153615 posted

    I need to design a data base for a survey web site.

    The website contains 1000 check boxes to be checked or left unchecked by the user.

    Means I need to make 1000 entry for one survey.

    If I make one check box as column in a table it will not allow, I can divide to i.e. 4 tables.

    If I make key and value as the column then 1000 records for one survey, soon the database will become huge.

    I can divide it to different 4 tables, still the tables will become huge.

    Could you please help me in finding a solution that will help me in designing a database which will be easy to query, tables should not become huge.


    Thanks

    Amit


    Saturday, February 20, 2010 4:31 PM

Answers

  • User-1199946673 posted

    First of all, Do you really want users to (un)check 1000 checkboxes! I would be bored after 100 or less, and will go on with something else. So you might worry to much about the size of your database Wink

    Which database are you using? If it is SQL Server, 1000 colums in a table won't be a problem:

    http://technet.microsoft.com/en-us/library/ms143432.aspx

    If the columns always remain the same, this is the preferred way, althought I wouldn't like it very much because it would hurt my eyes I guess!!! If the columns do change, I would place the columnames in a separate table, creatimng 1000 records and for each and every user create also a record for each checkbox. Alternativly, instead of creating 1000 records for each user, you could only create thise records where the checkbox is checked. So when a user/checkboxname combination is not in the table, the checkbox was not checked.

    Ans another solution is to store the 1000 values as a sort of array:

    1,4,5,6,7....

    Meaning checkboxes 1, 4, 5, 6, 7 are checked

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 20, 2010 7:37 PM

All replies

  • User-1199946673 posted

    First of all, Do you really want users to (un)check 1000 checkboxes! I would be bored after 100 or less, and will go on with something else. So you might worry to much about the size of your database Wink

    Which database are you using? If it is SQL Server, 1000 colums in a table won't be a problem:

    http://technet.microsoft.com/en-us/library/ms143432.aspx

    If the columns always remain the same, this is the preferred way, althought I wouldn't like it very much because it would hurt my eyes I guess!!! If the columns do change, I would place the columnames in a separate table, creatimng 1000 records and for each and every user create also a record for each checkbox. Alternativly, instead of creating 1000 records for each user, you could only create thise records where the checkbox is checked. So when a user/checkboxname combination is not in the table, the checkbox was not checked.

    Ans another solution is to store the 1000 values as a sort of array:

    1,4,5,6,7....

    Meaning checkboxes 1, 4, 5, 6, 7 are checked

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 20, 2010 7:37 PM
  • User-952121411 posted

    I agree with the second option presented in the last post.  Even though SQL Server can handle 1000 columns, I think a field of type varchar with a delimited value set representing maybe just the checked values (i.e. 1|2|5|10...) just the unchecked values (i.e. 6|7|8|9...) or true and false for all 1000 values (i.e. 1|1|0|0|0|1....) will be a workable solution. With this you can use any type delimiter needed as long as it remains consistent.  Then you only have 1 record per customer and a varchar or similar field taking up 2000+ characters (1 for each value + 1 for each delimiter) is no big deal at all.  Many times text notes, etc span much longer than that.

     

    Monday, February 22, 2010 9:18 PM
  • User1232153615 posted

    As it will be used for the medication purpose, the patient has to take the survey and check only the applicable options.

    I like you idea to only save the checked one.

    When the user comes secound time for the updation. I will take only consider the checked one and delete all the record in the database and do a fresh entry,

    else I will compare the values and do an updation and a Insert.

    Whcih one would be beter?

    Perfect !!

    Tuesday, February 23, 2010 8:21 AM
  • User-952121411 posted

    You could just do an 'Update' if the user comes in and makes changes to thier selections.  You could do an Update and overwrite the individual record based on the user name or ID and that saves the need to do a Delete and Insert.  The Update will modify the field to be the newest value saving an extra operation.

     

    Tuesday, February 23, 2010 9:56 AM