Is it normal to have like 100 columns in a table for a database in MSSQL?

[cutebaby20bk]

Im a beginner in database design, so i wanna ask before im laughed at after showing a 100-column table..<br />
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.<br />
<br />
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.<br />
<br />
Is this properly normalized?<br />

 

[Mehdi]

That's a LOT of columns.

What you need is a process called "normalization" - take a long hard look at your columns; is there any data that's repeated over and over again?

Give it its own table, assign an ID to each different entry, and use the ID in the original table.

Say you have (for example), an book store. Each book is listed in a table;

Title
ISBN
Published Year
Publisher
Author
Co-Author

etc.

You can use one single table, but it'd be a clunky, hard to maintain list.

Better is to make a table for authors and publishers, and assign each an ID.

Then you can also make combination tables (like Author/Publisher); if an author switches publishers, that creates a new ID... the result would be

Title
ISBN
Published Year
Author/Publisher ID

Your total number of columns spread of various tables may not be less than 100 (it may, in fact, be even more), but with data logically split over related tables, it will make searching for items so much easier (and a lot faster).

EDIT

I actually *have* experience creating a survey (our HR department requested it once). This is how I went about it

tblSurveys
sID (primary key, auto-number = unique identifier)
sName (name of survey)

tblQuestions
qID (primary key, auto-number = unique identifier)
sID (reference to survey)
qQuestion (text for question)
=> The survey ID allows you to keep all questions that go together in one place

tblAnswers
aID (primary key, auto-number = unique identifier)
qID (reference to question)
aAnswer (text for answer)
=> This allows for a "many-to-one" relation many answers to one question

tblResponses
rID (primary key, auto-number = unique identifier)
aID (chosen answer)
aAnswerer (reference to who answered, to keep all responses together)

The aAnswerer can be an actual reference to a person (name, ID, whatever), or a unique number (generated at random) to make certain that all answers from one person can be kept together.

This way, you'll have a couple of very small tables, that can grow and be adapted for each survey, and you won't be bound by a constricting number of columns (in case you want to - later on - make a longer or shorter new survey.

Depending on what interface you use, a couple of joined queries can pull all answers for each question, sorted by survey.

 

[douga]

Normal "Yes", Adviseable "Don't see why Not"

I have worked in SQL and Database design for 20 odd years.

Some of the tables I work on at the moment have 100's of columns. And each one vital.

However. MSSQL is quite adept at handling columns, and in a real world situation such as Healthcare it's needed.

What you need to do is think about if it would be better served bu maybe breaking it down into sub groups of Relational tables to the main key.

If you have just an extra tier you could speed up the querying of it once the inputting of the Q&A has finised.

This will show that you understand about Relational Database design.

and help in grouping sets of questions togehter. Also think oubout the overheads on indexing there a maximum of about 15%-20% indexed before the indexing of fields have a detrimental effect on performance so think carefully on that point.

Apart from that I see no problems

 

[TheMadProfessor]

It sounds like you defined your table to be mainly a summary derived from the responses. In practical terms, such a design tends to be unwieldy. It also has the disadvantage of not being able to access the original data. You might consider something like this

table Questions
questionID
questionText

table Answers
questionID
answerID
answerText

table Responser
responderID
(any other data you want to store about a particular respondant)

table Responses
responderID
questionID
answerID

Then, for example, to summarize your responses

SELECT questionID AS "Q#", questionText AS "", answerID AS "A#", answerText AS "", count(*) AS "# of responses"
FROM Questions Q, Answers A, Responses R
WHERE Q.questionID = A.questionID
AND Q.questionID = R.questionID
AND A.answerID = R.answerID
GROUP BY 1, 3
ORDER BY 1, 3