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.
