[thelist] Survey answers schema

[Bill Moseley]

Another database question.  For a survey I have a table that holds
answers.  But a question can generate different types of answers --
such as free text, a enum list of options( "red", "blue"), or an intger (1..10).

So, I'm wondering how best to handle that in the database.

    create table survey_answer (
        id              SERIAL PRIMARY KEY,
        -- which response this answer belongs to
        response        integer NOT NULL REFERENCE survey_response,
        question        integer NOT NULL REFERENCES survey_question,
        answer_text     text,   - text response, or select
        answer_int      integer
    );

I'm not very fond of the multi-columns.  Is there a better method?

Here's more of the schema:

    -- Table to hold the survey

    create table survey (
        id              SERIAL PRIMARY KEY,
        name            text,
        comment         text,
        active          boolean NOT NULL DEFAULT TRUE,
        created_time    timestamp(0) with timezone
    );

    create table question_type (
        id              SERIAL PRIMARY KEY,
        active          boolean NOT NULL DEFAULT TRUE,
        name            text
    );

    insert into question_type (name) values ('1 to 10');
    insert into question_type (name) values ('Select');
    insert into question_type (name) values ('Free Form');



    -- Define the questions
    create table survey_question (
        id              SERIAL PRIMARY KEY,
        survey          integer NOT NULL REFERENCES survey,
        name            text, -- question text
        active          boolean NOT NULL DEFAULT TRUE,
        order           integer default 1,  -- define order of questions.
        question_type   integer NOT NULL REFERENCES question_type
    );

    -- This groups responses together
    create table survey_response (
        id              SERIAL PRIMARY KEY,
        survey          integer NOT NULL REFERECES survey,
        start_time      timestamp(0) with timezone,
        end_time        timestamp(0) with timezone,
        class           integer REFERENCES clsss  -- if about an event
    );




-- 
Bill Moseley
moseley at hank.org