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

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,
);