Hi Sam, thanks for your suggestion.
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.
On Thu, Oct 23, 2008 at 7:01 PM, Sam Mason <sam@samason.me.uk> wrote:
> On Wed, Oct 22, 2008 at 03:59:07PM +0100, Thom Brown wrote:
>> I have previously had a questionnaire which had 5 tables, questions
>> and answers and question types, questionnaire and results.
>
> This design looks a lot like the EAV (entity-attribute-value) style of
> database design. This tends to be frowned upon here but is good in some
> situations. I personally have tended to just go for a simple field per
> question spread over several tables (if it's a big questionnaire). I.e.
> something like:
>
> CREATE TABLE questionnaire (
> qnid SERIAL PRIMARY KEY,
> entryname TEXT,
> qndate DATE
> );
>
> CREATE TABLE questionnaire_page1 (
> qnid INTEGER PRIMARY KEY REFERENCES questionnaire,
> business_purpose TEXT,
> average_turnover NUMERIC,
> num_employees INTEGER
> );
>
> CREATE TABLE questionnaire_buildings (
> qnid INTEGER REFERENCES questionnaire,
> buildnum INTEGER,
> PRIMARY KEY (qnid, buildnum),
> buildingname TEXT,
> buildingsize NUMERIC,
> lightlevel INTEGER,
> ventilation INTEGER
> );
>
> This way you can enforce useful constraints inside the database, but
> requires the database to be aware of what questionnaire data you're
> actually storing. The EAV style would, in my eyes, be appropriate if
> you're trying to write a generic program that could handle arbitrary
> questionnaire forms and not just one specific one.
>
> Hope that helps
>
>
> Sam
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
