Number of fields in a table

I am working on a project which requires having a form that must be filled by the user. The number of the fields on this form is arround 150. Is it a good idea to have a database table with 150 fields (columns)? If its not what would be a better approach for this case? Handling this many fields on a windows form is another issue but first I would like to know about how to deal with the data storage.

Any help is appreciated.

Thanks

its very hard to say.... you google for Database Normalisation and read about normalisation and how to normalise data.....

Madhu

|||

First of all thanks for your response,

How about if I put the form data into an XML file and keep the rest of data in database? Is it a good idea to hold information about one thing in two seperate format. (database and xml file)

I will not use the values in this form other than displaying them as a report.(Like a guy in a department will fill in the form and send it to another guy in another department for confirmation)

Thanks

|||

Hi FlatWhite,

There is no technical problem with having 150 fields in a table, but Madhu's point about normaization is valid and important. The question is whether those 150 fields should really be in the same table, meaning that all the fields are related to the key of the table, or whether they should be normalized across several tables. Understanding normalization is a core part of database design (probably the core part of database design). If you're building a database and you haven't read up on normaization yet, go do that now, before you do anything else. If you don't, you'll have problems later.

As to storing your data across two separate formats, this seems like a bad idea. Nothing wrong with XML, but when you start storing data in disparate formats without a clear technical reason for doing so, you just introduce complexity into your application. The issue with your application is the requirement of filling in 150 fields (a daunting form at best), not with how you store the data. Putting the data in XML won't make the form any easier to fill out, it will just make it more complex to access and back up your data because it will reside in two places.

You will serve you users much better by working out how to reduce the number of fields or at least provide default values for as many as possible so that it doesn't take an hour to fill in the form.

Mike

|||

Hi Mike,

Thanks for your reply. Honestly its very nice to hear that there is no technical problem with having 150 fields because I wasn't happy with this so couldn't go any further in my database design. Now I can work on.

I have been reading about normalization and I am really having difficulty of making decision about how to split data into smaller groups. Because the form I am designing is consists of say 50 independent questions. Each question (Or point to be answered) gets minimum three input. (some more) (not all questions in the same format)

Example:

Check the color of the product
Qualitative input = selection of good or bad or OK (in the customers hardcopy form this part is consists of 3 checkboxes)
Quantitative input = how big is the surface to be painted
Further Notes = Any text
From this example I made three fields for this question: ProductColorQuantitative, ProductColorQualitative and ProductColorFurtherNotes. (With roughly 50 questions it makes 150 fields)

In terms of normalization first thing that I can think of is putting every question in a seperate table :) which will not help me at all other than making things worse.
Questions are very technical questions so I don't think number of questions can be reduced.

So far I couldn't come up with a better solution. Since I don't need any of these inputs in anywhere in my program I believe it is better to leave it like it is. Do you agree?

Thanks

|||

I can think of having one table for the questions -with just a few fields:

Table: Questionnaire
Columns: Date
ResponderID
SurveyVersion
QuestionNumber
QualInput
QuantInput
Notes

Just tossing out ideas here...

|||

This question really takes me back...

For some reason questionnair design was one of the most common questions we got when I was supporting Access, so much so that we wrote a KB article to describe a table design that works well for this application and which you can read here. Don't worry that the article applies to Access, relational design is the same regardless of the database where you're designing, all the same concepts apply.

Mike

|||

Thanks for the response guys. One last thing that I am trying to figure out is if get three tables such;

Table : Responder
-
ResponderID

Table : Questions
-
QuestionID

Table : Answers
-
AnswerID
QuestionID
ResponderID
Response

I can solve the problem of having somany fields in a single table however since not all the questions get same type of input (some gets int, some gets text, some gets yes or no) I am having trouble in determining the type of the Response field in Answers table.

Am I missing a point in your posts or am I totally in wrong track?

Sorry for bothering you

FlatWhite

|||Since each question could have one of three types of response, create three columns in the table. Use the appropriate column based upon need.