Dear group,
I have been bugged by this problem on and off for years, and would like
to put it out to general discussion. I suspect it is a common SQL
problem but I have never found a satisfactory answer - maybe there is
not one.
The problem : to store, in SQL/RDBMS, an arbitrary set of questions and
their answers, where the questions are text (obviously) but the answers
can be dates, text, integers, money etc.
think of it as a big questionnaire where at design time we have no idea
what the questions will be.
My usual solution to this problem is to store everything in varchar and
flag the type, converting
as I extract data. It is not a desirable solution.
i.e.
CREATE TABLE Qu (ID INT, Question VARCHAR(64))
CREATE TABLE Answer(ID INT, questionID INT, answer_text VARCHAR(255),
datatype INT)
Are there any other solutions out there?
cheers
PS
I think I am essentially looking for a database that stores a datatype
of VARIANT (for those of MS VB background). However my (patchy)
knowledge of RDBMS internals says this is essentially breaks all the
rules for optimisation and storage so is never offered.
I can think of several ideas but they all fall short in some way
0. (current one) I have to cast any search term to string first but
after that the SQL performs as one expects.
1. store the serialised object in binary form. (how does one search
this? Totally dependant on choice of middleware language)
2. Store different types in diff columns
table answer (questionID, ans_text VARCHAR, ans_money MONEY,
ans_int INT ....
But this makes searching through SQL even harder than casting, as
in each query i must what answer to expect.
3. Different answer tables each with answer types - same problem as 2
but even harder.
4. I suspect pl/python might be useful, but I cannot see how at the
moment...
