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

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.