Actions

Database Storage Engine Project M1 Database Design

From LimeSurvey Manual

About the milestone

This milestone includes creating documentation and descriptions of the database schemes explained at Database Storage Engine Project.

Questions : Database Layout

This is the design to store survey, section and question information and their related metadata.

QuestionStructure .png

Responses : Flat Database Layout

This is the layout we propose for storing survey responses in a single database table.

Flat.png

Responses : Multitable Database Layout

This is the layout we propose for storing survey responses in multiple tables. Responses for each question get stored in a seperate table.

Multitable2.png

Responses : Sectionflat Database Layout

This is the layout we propose for storing survey responses in multiple tables. Responses for each section get stored in a seperate table.

SectionFlat2.png

Question Type mappings

These are database mappings for the question types presented here. Since the question types are presented as single questions, there is no difference between the 3 proposed database layouts.

Mappings for questions to QuestionType classes

Type of question Mapping in QuestionType classes
Choice matrix questions ChoiceMatrixQuestionType (NumberOfRows rows and 1 column) with NumberOfRows child questions of type ChoiceQuestionType
Multi scale array ChoiceMatrixQuestionType (NumberOfRows rows and NumberOfCols columns) with NumberOfRows * NumberOfCols child questions of type ChoiceQuestionType
Various matrix questions MatrixQuestionType (NumberOfRows rows and NumberOfCols columns) with NumberOfRows * NumberOfCols child questions of a type by choice
Date question DateQuestionType
Gender question ChoiceQuestionType (possible to make a specific GenderQuestionType later)
Numerical input IntegerQuestionType or DoubleQuestionType
Multiple numerical input MatrixQuestionType (NumberOfRows rows and 1 column) with NumberOfRows child questions of type IntegerQuestionType or DoubleQuestionType
Ranking question RankingQuestionType
Boilerplate BoilerplateQuestionType
Yes/No Same case as gender questions
Language question LanguageQuestionType
Multiple options without other MatrixQuestionType (NumberOfRows rows and 1 column) with NumberOfRows child questions of type CheckboxQuestionType
Multiple options with other OtherQuestionType with X children of type CheckboxQuestionType and 1 child of type IntegerQuestionType/DoubleQuestionType/StringQuestionType/DateQuestionType/...
List questions ChoiceQuestionType
List question with other OtherQuestionType with a ChoiceQuestionType and an IntegerQuestionType/DoubleQuestionType/StringQuestionType/DateQuestionType/...
Text question StringQuestionType
Long text questions TextFieldQuestionType

Mappings for QuestionType classes to table columns

QuestionType class Columns in database table
StringQuestionType input: longtext
TextFieldQuestionType input: longtext
ChoiceQuestionType choice: unsigned integer (reference to model_answers)
DateQuestionType date: date
TimeQuestionType time: time
IntegerQuestionType input: integer
DoubleQuestionType input: double
CheckboxQuestionType input: unsigned tinyint(1)
BoilerplateQuestionType none
LanguageQuestionType input: char(3) (we suggest using the ISO 639-2 language codes)
RankingQuestionType 1: unsigned integer (reference to model_answers)
2: unsigned integer (reference to model_answers)
... as many keys as options. The key is the rank.
MatrixQuestionType none (data is stored by children)
ChoiceMatrixQuestionType none (data is stored by children)
OtherQuestionType none (data is stored by children)

Todo

  • Design flat DB layout (Done)
  • Design normalized DB layout (Done)
  • Design section-flat DB layout (Done)
  • Keep in mind all the Question types supported by LimeSurvey1 : Question types (Done)
  • Problem with loops with current response layouts (sectionflat and multitable), flat has no support for loops. (Done)

Discussions

Comments and ideas here please:

Metadata requirements for databases

- time question displayed/made available to participant (added by Jason)

this may be an indication of when the page was served to the participants browser (time can be generated by web server/php) or in the case of conditional questions, it may need to indicate the time that the question became visible on the page (in which case it may need to be generated by javascript, with all those inherent problems.. maybe it should indicate how many seconds since the page was loaded, and that can be added to the page-load time?)

- time question answered by participant (added by Jason)

this may be a recording of the time that the data in the question was posted (time could be generated by web serer/php) or the time that the question lost focus (after having gained it) - which would be a javascript issue

Any other metadata requirements?