Re: How do i store arbitrary questions and answers in SQL?

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.