Designing the Database for a Google Forms-Style App

What We Are Building

In this tutorial we design the full database for a form builder like Google Forms. We cover users, form topics, forms, questions, question types, answer options, and how to store the actual answers. The engine is MySQL / MariaDB. We finish with two labelled, animated diagrams.

The Nine Tables at a Glance

Users and Form Topics

Start with the simplest tables. A user is the person who creates forms. A form_topic is a category that groups forms. Both stand alone with no foreign keys, so we build them first.

-- ============ USERS ============
CREATE TABLE users (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name           VARCHAR(120)      NOT NULL,
    email          VARCHAR(190)      NOT NULL,
    password_hash  VARCHAR(255)      NOT NULL,
    created_at     TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ FORM TOPICS (categories) ============
CREATE TABLE form_topics (
    id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    name         VARCHAR(80)  NOT NULL,
    slug         VARCHAR(80)  NOT NULL,
    description  VARCHAR(255) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_topics_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The Form Table

A form is the blueprint. It belongs to one user (the owner) and sits in one topic. It has a status so you can keep drafts private and publish when ready. A share_slug gives each form a public link like /f/abc123.

CREATE TABLE forms (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner_id    BIGINT UNSIGNED NOT NULL,          -- FK -> users
    topic_id    INT UNSIGNED    NULL,              -- FK -> form_topics
    title       VARCHAR(200)     NOT NULL,
    description TEXT            NULL,
    status      ENUM('draft','published','closed') NOT NULL DEFAULT 'draft',
    share_slug  VARCHAR(40)      NULL,
    settings    JSON            NULL,              -- theme, one-response-per-user, etc.
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_forms_slug (share_slug),
    KEY ix_forms_owner (owner_id),
    KEY ix_forms_topic (topic_id),
    CONSTRAINT fk_forms_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_forms_topic FOREIGN KEY (topic_id) REFERENCES form_topics(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Question Types and Questions

Instead of hard-coding types in the app, we store them in a small lookup table. Adding a new type later (say "signature") becomes one row, not a code change. The has_options flag tells us whether a type needs rows in question_options.

-- ============ QUESTION TYPES (lookup) ============
CREATE TABLE question_types (
    id           TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    type_key     VARCHAR(30)      NOT NULL,   -- 'short_text', 'single_choice'...
    label        VARCHAR(60)      NOT NULL,   -- shown in the builder UI
    has_options  TINYINT(1)      NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_qtype_key (type_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ QUESTIONS ============
CREATE TABLE questions (
    id                BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    form_id           BIGINT UNSIGNED  NOT NULL,     -- FK -> forms
    question_type_id  TINYINT UNSIGNED NOT NULL,     -- FK -> question_types
    title             VARCHAR(500)     NOT NULL,
    help_text         VARCHAR(500)     NULL,
    position          INT UNSIGNED     NOT NULL DEFAULT 0,  -- order on the page
    is_required       TINYINT(1)      NOT NULL DEFAULT 0,
    settings          JSON             NULL,     -- min, max, scale labels...
    PRIMARY KEY (id),
    KEY ix_questions_form (form_id, position),
    CONSTRAINT fk_q_form FOREIGN KEY (form_id) REFERENCES forms(id) ON DELETE CASCADE,
    CONSTRAINT fk_q_type FOREIGN KEY (question_type_id) REFERENCES question_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Question Options (the Choices)

A single-choice or multi-choice question needs a list of options. We store each option as its own row. This keeps them ordered, editable, and re-usable by the answer tables. A free-text question simply has zero option rows.

CREATE TABLE question_options (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    question_id  BIGINT UNSIGNED NOT NULL,       -- FK -> questions
    label        VARCHAR(255)    NOT NULL,       -- what the user sees
    value        VARCHAR(255)    NULL,           -- optional stored code
    position     INT UNSIGNED    NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY ix_opt_question (question_id, position),
    CONSTRAINT fk_opt_question FOREIGN KEY (question_id)
        REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The Core Problem — How to Store Answers

There are three common patterns. Here is the honest trade-off:

Where Each Answer Type Lands

The Data Tables — Responses, Answers, Answer Options

A response is one submission of the whole form. Each answer belongs to that response and points to the question it answers. Choice picks go into answer_options, which links an answer to the option rows it selected.

-- ============ RESPONSES (one submission) ============
CREATE TABLE responses (
    id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    form_id            BIGINT UNSIGNED NOT NULL,     -- FK -> forms
    respondent_user_id BIGINT UNSIGNED NULL,         -- FK -> users (if logged in)
    respondent_email   VARCHAR(190)    NULL,         -- if collected anonymously
    submitted_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address         VARBINARY(16)   NULL,         -- INET6_ATON()
    PRIMARY KEY (id),
    KEY ix_resp_form (form_id, submitted_at),
    CONSTRAINT fk_resp_form FOREIGN KEY (form_id) REFERENCES forms(id) ON DELETE CASCADE,
    CONSTRAINT fk_resp_user FOREIGN KEY (respondent_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ANSWERS (one per question per response) ============
CREATE TABLE answers (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    response_id    BIGINT UNSIGNED NOT NULL,      -- FK -> responses
    question_id    BIGINT UNSIGNED NOT NULL,      -- FK -> questions
    answer_text    TEXT            NULL,          -- text, email, file URL
    answer_number  DECIMAL(14,4)   NULL,          -- number, rating, scale
    answer_date    DATETIME        NULL,          -- date, time
    PRIMARY KEY (id),
    UNIQUE KEY uq_answer (response_id, question_id),  -- one answer per question
    KEY ix_answer_question (question_id),
    CONSTRAINT fk_ans_response FOREIGN KEY (response_id) REFERENCES responses(id) ON DELETE CASCADE,
    CONSTRAINT fk_ans_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ANSWER OPTIONS (picked choices) ============
CREATE TABLE answer_options (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    answer_id           BIGINT UNSIGNED NOT NULL,   -- FK -> answers
    question_option_id  BIGINT UNSIGNED NOT NULL,   -- FK -> question_options
    PRIMARY KEY (id),
    UNIQUE KEY uq_answer_option (answer_id, question_option_id),
    KEY ix_ao_option (question_option_id),
    CONSTRAINT fk_ao_answer FOREIGN KEY (answer_id) REFERENCES answers(id) ON DELETE CASCADE,
    CONSTRAINT fk_ao_option FOREIGN KEY (question_option_id) REFERENCES question_options(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Animated ER Diagram — The Blueprint

The six blueprint tables and their links. Each line reads one-to-many (1 → N) from parent to child. Lines flow in the direction data depends.

Animated Diagram — How One Answer Is Stored

A submission creates one response, then one answer per question. Choice picks add rows in answer_options. Faded boxes are blueprint tables the data points back to.

Putting It to Work — Example Queries

Save a submission (inside one transaction)

START TRANSACTION;

-- 1) the response
INSERT INTO responses (form_id, respondent_email)
VALUES (10, 'asha@example.com');
SET @response_id = LAST_INSERT_ID();

-- 2) a text answer (question 101)
INSERT INTO answers (response_id, question_id, answer_text)
VALUES (@response_id, 101, 'The service was quick and friendly.');

-- 3) a rating answer (question 102)
INSERT INTO answers (response_id, question_id, answer_number)
VALUES (@response_id, 102, 5);

-- 4) a multi-choice answer (question 103) -> picks 2 options
INSERT INTO answers (response_id, question_id) VALUES (@response_id, 103);
SET @answer_id = LAST_INSERT_ID();
INSERT INTO answer_options (answer_id, question_option_id)
VALUES (@answer_id, 5001), (@answer_id, 5004);

COMMIT;

Read back one full submission

SELECT q.title                       AS question,
       COALESCE(
           a.answer_text,
           CAST(a.answer_number AS CHAR),
           GROUP_CONCAT(o.label ORDER BY o.position SEPARATOR ', ')
       )                              AS answer
FROM   answers a
JOIN   questions q          ON q.id = a.question_id
LEFT JOIN answer_options ao  ON ao.answer_id = a.id
LEFT JOIN question_options o ON o.id = ao.question_option_id
WHERE  a.response_id = 7
GROUP BY a.id, q.title, a.answer_text, a.answer_number
ORDER BY q.position;

Count votes for a single-choice question

SELECT o.label, COUNT(*) AS votes
FROM   answer_options ao
JOIN   question_options o ON o.id = ao.question_option_id
JOIN   answers a          ON a.id = ao.answer_id
WHERE  a.question_id = 103
GROUP BY o.id, o.label
ORDER BY votes DESC;

Golden Rules