[thelist] Survey database design for storing responses to multiple-choice q uestions
Ben Gustafson
Ben_Gustafson at lionbridge.com
Mon May 20 14:30:01 CDT 2002
--
[ Picked text/plain from multipart/alternative ]
I'm designing a database to store responses to an on-line customer survey.
One of the design dilemmas I'm running into is how to store responses to
"check all that apply"-type multiple-choice questions, where a respondent
can choose between all and none of the response choices. I was thinking of
something like:
---
tblQuestion
question_id (pk)
question_text
tblRespondent
respondent_id (pk)
name
...
tblResponses
question_id (pk, fk)
respondent_id (pk, fk)
response_text (pk)
---
But SQL Server barks when setting the primary key on tblResponses that the
total size of a primary key cannot exceed 900 bytes (the column datatype of
response_text is varchar and the length is 2000, to accommodate for
responses to "free-form" questions using textareas). I'm loath to rely on
limiting the length of the response_text field to set the primary key to
under 900 bytes, since we all know that some important customer will want to
write a book and will complain when the ASP code throws an error when
inserting the response. How would y'all database gurus approach this issue?
--Ben
More information about the thelist
mailing list