0

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

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by griswolf
0

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,
);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.