Very long questionnaires, unlikely to change

[juancruzhurtado]

Hello everybody!
I am starting to work on a web page for a medical firm.
This webpage will include about ten different questionnaires for Patients.
The longest questionnaire has 160 questions, most of them having a yes/no answer.

I am not sure what the best way to go about this is.

The questionnaires are not likely to change.
So, should I a table for each questionnaire?
Or should I have a three tables: questionnaires, questions, and answers?

The latter is probably better for scalability, but the other on might be better to make data decisions with.

I am not sure if this was clear enough. Hopefully it was.

I appreciate any answers, tips, comments.

Juan

[griswolf]

In my experience 'not likely to change' means that the required changes will appear just before product release, so it is best to be prepared. Thus, you will want a table or tables to describe the questions.

• One table: There are columns for id, question, ordinal_in_questionnaire and questionnaire_holding_this_question.
  • Pros:
    • Easy to re-locate the question
    • Easy to add a questionnaire
    • Easy to select answers from any or all questionnaires if desired
  • Cons: None that I see
• Multiple tables: there are columns for id, question and ordinal_in_questionnaire, 'which questionnaire' is encoded in the table name
  • Pros: None that I see
  • Cons: see 'Pros' points for single table

Note that you may want to plan this for multi-language use, in which case you might have only indices in the 'question' column, used to look up the question in the appropriate language

As for response data: If in the USA, you are doing this in a HIPPA environment, so you need to be very thoughtful about linking answers to patients. However, simplistically, you will want a table that 'somehow' links a patient with a patient_id, and a link table that associates the answer with question_id, patient_id and timestamp (since these questions may have time-varying answers)

So I'd have something like this (beware: not syntax checked, not thoughtfully complete)

create table question (
  id int(11) not null auto_increment,
  questionnaire varchar(20),
  ordinal_in_questionnaire int(5),
  question varchar(500)
);

create table patient (
  id int(11) not null auto_increment,
  name varchar(50) /* not really */
);

create table answer (
  id int(11) not null auto_increment, /* not strictly needed */
  patient_id int(11) foreign key references patient:id,
  question_id int(11) foreign key references question:id
  answer varchar(1000),
  answer_time timestamp,
);