Get past "Row size too large" error.

[speck]

I am working on a project for a client that has nearly 500 fields in a table. Each field entry will be just a few words. I have used a mix of "varchar", "tinytext", "text", and others, but after a couple hundred fields, I keep getting the "row size too large" error.

What is the best field type to use for these and how do I get it where I can have more fields?

Thank you so much in advance for you help!

[Coyote6]

That sounds like too many fields in my personal opinion... depending on the size of your input I would suggest using VARCHAR and possible TEXT for really long strings but just out of curiosity what are some of the fields that you have in your table?

[speck]

A lot of the fields are yes and no questions. Some are one or two words. And some are a couple of sentences.

[abduraooft]

What is the best field type to use for these and how do I get it where I can have more fields? Make your table properly normalised. You may get some hints, if you give the purpose of that table and some sample records.

[speck]

The table I am speaking of is for the Assessment part of the application. The user goes through and answers 500+ questions. Some yes or no questions, some text fields, some paragraph fields. There are about 7 categories within the assessment. Should I seperate each category into a separate table?

Thanks!

[Coyote6]

Yes... I would say create a questions table, an answers table and a category table.

Tables would look something like this:

Categories
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
name VARCHAR(100)

Questions
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
question VARCHAR(500) or TEXT
category_id INT UNSIGNED NOT NULL DEFAULT '0'
UNIQUE INDEX (question, category_id)

/* If the user can only take the survey once.*/
Answers
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
user_id INT UNSIGNED NOT NULL DEFAULT '0'
question_id INT UNSIGNED NOT NULL DEFAULT '0'
answer VARCHAR(100) or what ever your max value is.
UNIQUE KEY (user_id, question_id)

/* If the user can take the survey more than once then you need to create a fourth table. */
Surveys_Taken
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
user_id INT UNSIGNED NOT NULL DEFAULT '0'

Answers
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
surveys_taken_id INT UNSIGNED NOT NULL DEFAULT '0'
question_id INT UNSIGNED NOT NULL DEFAULT '0'
answer VARCHAR(100) or what ever your max value is.
UNIQUE KEY (surveys_taken_id, question_id)



The reason you cannot just take out the user in the unique key in the first answer table, is because you could end up with multiple responses to the same question when the survey is taken a second time and you would not know which one belong to which survey (unless you went by the keys but that's not a good db design). So you would want to single out when each survey individually.