100-column table in Database!

[joyceqian]

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?

[Jeff Mott]

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:

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.

[felgall]

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.