www.webdeveloper.com
Results 1 to 3 of 3

Thread: 100-column table in Database!

  1. #1
    Join Date
    Jun 2008
    Posts
    17

    100-column table in Database!

    Is it normal to have like 100 columns in a table for a database in MSSQL?
    Im a beginner in database design, so i wanna ask before im laughed at after showing a 100-column table..

    Im doing a survey project. That table would be the one that my responses are collected. Eg, i have Question 5, a rating type question that goes from Very Important to Not at all Important, then Q5 is divided into 5 areas. And i have 15-20 questions like that, and that would be 15 * 5 questions.

    My initial plan was to out a question as a column, so column names would be something like Q5a, Q5b, Q5c, each column contain one response.

    Is this properly normalized?

    Does anyone have a better idea of how should i store my responses?

  2. #2
    Join Date
    Jul 2003
    Location
    The City of Roses
    Posts
    2,503
    I haven't dug into DB internals enough to know how lots of columns affects performance, but I do know that most DBs have a limit on how many columns any table may have. So if you're planning on creating a column for each question, then one day you may find it impossible to use that kind of schema.

    You could consider setting it up like this:
    Code:
    respondents
    ===================================
    | respondent_id | respondent_name |
    |---------------------------------|
    |               |                 |
    |               |                 |
    ===================================
    
    
    surveys
    ===========================
    | survey_id | survey_name |
    |-------------------------|
    |           |             |
    |           |             |
    ===========================
    
    
    survey_questions
    ====================================================
    | survey_question_id | survey_id | survey_question |
    |--------------------------------------------------|
    |                    |           |                 |
    |                    |           |                 |
    ====================================================
    
    
    survey_answers
    =========================================================================
    | survey_answer_id | survey_question_id | respondent_id | survey_answer |
    |-----------------------------------------------------------------------|
    |                  |                    |               |               |
    |                  |                    |               |               |
    =========================================================================
    Now the schema shouldn't need to ever change even if you add extra questions or even whole surveys.
    for(split(//,'))*))91:+9.*4:1A1+9,1))2*:..)))2*:31.-1)4131)1))2*:3)"'))
    {for(ord){$i+=$_&7;grep(vec($s,$i++,1)=1,1..($_>>3)-4);}}print"$s\n";

  3. #3
    Join Date
    Mar 2005
    Location
    Sydney, Australia
    Posts
    7,974
    Few properly normalised databases require tables with more than 20 - 30 columns maximum. If you have tables that have more columns than that then there is a really good chance that you haven't normalised the data properly in designing your tables. Of course some times there are good reasons for undoing some of the normalisations in implementing the physical database but that rarely adds more than an extra column or two.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles