Database design for a survey

sql

database-design

survey-design

sql-queries

byNikita Barsukov·Dec 6, 2024

Start with a survey database having four key tables: Surveys, Questions, Survey_Questions, and Responses. The Survey_Questions is a junction table, connecting surveys to questions, catering to many-to-many relationships. Register answers in Responses using foreign keys for maintaining relationships between user responses with respective questions.

CREATE TABLE Surveys (SurveyID INT, Title VARCHAR(255), PRIMARY KEY (SurveyID)); --'Cause naming things is hard!
CREATE TABLE Questions (QuestionID INT, Text VARCHAR(255), PRIMARY KEY (QuestionID)); -- I've got a question for you. 
CREATE TABLE Survey_Questions (SurveyID INT, QuestionID INT, FOREIGN KEY (SurveyID) REFERENCES Surveys(SurveyID), FOREIGN KEY (QuestionID) REFERENCES Questions(QuestionID), PRIMARY KEY (SurveyID, QuestionID)); -- Playing matchmaker for questions and surveys.
CREATE TABLE Responses (ResponseID INT, Survey_QuestionID INT, Answer TEXT, PRIMARY KEY (ResponseID), FOREIGN KEY (Survey_QuestionID) REFERENCES Survey_Questions(SurveyID, QuestionID)); -- Because your opinion matters.

Aim for a flexible design that's query-friendly, optimizing for regular survey tasks and data crunching.

Designing for versatile question types

Expanding on the basic answer, factor in a wide palette of question types. Surveys may include multiple-choice, text responses, ratings, and even media uploads. It's paramount to build a system that can handle different forms of answers.

ALTER TABLE Questions ADD COLUMN Type VARCHAR(50); -- Because variety is the spice of life.

An Answer_Options table can manage additional option sets for predefined options:

CREATE TABLE Answer_Options (OptionID INT, QuestionID INT, OptionText VARCHAR(255), FOREIGN KEY (QuestionID) REFERENCES Questions(QuestionID), PRIMARY KEY (OptionID)); -- Now with more choices!

Navigating interactive survey experience

To personalize a survey, tables can be put in to define and handle conditional logic paths:

CREATE TABLE Question_Logic (LogicID INT, QuestionID INT, Target_QuestionID INT, TriggerAnswer TEXT, FOREIGN KEY (QuestionID) REFERENCES Questions(QuestionID), FOREIGN KEY (Target_QuestionID) REFERENCES Questions(QuestionID), PRIMARY KEY (LogicID)); -- Because we're all about drama.

This enables the survey to adapt questions based on prior responses by storing trigger responses and follow-up questions.

Scales for large data sets

A hybrid storage approach can be necessary for scaling scenarios and complexity. Use JSON strings for less structured data, leveraging JSON capabilities of modern databases like SQL Server or PostgreSQL.

ALTER TABLE Responses ADD COLUMN StructuredData JSON; -- Now we're cooking with gas!

When dealing with heavy data usage, consider data archiving methods to avoid a performance drop and maintaining indexes on commonly queried fields.

Optimizing for data analysis

In a survey database, generating summary data can be resource-heavy. Schedule jobs to compute summary data while off-peak to enhance system performance.

Additionally, give thought to a dedicated analytics table:

CREATE TABLE Survey_Summary (SurveyID INT, QuestionID INT, SummaryDetails JSONB, FOREIGN KEY (SurveyID) REFERENCES Surveys(SurveyID), FOREIGN KEY (QuestionID) REFERENCES Questions(QuestionID)); -- One table to rule them all.