Re: Storing questionnaire data

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