Hello everyone -

We've designed a web survey with a MySQL backend. The survey is
composed of a series of questions with a possible answer of 1-5 or N/
A, in a particular order. We have a table Questions that stores the
text of the question and its answer.

CREATE TABLE `Questions` (
`id` int(10) unsigned NOT NULL auto_increment,
`question` text NOT NULL,
`answer` enum('1','2','3','4','5','N/A') default NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

A NULL answer value means that the respondent has not answered the
question.

Now we are being asked to add survey questions that can have answers
of different data types. The spec is to have the survey
'configureable', so that at any point in the future, when someone
says, "We need a new survey that asks {text answer question}, {1-5
question}, {true false question}", we can do that without changing the
database structure.

I'm trying to think of the best way to store those answers, but every
method I come up with seems somewhat hackish.

Some questions may have a yes/no or true/false answer, some may have
an integer answer ( "How many times in the past month have you used
tech support?"), another answer may have a date, a string, a multiple
choice with a single value, a multiple choice with multiple values,
etc. Or sometimes, a particular answer value may prompt a sub-question
( "What disappointed you about...?" )

Brainstorming, the best method I could come up with is to have a
different table for each answer type, but that feels susceptible to
data integrity issues. In other words, I would have

CREATE TABLE `Questions` (...);
CREATE TABLE `TrueFalseAnswers` (...);
ALTER TABLE `TrueFalseAnswers`
ADD CONSTRAINT `TrueFalseAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE `TextAnswers` (...);
ALTER TABLE `TextAnswers`
ADD CONSTRAINT `TextAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

etc.

One problem with the above is that I can't guarantee that at least one
and only one answer exists for any question in the DDL alone.

Another solution might be to have a binary or string column for the
answer in the Questions table, and encode all answers into some string
or binary format, and store them in the answer column. That gives me
at least one and only one answer for every question, but then I lose
access to aggregate features in SQL queries. This strikes me as not a
very 'relational' solution.

So, I see problems with the two ideas outlined above. Is there a
'best' way to solve this problem?

lawpoop wrote:

Yes, but no one ever seems to follow the advice. So before we use
up any of our finite and dwindling lifetime typing it out for you, do
you promise to follow the advice?

This is a programming problem not a database design problem. Even
an SQL DBMS can handle this problem very easily. In fact this is, in
miniature, exactly the kind of problem relational/SQL databases were
conceived to solve. To apply the 'best' solution you will have to write
one moderately complicated program that will read the database metadata
but it will work for all future questionnaires. I am not being
euphemistic or ironic--it will be only moderately complicated.

So, do you promise?

--
Roy

On Apr 21, 12:01*am, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
Can you just copy and paste the solution? Or post a link?

If it's so great, why doesn't anyone ever seem to follow it? I'm not
interested in the hacky methods that I've thought of so far. If it is
demonstrably better, I'll use it

I'm supremely uninterested in wasting your time. If you give me a few
keywords, I'll google the solution and do all the heavy lifting of
explaining it to myself.

Oh what the heck. I promise!

[Snipped]
If you are not interested in hacky methods as you claim, please don't
expect Roy to post the solution or a link as an immediate
satisfactory. In database design, hacked and immediate solutions are
almost synonyms.

material of value has been posted on Google. Grabbing a good book and
reading will help you even more.


You just swallowed the red pill in Matrix...

Regards...

On Apr 22, 6:33*am, cim... (AT) hotmail (DOT) com wrote:
I'm not looking for 'immediate satisfactories', I'm look for a
solution. If the hold-up is the time it might take him to type up a
response, I'm trying to find ways to work around that. I was thinking
that maybe he has posted this solution before, perhaps to a newsgroup,
and so as not to waste any more of his regrettably mortal life, he
could simply refer me to that thread, or something along those lines.

That sounds wonderful! Can you give me a title?

Oh, is that why I'm standing in a sewer, covered in goo?

lawpoop wrote:

_Practical Issues In Database Management: A Reference for the Thinking
Practitioner_ by Fabian Pascal.

ISBN-10: 0201485559
ISBN-13: 978-0201485554

lawpoop wrote:

Your concern for the limit of my remaining span has touched me
sufficiently that I will sketch (no more) a solution for you. As cimode
says, you won't find a recipe on the net because 99.99% of what
is written about database design on the net is purest trash. Scarcely
believable, but true.

There is considerable debate over which is the best title to start with.
If you consider yourself a willing beginner with very limited time,
then I might venture *far* into left-field and suggest a long
out-of-print book by Fabian Pascal: SQL and Relational Basics
(M&T Books, ISBN 1-55851-063-X). It is concise and accurate (especially
for its time). I suggest it just to get you in the right
frame of mind quickly, and to innoculate you against the crap that is
readily and widely available. After you've read that, I might suggest
An Introduction to Database Systems by C J Date, though that is not an
easy or quick read. If you can make even a little progress with that
you won't ever feel the need of a recipe again. I am sure others here
will have their personal favorite titles and opinions differ.

You too?

Sketch to follow later.

--
Roy

Bob Badour wrote:

I was going to mention that one. (I have an autographed copy that Fabian
gave me!) But I decided his much earlier book is the nearest thing
to "Relational Databases for Beginners" that anyone has ever written.

--
Roy

On Apr 22, 7:16*am, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:

Thanks to you and Bob, I just placed an order for it. :

That might be next!

My sincerest gratitude!

On Apr 22, 7:26*am, lawpoop <lawp... (AT) gmail (DOT) com> wrote:
Actually, I skimmed to quickly. I ordered Practical Issues in Database
Management. But Amazon lists used copies of _SQL..._ starting at $5, I
might throw that in also.