Unconstrained Vocabularies

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Tue, 25 Feb 2003 21:58:53 GMT
Message-Id: <slrnb5nqsf.d5c.ajglist_at_izzy.net>

Imagine you want the user to choose from a list of possible and likely answers, but they also have the option to pencil in an answer. What are the various ways of modeling this problem?

I've created a table for the items to choose from, the vocabulary, and added a flag to note whether or not that item was penciled in.

If penciled, it means that that item is not to be displayed as an choice, it is only referenced once, it lives as long as the referenced row lives.

Here is a script that I hope is not terribly PostgreSQL specific:

------>8------>8------>8------>8------>8------>8------>8
CREATE TABLE Question
 (question_id VARCHAR(64) NOT NULL,
  wording VARCHAR(128) NOT NULL,
  PRIMARY KEY (question_id));

CREATE TABLE Response
 (question_id VARCHAR(64) NOT NULL,
  response_id VARCHAR(64) NOT NULL,
  wording VARCHAR(128) NOT NULL,
  penciled_in BOOLEAN NOT NULL,
  PRIMARY KEY (question_id, response_id),   FOREIGN KEY (question_id) REFERENCES Question);

CREATE TABLE Survey
 (survey_id VARCHAR(64) NOT NULL,
  question_id VARCHAR(64) NOT NULL,
  response_id VARCHAR(64) NOT NULL,
  PRIMARY KEY (survey_id, question_id, response_id),   FOREIGN KEY (question_id, response_id) REFERENCES Response);

INSERT INTO Question (question_id, wording) VALUES('fav-color', 'What id your favorite color?');

INSERT INTO Response (question_id, response_id, wording, penciled_in) VALUES('fav-color', 'cyan', 'Cyan', false); INSERT INTO Response (question_id, response_id, wording, penciled_in) VALUES('fav-color', 'black', 'Black', false); INSERT INTO Response (question_id, response_id, wording, penciled_in) VALUES('fav-color', 'magenta', 'Magenta', false); INSERT INTO Response (question_id, response_id, wording, penciled_in) VALUES('fav-color', 'yellow', 'Yellow', false);

INSERT INTO Survey (survey_id, question_id, response_id) VALUES ('sally', 'fav-color', 'cyan');

INSERT INTO Response (question_id, response_id, wording, penciled_in) VALUES('fav-color', 'periwinkle', 'Periwinkle', true); INSERT INTO Survey (survey_id, question_id, response_id) VALUES ('steve', 'fav-color', 'periwinkle');

SELECT Survey.survey_id,
       Question.wording,
       Response.wording, Response.penciled_in

  FROM Survey
  JOIN Response USING (question_id, response_id)   JOIN Question USING (question_id);

DROP TABLE Survey;
DROP TABLE Response;
DROP TABLE Question;
------>8------>8------>8------>8------>8------>8------>8

-- 
[Quoted] Alan Gutierrez - ajglist_at_izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

Received on Tue Feb 25 2003 - 22:58:53 CET