CODEWITHSUNDEEP
sqldatabase-designrelational-databaseentity-relationship
JoJo
JoJo

Reputation: 4923

How to build a Sql Server table to store Quiz answers paired with the questionId?

I have an .aspx form that has about 50 multiple choice questions in total on the survey.

Should I build a delimited string of the question id and the answer given and just store a string? The problem with that is that the string might be long so a datatype text would be required?

This would allow all of the answers to be in 1 record.

Alternatively I was considering something like this where each answer is it's own record and each submitted survey would need to be joinied by a uniqueidentifier.

What is the correct approach for this, or even something I have not thought of?

I have an .aspx form that has about 50 multiple choice questions in total on the survey.

Should I build a delimited string of the question id and the answer given and just store a string? The problem with that is that the string might be long so a datatype text would be required?

This would allow all of the answers to be in 1 record.

Alternatively I was considering something like this where each answer is it's own record and each submitted survey would need to be joinied by a uniqueidentifier.

What is the correct approach for this, or even something I have not thought of?

CREATE TABLE [dbo].[surveyAnswers](
[id] [int] IDENTITY(1,1) NOT NULL,
[questionId] [int] NOT NULL,
[quizId] [uniqueidentifier] NOT NULL,
[answerValue] [varchar](50) NULL,
[quizDate] [datetime] NOT NULL) ON [PRIMARY]

Upvotes: 1

Views: 1843

Answers (1)

user2246674
user2246674

Reputation: 7719

Do not store denormalized data - this will make life a pain later on1. Okay, now that that's out of the way we can skip past the first approach .. :)

The approach at the bottom looks "like a start", but there are several issues. Firstly, quizDate has no business being there (it is related to a quiz, not an answer) and, secondly, the schema doesn't capture who actually took the quiz.

My model (shown as a simplified form as I capture criteria groupings, multiple deployments, dimensions, aspects and multi-values which are overkill for a simple case) looks similar to the following at heart:

-- Survey/Quiz "has many Questions"
Survey (SurveyStartedAt, SurveyExpiresAt)
-- Question "belongs to a Survey"
Question (FK Survey, QuestionPrompt, QuestionRules..)
-- Each Participant can "respond to a Survey"
Response (FK Participant, FK Survey, ResponseTime)
-- And each Answer, of which there are many per Response,
-- "contains the answer for a single Question"
Answer (FK Response, FK Question, Value)

Using the above approach allows me to run queries like:

  1. Who took (or didn't take) the quiz? When? How many times?
  2. What was the average rating for "Likert" Questions?
  3. Which optional questions were not answered?
  4. (And many more including advanced roll-ups; SQL Server is quite powerful.)

Note that I eschew a bit of safety and normalization (as Answer->Question, but Response->Survey->Question) so the schema does not prevent an invalid Answer(Response, Question) pair. While this could be dealt with by feeding the Survey through into the Answer relation (and, perhaps better, adding a SurveyQuestions relation), I deal with that by imposing an immutable design and a gatekeeper: once a Quiz starts the Survey (and all related Questions) can never be altered.

1 While it might be tempting to use one record-per-quiz result based on "performance" to "avoid creating excessive rows" - don't; there is no performance issue here! The Answer records are very small (can be compacted nicely on pages) and, when properly indexed, are very fast to retrieve and run queries against. At the point where such decomposition leads to "too many" records (in excess of many millions), other approaches can be considered - but do not start with a denormalized relation schema!

Upvotes: 5

Related Questions