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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
