• This is exactly what I was talking about in my previous post. You might be able to represent this type of Client-Question-Response using an EAV, but you'll find it's better to have a good relational model with "limited" EAV-ness. The only EAV portion I can see in your model would be the Client "Answers" or "Responses" table.

    Basically there are two pieces:

    1.) The storage model for the Surveys, Questions, and Answers:

    Surveys (table)

    Questions(table) - this table stores the Question, type of Question (multiple-choice, true / false, or written). When the question type = "multiple choice" then the Answers table will be used. You could also provide a "Score" column here that would represent the overall points associated with this question if it had more "weight" than others (in the situation where this is a survey type of "Test / Exam"??).

    Answers (table) - this table stores the answers, their sequence (so they can be re-ordered) and any other info about them. This table is probably only used for multiple-choice questions. You could also provide a "Score" column in this table if you're trying to derive a total "risK assessment" type of survey.

    2.) The storage model for the Surveys, Clients, and their Responses / Answers. In this side of the design, you are sort of using an EAV on the "ClientAnswers" because you're not sure that the "Value" column is storing an answer to a True/False or a Multiple-Choice question.

    Surveys (table) - same as above

    ClientSurveys (table) - stores which surveys the client has taken. Maybe a total score if you're not always summing the score from the ClientAnswers.

    ClientAnswers (table) - stores the answer the client chose for the particular question in the survey. This is a child of ClientSurveys. In this table, the "Answer" (or "Value") column should probably be a Sql_Variant. This way you can either store the Value from one of the multiple-choice answers (an Int usually) or the "T" or "F" as a Bit data type.

    Actually, I'd have two valid "Value" columns (Value Sql_Variant NULL, and ValueText (Text or VarChar(MAX) NULL). This way if one of the question is a "written answer" then the storage or the answer will be more efficient because only for a limited number of records will the Text or Varchar(MAX) column be used.

    I hope I haven't confused you too much...