[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