[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