Re: How do i store arbitrary questions and answers in SQL? - Mailing list pgsql-general

Dang.  I re-read your post and see that you are not checking for the
correct answer, you simply want to store the responses like a
questionare.  Well I still think a different table for each type of
response would be beneficial, obviously, adding a serial field like you
have done and the foreign key linking to the qid.

I had a completely different problem on my mind (testing user's
knowledge).  Sorry about that.


macgillivary wrote:
> I agree with Tim, that your option 3 is really not all that hard, and I
> suggest would provide you with the best solution.  I'm assuming,
> however, that your application asks the question and checks the user
> supplied answer with the answer in the db (and not Jeopardy style).  I
> might add a 'join' or 'answertype' field in the questions (qu) table to
> assist when drawing the application and to immediately know what table
> to verify the supplied answer against.  Otherwise, I think you might
> need a query to check the type on the answer the user supplies and make
> a possible few passes (through the numeric and integer tables for
> example when the user's response is 42) - or some combination of outers
> to find the non null value field.
>
> For what it's worth, I'd probably also include a 'type' table to hold
> my application specific mask for the user's reply, and perhaps a
> attribute to hold the joining table name (I'm thinking about the future
> requirement to add a new type without much coding changes on the
> application side).
>
> So, now I'm interested in this, and I could see a use for something
> similar in the near future, I'll put the following out there for
> comment.  Obviously I haven't put it into practice but perhaps it could
> work.  One of differences from your original post is the absense of a
> serial field on the responses.  I've only put a primary key which would
> link back to the the questions.qid field (so maybe it should be a fk?
> see my note about my pg newness in a moment).  I'm assuming this is not
> a multiple choice type of situation.  Each question has one answer
> (although not currently enforced in the db layer here since there is
> nothing stopping you from placing an answer in more than one of the
> response tables - I'm relatively new to pg and not sure of anyway to
> deal with this).  It's early, haven't had my first cup yet, but I would
> start with something like the following:
>
> CREATE TABLE questions  -- holds the questions or challenges
> (
>   qid serial NOT NULL,
>   qchallenge text,
>   qtype int2,   -- linking to types.tid
>   CONSTRAINT questions_pkey PRIMARY KEY (qid)
> )
> WITHOUT OIDS;
>
> CREATE TABLE types  -- mostly to assist application development
> (
>   tid serial NOT NULL,
>   tdescription varchar(25),
>   tmask varchar(25),   -- just a thought, could be useful when building
> a web app
>   tjoin varchar,   -- again, just thinking about ease of new additions
>   CONSTRAINT types_pkey PRIMARY KEY (tid)
> )
> WITHOUT OIDS;
>
> CREATE TABLE response_numeric
> (
>   rnqid int2 NOT NULL,   -- linking to questions.qid
>   rnvalue numeric,
>   CONSTRAINT response_numeric_pkey PRIMARY KEY (rnqid)
> )
> WITHOUT OIDS;
>
> CREATE TABLE response_integer
> (
>   riqid int2 NOT NULL,
>   rivalue int4,
>   CONSTRAINT response_integer_pkey PRIMARY KEY (riqid)
> )
> WITHOUT OIDS;
>
> CREATE TABLE response_text
> (
>   rtqid int2 NOT NULL,
>   rtvalue text,
>   CONSTRAINT response_text_pkey PRIMARY KEY (rtqid)
> )
> WITHOUT OIDS;
>
> CREATE TABLE response_date
> (
>   rdqid int2 NOT NULL,
>   rdvalue date,
>   CONSTRAINT response_date_pkey PRIMARY KEY (rdqid)
> )
> WITHOUT OIDS;
>
>
>
> Tim Allen wrote:
> > > 3. Different answer tables each with answer types - same problem as 2
> > > but even harder.
> >
> > This is the other option I mentioned above. It's not hard at all.