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.
