Database Storage Engine Project M1 Database Design
From LimeSurvey Manual
- 1 About the milestone
- 2 Questions : Database Layout
- 3 Responses : Flat Database Layout
- 4 Responses : Multitable Database Layout
- 5 Responses : Sectionflat Database Layout
- 6 Question Type mappings
- 7 Todo
- 8 Discussions
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.
Responses : Flat Database Layout
This is the layout we propose for storing survey responses in a single database table.
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.
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.
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|
|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|
|Yes/No||Same case as gender questions|
|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 question with other||OtherQuestionType with a ChoiceQuestionType and an IntegerQuestionType/DoubleQuestionType/StringQuestionType/DateQuestionType/...|
|Long text questions||TextFieldQuestionType|
Mappings for QuestionType classes to table columns
|QuestionType class||Columns in database table|
|ChoiceQuestionType||choice: unsigned integer (reference to model_answers)|
|CheckboxQuestionType||input: unsigned tinyint(1)|
|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)|
- 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)
Comments and ideas here please:
Metadata requirements for databases
- time question displayed/made available to participant (added by Jason)
- time question answered by participant (added by Jason)
Any other metadata requirements?