Hi there,
I have the following problem: I'm developing some forms for data entry
for some clinical studies, with data stored on a PostgreSQL database.
The study is based in a questionnaire, with stablished options for
almost every question. They want answers to be stored as numeric codes
so they can later use the information for statistical analysis. The
current approach is to have some tables with valid answers and codes,
and then a table with questionnaire data, including foreign keys to
the corresponding table.
Now imagine you have a question that may be answered with yes (1) or
no (2). I would have something like the following:
CREATE TABLE yes_no
(
code integer NOT NULL,
description varchar(255),
CONSTRAINT "yes_no_PK" PRIMARY KEY (code)
)
INSERT INTO yes_no (code,description) VALUES (1,'yes');
INSERT INTO yes_no (code,description) VALUES (2,'no');
CREATE TABLE questionnaire
(
.......
someQuestion integer,
......
CONSTRAINT "someQuestion_FK" FOREIGN KEY (someQuestion)
REFERENCES yes_no (code)
)
The problem comes when you have questions that may be not applicable
(8), or optional (doesn't know, doesn't answer) (9). The easy solution
would be to have four tables:
yes_no
yes_no_not_applicable
yes_no_optional
yes_no_not_applicable_optional
and then stablish foreign keys to the corresponding tables. This is
quite cumbersome, as the example I have used here just have two
possible values, but there are some of this codifications that include
hundred of options (for example, cities): I would have four copies of
the same information with all the related problems.
My first try to solve this was to create a "data" table, a optional
table, a not applicable table and then create views as needed:
Tables:
yes_no
not_applicable
optional
Views (when needed)
yes_no_not_applicable (SELECT * FROM yes_no UNION SELECT * FROM not_applicable)
yes_no_optional (same idea)
yes_no_not_applicable_optional (same idea)
But this can't be done in PostgreSQL, as you can't create a foreign
key referencing a view.
So the question is: can this be solved in an easy portable way? maybe
I should rethink the design so special values (not applicable /
optional) aren't represented the same way or in the same column that
real data?
Any thoughts are really appreciated, best regards
Jose
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
