[thelist] Survey database design for storing responses to multiple-choice q uestions

[Michael Kimsal]

Ben Gustafson wrote:

>
> 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).

Why not just have an index on question_id and respondent_id?  I'm not
sure I see the need for a primary key, unless you're meaning that
each respondent can answer the same question_id more than once.  Even
so, just index the fields together (and maybe separately, depending
on what you need to do).


Michael Kimsal
http://www.logicreate.com
1-866-745-3660