Re: Storing questionnaire data - Mailing list pgsql-general

On Thu, 2008-10-23 at 21:38 +0100, Thom Brown wrote:
> I'm afraid such a rigid structure is completely tailored for a
> specific questionnaire.  What if I, or even a client, wanted to
> generate different questionnaires?  I would like the data to indicate
> the flow of questions and answers rather than just use the database as
> pure storage for a completely coded solution.

If you construct a schema in such a way that there's really no
constraint on the data at all, then the user of that permissive schema
is effectively designing the database.

This is not necessarily a bad thing (or rather, it may not be
avoidable). For instance, if you have no idea what kind of questions
might be asked by the questionnaire, nor any idea what kind of questions
might be asked about the responses they receive to the questionnaire,
there's not much you can do. Pretty much anything is going to look a lot
like EAV.

The cost of this, however, is that you (as the designer of the
permissive schema) can no longer see any meaning in the data at all. You
may be able to dig around manually a bit and find out a few specific
things, but you can't do it in any automated way. This is because you
aren't the real designer of the database, you've passed that job along
to your users. Only they (hopefully) have any idea what it might mean.
The users might not be good database designers, in which case they'll
end up with a mess, and you won't be able to help them.

Also, as a performance matter, the optimizer also has no idea what your
data means, and so it can't take any useful shortcuts. So, it will
probably be slow.

The best you can really do is try to find whatever basic meaning you
can. Usually there is something there: there are basic data types people
will want (e.g. string, numeric, timestamp). There are questions,
perhaps groups of questions, order in which the questions should be
asked, order in which the questions are answered, time the question was
answered, and respondents. There is one (or fewer) answer per question
per respondent. Try to piece this stuff together in some way as to
provide maximum meaning to you (and to PostgreSQL) without destroying
the usefulness to your customers.

I think the article David mentioned:
http://www.varlena.com/GeneralBits/110.php
Is a pretty reasonable compromise for many use-cases. Perhaps more can
be done, but usually questionnaires are either too unimportant to really
dig in, or so important that designing a database around it is the
obvious thing to do.

Regards,
    Jeff Davis