[thelist] Survey answers schema

Ken Moore psm2713 at hotmail.com
Thu Jun 8 15:10:23 CDT 2006


Hi all,

Bill Moseley asked about saving different types of data.

It is important to remember that presenting data (reports) is very different 
from processing data. As a matter of fact, “This is my answer”, “Blue”, and 
“217” are all text. If what you need is reports, all you do is simply 
convert all answers to text and you are done.

If you need do calculations with a number field, convert back and then 
process.

Ken


>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
>
>--
>
>* * Please support the community that supports you.  * *
>http://evolt.org/help_support_evolt/
>
>For unsubscribe and other options, including the Tip Harvester
>and archives of thelist go to: http://lists.evolt.org
>Workers of the Web, evolt !

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/




More information about the thelist mailing list