Database
| Table | Description |
|---|---|
| ServerMetadata | It is used to stored the server metadata like the server controlDataelements. |
| Media | This table stores the mediatype, the uri, and the related question.Only available in QApp. The media type define the kind of resource, 0 for a imagen, 1 for a video. |
| Score | This table stores the survey scores. |
| SurveySchedule | This table stores a optional comment and the previous date. |
| Survey | This table stores all the survey relations(tabgroup,orgunit,user,creation date, upload date, schedule date and status(The survey status can be -1 Planned, 0 in progress, 1 completed or 2 sent). |
| OrgUnit | This table stores the organisation units, one organisation unit can be parent of other organisation unit, and the organisation units have a related org Unit Level. |
| OrgUnitLevel | This table stores the org unit level id and name. |
| OrgUnitProgramRelation | This table stores the relations between organisation units and programs, and its productivity. |
| Program | This table stores the program uid and the program name. |
| TabGroup | This table stores the tabgroups, and its related program. One tabgroup is a group of tabs |
| Tab | This table stores the name, order pos, type, and related tabgroup. One tab is a group of headers |
| Header | This table stores header, short_name, name, order pos, and the related tab. One header is a group of questions |
| Question | This table stores the questions, the order form_name (used to display the question), the order_pos (to know the order position for each tab), the related composite score, a possible question parent, the header, the answer, the output (it is the type of question), the row used in eds, the column used in eds, and the numerator and denominator used in the composite score calculation. |
| CompositeScore | This table stores the Composite Scores, the Composite Scores can be parents, and they are used to group the questions and know the grouped questions scores. |
| Answer | This table stores the type of answers. |
| Value | This table stores the survey answered values, and save the related question, related survey, related option, and the uploaded date to know when the value was changed. |
| Option | This table stores the possible answer options. In QApp we can use the path column, and in Malaria Case Surveillance the id_option_attribute. |
| OptionAttributte | This table stores the option background color, only used in Malaria Case Surveillance. |
| User | This table stores the logged-in user. |
| QuestionOption | This table stores the related Question, the related Option, and the related Match. |
| QuestionRelation | This table stores the question relation operation for a related question, and is used to know if the match is a relation Parent/child or a match operation that change a option value. |
| Match | This table stores the match and the related QuestionRelation. |
The parent-child attribute in the server is 24 Question Hide Type. And its group by the attribute 25 Question Hide Group.
The parent-child relation is represented in the tables QuestionRelations QuestionOption and Match. For the given table QuestionOption:
| id_question_option | id_option | id_questionn | id_match |
|---|---|---|---|
| 1 | 96 | 1 | 1 |
| 2 | 96 | 1 | 2 |
| 3 | 96 | 1 | 3 |
| 4 | 96 | 1 | 4 |
When the user select the 96 id_option(in this example is "yes") for the 1 id_question, the application will search all the "id_match" for that coincidence.
The id_match is a reference of the "Match" table. In the Match table we have the id_question_relation as foreing key.
Match table:
| id_match | id_question_relation |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
The "operation" column say the type of operation and the Question child(in this case) in the QuestionRelation table.
QuestionRelation table:
| id_question_relation | id_question | operation | |:-:|:-:|---|---| | 1 | 2 | 1 | | 2 | 3 | 1 | | 3 | 4 | 1 | | 4 | 5 | 1 |
The operation is 1, 1 is a parent/child operation. The parent is the question with id 1, and the children are the questions with ids 2,3,4 and 5.
The parent-child attribute for a match operation in the server is 27 Question Match Type. And its group by the attribute 28 Question Match Group.
In the Match operation, two "Question Match Type" with Child value changes the option of a "28 Question Match Group" with parent value. The parent depends of the children values.
If the selected options are different than the QuestionOption options... the parent value is NO. Else, if the selected options are the same as the QuestionOption option, the parent value is Yes.
The parent-child relation is represented in the tables QuestionRelations QuestionOption and Match.
For the given table QuestionOption:
| id_question_option | id_option | id_questionn | id_match |
|---|---|---|---|
| 187 | 89 | 308 | 173 |
| 188 | 60 | 310 | 173 |
| 3 | 90 | 308 | 174 |
| 4 | 62 | 310 | 174 |
| 3 | 91 | 308 | 175 |
| 4 | 11 | 310 | 175 |
| 3 | 12 | 308 | 176 |
| 4 | 13 | 310 | 176 |
| 3 | 10 | 308 | 177 |
| 4 | 42 | 310 | 177 |
| 3 | 74 | 308 | 178 |
| 4 | 54 | 310 | 178 |
| 3 | 70 | 308 | 179 |
| 4 | 52 | 310 | 179 |
We can look groups of two questions/options for one match in the example QuestionOption table.
For each group of id_match:
If the questions options selected are the same as the Options saved in QuestionOption... It is a match..
For example, if we select the option 70 for the Question 308 and the Option 52 for the Question 310.
We need look the id_match 179 in the Match table because the id_match is a reference of the Match table, and in the Match table we have the id_question_relation.
Match table:
| id_match | id_question_relation |
|---|---|
| 173 | 161 |
| 174 | 161 |
| 175 | 161 |
| 176 | 161 |
| 177 | 161 |
| 178 | 161 |
| 179 | 161 |
We have the id_question_relation 161 for the id_match 179, and if we look in the QuestionRelation table, the related id_question is the "parent".
QuestionRelation table:
| id_question_relation | id_question | operation | |:-:|:-:|---|---| | 161 | 312 | 0 |
The 161 id_question_relation have an operation equals to 0, is a "Match" operation, and the Question 312 need be set as Yes, because the question options in QuestionOption does match.
For example, if we change the selected value for a 308 Question, from 70 to 71, the "parent" question(312) will be set as NO.
It is explained in this external link