How to Design a Survey Database: Trade-offs Among Response, Answer, and Version Tables

The easiest way to expose problems in a survey database is at the first question revision: the first version of Q6 was a single-choice question “most frequently used channel,” while the second version allowed multiple selection and added “partners”; the number 3 in an old submission represented “offline store,” but in a new submission, 3 might simply be the third selected option. If the database only stores q6=3, the value remains, but the meaning has been lost.

For long-term survey data, the core relationship should be “which version of the question was seen when the answer was given, what the response domain was, and how many values a submission can generate.” A robust default approach is: versioned questionnaire metadata is managed with relational tables, raw submissions retain an immutable payload, standardized answers enter a long table with types and constraints, and analysis wide tables are generated for specific versions. Wide tables are still useful, but they are better treated as analysis artifacts rather than as the single source of truth across projects.

First decide what the database must preserve, not how many tables it should have

At minimum, the database must support three time scales. At the moment of submission, it needs to accept a completed questionnaire in full, even if network retries must not duplicate counters; during project analysis, it needs stable queries by question, option, batch, and population; six months later, it needs to explain what old values meant at the time and trace report numbers back to real answers. If there are multilingual versions, matrices, loops, quotas, longitudinal tracking, or deletion requests, a single wide table will soon push these differences into column names and null values.

You can first write six acceptance queries, then draw the structure:

1. Given a response ID, find the version of the questionnaire it actually used and the full question wording displayed at that time;
2. Given a multiple-choice question, distinguish between “was displayed but not selected,” “not displayed,” and “the entire submission was interrupted”;
3. For a matrix or loop question, recover the answer for a given row and object without overwriting previous responses;
4. When merging two versions, identify which variables are directly comparable and which have changed in response domain;
5. Trace any column in an analysis wide table back to the raw answer and the standardization rules;
6. When executing access or deletion workflows, locate the relationships among identity information, responses, attachments, and downstream copies.

Only if the structure can answer these questions does it qualify for research use; “being able to store JSON” just means the write succeeded.

Seven entities form a sufficient relational skeleton

study
  └── instrument_version
        ├── question_version
        │     └── option_version
        └── collection_batch
               └── response
                     ├── answer
                     │     └── answer_choice
                     └── raw_submission

study represents the research project and the target population; instrument_version stores a published, immutable questionnaire version; question_version retains question intent, wording, type, order, and display conditions; option_version saves stable option IDs, display labels, and order. A question number is only a position within one version and should not serve as a cross-version identifier.

collection_batch records the channel, time, and sample context; response represents a single submission or answering session and directly references the instrument version and batch; answer stores single values of text, numbers, dates, or structured missingness; multiple selections go into answer_choice, with one row per actual choice. raw_submission stores the original payload received or its controlled location, for replay and auditing, and does not directly support daily analysis.

The DDI Alliance’s DDI-Lifecycle covers the full research data lifecycle from questionnaire and question through logical records, physical records, and storage, and it designs reusable objects to be identifiable and versionable. The database does not need to copy its entire model, but “question intent,” “displayed wording,” “response domain,” and “version” should be understood separately rather than stuffed into a single question_text field that might be overwritten.

The same multiple-choice answer: three storage approaches lead to three consequences

Suppose a respondent selects “website” and “offline store” for “which channels have you used?”

Wide-table column splitting can store channel_web=1 and channel_store=1. Analysis is straightforward, and importing into statistical software is convenient; but new options mean new columns, and unchecked vs. not-displayed can easily collapse into both becoming 0. Cross-questionnaire reuse and loops become cumbersome. For a one-off survey with fixed questions and a single analysis deliverable, it can be a reasonable final form.

Arrays or JSON can store ["web","store"]. They faithfully preserve the set of a single submission, offer flexible writes, and suit the raw layer; but if all answers are stored only as JSON, foreign keys, types, option validity, and cross-project queries become harder to enforce. JSON is not wrong; using it to replace all semantic constraints is the problem.

Choice detail tables create one row for each selected option and reference the specific version of the option that was available for that question at that time. This enables easy validation of legal options, counting of selections, and handling of new options, at the cost of aggregation or pivoting before analysis. Hybrid models usually retain both the raw set and normalized choice rows, and then generate a pivoted view for analysis.

Regardless of the storage method, you must also express “whether the question was displayed.” The absence of a choicerow for an option in a multiple-choice table only indicates that no choice was recorded; it cannot automatically determine whether the respondent saw the question. You can infer it from questionnaire logic and response paths, or save question_exposure events in complex scenarios; the key is not to encode a missing row directly as a negative answer.

Do not create a generic EAV answer table without types

The simplest long table is often designed as response_id + question_id + value_text, converting everything to strings. While it can accommodate any question type, it strips dates, amounts, scales, and missing values of their types, and the database cannot prevent “satisfaction = apple” or the same question being written twice.

Two safer options exist:

• In answer, define value_text, value_number, value_date, value_boolean, and other columns, and use check constraints to ensure that only one value matching the question type is non-null;
• Split into separate detail tables by response domain, such as numeric answers, text answers, and choice answers, sharing the response and question-version identifiers.

The former provides centralized queries and fewer tables, though constraints are slightly more complex; the latter offers clear types, but cross-type aggregation requires more joins. For projects of moderate size, both are more manageable than typeless strings. Open text can preserve the original wording and separately store cleaned or coded results; topic labels must never overwrite raw answers.

Version relationships must anchor to the response, not just keep the “current questionnaire”

A published questionnaire version should be treated as an immutable object. Corrections of typos, adjustments to options, changes in skip logic, or translations should be recorded through new versions or explicit intra-version change policies; a response references the version it actually displayed. When querying historical responses, you should not link to the project’s current question records; otherwise, old answers will be reinterpreted under new wording.

Questions need two distinct identifiers: a stable concept or variable identifier that indicates “intended to measure the same thing” across versions, and a question-version identifier that locks in the wording, response domain, and logic at that time. Whether merging is allowed should be determined by explicit mappings, not automatically because stable identifiers match. Changing the reference period from 7 to 30 days, switching single-choice to multiple-choice, or altering scale endpoints can make values not directly comparable.

DDI’s data capture structures also describe question intent, question text, response domain, instructions, and missing values separately, and make question structures versionable. This approach avoids the false assumption “the question text has not changed, so the variable has not changed”: changes in display conditions or response domains can also alter data meaning.

Matrix, loops, and repeated measurements need a “response position”

Each row of a matrix question may share a scale but measure different objects; loop questions repeat the same question for several brands, family members, or visits; longitudinal studies have the same person answering across waves. A unique constraint of response_id + question_id alone will cause the later value to overwrite the earlier one.

The primary key of an answer should be able to represent the instance position, such as repeat_path, subject_instance_id, or a separate response_item_instance. The path must refer to stable objects, not merely store “row 2 on the screen,” because random ordering changes positions. The analysis layer can then pivot “purchase intention for brand A” into the needed columns.

Longitudinal studies must also distinguish participants from responses. A participant_id can link multiple waves, while a response_id only identifies a single session; if the study promises anonymity, you should not later create identifiable links for the sake of convenience. If identity or contact information has legitimate uses, it should be stored separately from research answers, linked through a controlled mapping, and set with distinct permissions and retention periods.

Put key business invariants under database constraints

Application code changes, and bulk imports or manual fixes can bypass a page-level check. The database should, at minimum, protect these invariants:

• Every table has a stable primary key; external submission IDs are unique within a project or batch, so retries do not create duplicate responses;
• The instrument version referenced by a response, the question version referenced by an answer, and the option version referenced by a response domain must actually exist;
• A single-value answer must be unique for the same response, question version, and repeat position;
• Under the same answer, the choice detail table cannot contain duplicate selections of the same option;
• Completion time cannot be earlier than start time; numeric values are within the allowed range of the question; enumeration statuses can only take controlled values;
• Deleting questionnaire metadata cannot orphan historical responses; usually deletion should be restricted and replaced with an archived status.

PostgreSQL’s current documentation on primary key and foreign key constraints embodies the basic principles here: a primary key uniquely and non-nullly identifies a row, and a foreign key requires that the referenced value exist in the target table, thereby maintaining referential integrity between entities. Database types are only the first layer; unique, not-null, check, and foreign key constraints together turn research rules into executable boundaries.

Hybrid models separate collection facts from analysis convenience

The recommended data flow is not “always use long tables,” but four layers with clear purposes:

1. Raw layer: retain the received payload, version, and timestamp without silent modifications;
2. Canonical layer: parse submissions, questions, options, and repeat instances into typed, constrained relational records;
3. Analysis layer: generate wide tables or themed datasets according to a specific codebook, sample, and processing rules;
4. Delivery layer: charts and reports reference a specific analysis version rather than querying the ever-changing operational database directly.

This allows researchers to still use the familiar one-row-per-submission wide table while preserving the raw values and version semantics needed to regenerate it. A one-off survey that needs to go live quickly can simply store the wide table and a questionnaire snapshot; as soon as a project is repeated, revised, multilingual, merged across systems, or audited, the benefits of a hybrid model usually quickly outweigh the cost of extra tables.

When data is derived from a questionnaire tool or enters internal platforms through permitted interfaces, preserve the project, instrument version, collection batch, stable variables, and original option identifiers before mapping to an enterprise data model. If analysis wide tables from the questionnaire tool support immediate viewing while the internal warehouse handles long-term association, do not guess fields from display wording, and do not transfer only an Excel file without version context.

Finally, test with the original six acceptance queries. In particular, randomly pick an old multiple-choice or loop submission, reconstruct the page and each choice at that time from the database, and then generate an analysis column from the canonical answers. If any step relies only on the original developer’s verbal explanation, what is missing is not more storage space, but a piece of research semantics that has not yet entered the model.