Survey Answers: A Table Design for Questions and Responses

Survey answers belong in rows, not columns. Store one row per answer, linked to a question and to a response. Then a new question is new data, not a new column.

Why one column per question hurts

Here is how it usually starts. Marketing wants one more question on the customer survey. A developer adds a column to the response table, then updates the form, the export, and the report. Next quarter it happens again.

A year later the table has eighty columns and most of them are NULL. Nobody remembers which column held which version of which question. Typed rows avoid that, and they are not hard to build.

Five tables, five different things

A survey has questions. A question may have allowed options. A response is one submitted form. An answer connects a response to a question and carries the value. Those are different things, so they get different tables.

DROP TABLE IF EXISTS dbo.AnswerDemo, dbo.ResponseDemo, dbo.OptionDemo, dbo.QuestionDemo, dbo.SurveyDemo;
GO
CREATE TABLE dbo.SurveyDemo (SurveyId int PRIMARY KEY, SurveyName nvarchar(100) NOT NULL);

CREATE TABLE dbo.QuestionDemo
(QuestionId int PRIMARY KEY,
 SurveyId int NOT NULL REFERENCES dbo.SurveyDemo(SurveyId),
 Prompt nvarchar(300) NOT NULL,
 AnswerType varchar(20) NOT NULL,
 UNIQUE (SurveyId, QuestionId));

CREATE TABLE dbo.OptionDemo
(OptionId int PRIMARY KEY,
 QuestionId int NOT NULL REFERENCES dbo.QuestionDemo(QuestionId),
 OptionText nvarchar(100) NOT NULL,
 UNIQUE (QuestionId, OptionId));

CREATE TABLE dbo.ResponseDemo
(ResponseId int PRIMARY KEY,
 SurveyId int NOT NULL REFERENCES dbo.SurveyDemo(SurveyId),
 SubmittedAt datetime2 NULL,
 UNIQUE (ResponseId, SurveyId));

Notice the extra UNIQUE constraints. They look redundant, but they let the answer table use composite foreign keys. You will see why in a moment.

One typed value per answer row

An answer can be text, a number, a date, or a chosen option. I give each its own column. A CHECK constraint counts the non-NULL ones and demands exactly one. That blocks empty answers, and it blocks a number plus an option in the same row.

CREATE TABLE dbo.AnswerDemo
(AnswerId bigint IDENTITY PRIMARY KEY,
 ResponseId int NOT NULL,
 SurveyId int NOT NULL,
 QuestionId int NOT NULL,
 TextValue nvarchar(1000) NULL,
 NumberValue decimal(19,4) NULL,
 DateValue date NULL,
 OptionId int NULL,
 FOREIGN KEY (ResponseId, SurveyId) REFERENCES dbo.ResponseDemo(ResponseId, SurveyId),
 FOREIGN KEY (SurveyId, QuestionId) REFERENCES dbo.QuestionDemo(SurveyId, QuestionId),
 FOREIGN KEY (QuestionId, OptionId) REFERENCES dbo.OptionDemo(QuestionId, OptionId),
 CONSTRAINT CK_AnswerDemo_OneValue CHECK
 ((CASE WHEN TextValue   IS NULL THEN 0 ELSE 1 END
 + CASE WHEN NumberValue IS NULL THEN 0 ELSE 1 END
 + CASE WHEN DateValue   IS NULL THEN 0 ELSE 1 END
 + CASE WHEN OptionId    IS NULL THEN 0 ELSE 1 END) = 1));

The composite foreign keys do the quiet work. The first one says the response and the answer belong to the same survey. The second says the question belongs to that survey. The third says the chosen option belongs to that question.

Multiple choice means several rows

For a “pick all that apply” question, store one selected option per row. Please do not store a comma-separated list of option ids. Rows give you foreign keys, counts, and joins without parsing strings later.

Two filtered unique indexes stop duplicates. One allows each option once per response and question. The other allows only one scalar answer per response and question. Then I load one response: a score of 4.5 and two chosen areas.

CREATE UNIQUE INDEX UX_AnswerDemo_Option
ON dbo.AnswerDemo (ResponseId, QuestionId, OptionId) WHERE OptionId IS NOT NULL;

CREATE UNIQUE INDEX UX_AnswerDemo_Scalar
ON dbo.AnswerDemo (ResponseId, QuestionId) WHERE OptionId IS NULL;

INSERT dbo.SurveyDemo VALUES (1, N'Sample survey');
INSERT dbo.QuestionDemo VALUES (10, 1, N'Score', 'number'), (20, 1, N'Chosen areas', 'multi');
INSERT dbo.OptionDemo VALUES (100, 20, N'Area A'), (200, 20, N'Area B');
INSERT dbo.ResponseDemo VALUES (1, 1, SYSUTCDATETIME());

INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, NumberValue) VALUES (1, 1, 10, 4.5);
INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, OptionId) VALUES (1, 1, 20, 100), (1, 1, 20, 200);

Pivot only the report

A wide, one-column-per-question layout is a report, not a storage design. For a small fixed report, conditional aggregation is enough. Here I first collapse the chosen options into one text value per response with STRING_AGG. Then I join that to the score.

The order matters. If you join scores and options row by row, the score repeats once per option. Aggregate first, join second.

WITH Choices AS
(SELECT a.ResponseId,
        STRING_AGG(CONVERT(nvarchar(max), o.OptionText), N', ') WITHIN GROUP (ORDER BY o.OptionId) AS ChosenAreas
 FROM dbo.AnswerDemo AS a
 JOIN dbo.OptionDemo AS o ON o.OptionId = a.OptionId
 WHERE a.QuestionId = 20
 GROUP BY a.ResponseId),
Scores AS
(SELECT ResponseId, MAX(CASE WHEN QuestionId = 10 THEN NumberValue END) AS Score
 FROM dbo.AnswerDemo
 GROUP BY ResponseId)
SELECT r.ResponseId, s.Score, c.ChosenAreas
FROM dbo.ResponseDemo AS r
LEFT JOIN Scores AS s ON s.ResponseId = r.ResponseId
LEFT JOIN Choices AS c ON c.ResponseId = r.ResponseId
ORDER BY r.ResponseId;

Make the database say no

A design is only as good as its refusals, so let me try to break it. I add a second survey and a second response, then attempt four bad writes. Each one logs its error number. Zero would mean the write was accepted.

INSERT dbo.SurveyDemo VALUES (2, N'Other survey');
INSERT dbo.ResponseDemo VALUES (2, 1, SYSUTCDATETIME());
CREATE TABLE #Attempts (Step int IDENTITY, Attempt nvarchar(60), ErrorNumber int);

BEGIN TRY
  INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, NumberValue, TextValue) VALUES (2, 1, 10, 3, N'three');
  INSERT #Attempts VALUES (N'Two values in one row', 0);
END TRY
BEGIN CATCH INSERT #Attempts VALUES (N'Two values in one row', ERROR_NUMBER()); END CATCH;

BEGIN TRY
  INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, OptionId) VALUES (2, 1, 10, 100);
  INSERT #Attempts VALUES (N'Option from another question', 0);
END TRY
BEGIN CATCH INSERT #Attempts VALUES (N'Option from another question', ERROR_NUMBER()); END CATCH;

BEGIN TRY
  INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, NumberValue) VALUES (2, 2, 10, 5);
  INSERT #Attempts VALUES (N'Response from another survey', 0);
END TRY
BEGIN CATCH INSERT #Attempts VALUES (N'Response from another survey', ERROR_NUMBER()); END CATCH;

BEGIN TRY
  INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, OptionId) VALUES (1, 1, 20, 100);
  INSERT #Attempts VALUES (N'Same option picked twice', 0);
END TRY
BEGIN CATCH INSERT #Attempts VALUES (N'Same option picked twice', ERROR_NUMBER()); END CATCH;

SELECT Step, Attempt, ErrorNumber FROM #Attempts ORDER BY Step;

The database refuses all four. The first three fail with error 547, a constraint conflict. The duplicate pick fails with error 2601, a duplicate key in a unique index. The database did the refusing, so a bug in the application cannot sneak these rows in.

What the schema cannot check

Now the fifth attempt, which is the honest one. Question 10 expects a number. Watch what happens when I send it text.

INSERT dbo.AnswerDemo (ResponseId, SurveyId, QuestionId, TextValue) VALUES (2, 1, 10, N'excellent');

SELECT a.AnswerId, q.AnswerType, a.TextValue, a.NumberValue
FROM dbo.AnswerDemo AS a
JOIN dbo.QuestionDemo AS q ON q.QuestionId = a.QuestionId
WHERE a.ResponseId = 2;

It is accepted. The CHECK only counts values. It does not know that this question wants a number. So validate the whole response when the user submits, in one transaction, and test it. Check required questions, allowed types, and choice limits there.

Keep “skipped” separate from “prefer not to answer”. A NULL is not a universal way to say both. And do not push every attribute of your application into one answer table. Questionnaires fit this design. Customers and invoices still deserve real columns.

DROP TABLE IF EXISTS #Attempts;
DROP TABLE IF EXISTS dbo.AnswerDemo, dbo.ResponseDemo, dbo.OptionDemo, dbo.QuestionDemo, dbo.SurveyDemo;

Start with the rows, and let the report be as wide as it wants.

A survey schema is not universal storage, it is a questionnaire model.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.