Table of Contents
This documentation provides information about Opinio 6.0 database structure and description of tables, indexes and foreign keys.
Opinio database is a relational database that stores information about survey, respondent, reports and all other Opinio data. The database is total contains 101 tables.
We tried to keep Opinio databases as simple as possible to give the Opinio user a wide selection of supported databases. Opinio database doesn't rely on stored procedures, triggers, functions and other database programming features because a lot of databases don't support them.
Several sql scripts to create database are included in the Opinio distribution, one for each supported database. By supported database we mean a database that has been tested by Opinio developers and added to automatic upgrade inbuilt in Opinio.
History of database support
| Opinio version | Database supported |
|---|---|
| Opinio 4.0 |
|
| Opinio 4.1 - 5.2.11 |
|
| Opinio 6.0 |
|
In this document we use MySQL data types to describe table columns. Column types may vary from database to database.
18. December 2007
General information about the system.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SystemInfoId | BIGINT | NOT NULL | PK | Unique id |
| Language | VARCHAR(100) | NULL | Default language | |
| MainMailserver | VARCHAR(100) | NULL | Main mail server to use when sending email | |
| SecondMailserver | VARCHAR(100) | NULL | The SMTP email server to use if the main server is down | |
| Cache_size | INTEGER | NULL | Survey cache size | |
| LicenseCode | VARCHAR(100) | NULL | The license code controls whether Opinio runs in Demo or full mode. |
One record represents one system attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SystemAttributeId | BIGINT | NOT NULL | PK | Unique id |
| AttributeName | VARCHAR(30) | NOT NULL | Short name of the system attribute | |
| StringValue | VARCHAR(255) | NULL | String value of the attribute | |
| LongValue | BIGINT | NULL | Long value of the attribute | |
| BigTextValue | TEXT | NULL | Big text value of the attribute | |
| FloatValue | DOUBLE | NULL | Double value of the attribute | |
| SystemInfoId | BIGINT | NOT NULL | FK | References OPS_SystemInfo table |
Defines a user.
One record represents one user attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| UserAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the user attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| UserId | BIGINT | Not null | FK | References OPS_User table |
Defines a group of Opinio users.
Each record maps one user to one user group. One user can be a member of unlimited number of user groups.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| UserGroupMemberId | BIGINT | Not null | PK | Unique id |
| UserId | BIGINT | Not null | FK | References OPS_User table |
| UserGroupId | BIGINT | Not null | FK | References OPS_UserGroup table |
Represents a set of permissions for a resource. Resource are reusable items, such as drop down lists, headers/footers and survey objects, such as survey group(folder), survey, report and so on. One record exists for one resource.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ResourcePermissionId | BIGINT | Not null | PK | Unique id |
| ResourceType | INTEGER | Null | Type of the resource:
|
|
| ResourceId | BIGINT | Null | Id of the resource. Example: if resource type is survey, then ResourceId is survey id. | |
| PermissionList | VARCHAR(255) | Null |
Permissions to the resource. Stored as a string with access types separated by comma and enclosed in braces.
Example: {1, 2, 7}
|
|
| UserId | BIGINT | Null | FK | References OPS_User table |
Defines survey group.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyGroupId | BIGINT | Not null | PK | Unique id |
| GroupTitle | VARCHAR(100) | Null | Survey group name | |
| ParentGroupId | BIGINT | Null | Id of the parent survey group | |
| Description | VARCHAR(255) | Null | Survey group description | |
| SortBy | INTEGER | Null | Sort by value
|
|
| CreatedDate | BIGINT | Null | Created date | |
| UpdatedDate | BIGINT | Null | Last updated date |
Defines a survey. This table contains necessary information about a survey. All general information is defined in OPS_SurveyAttribute table.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyId | BIGINT | Not null | PK | Unique id |
| SurveyName | VARCHAR(100) | Not null | Name of the survey (used in the administration module) | |
| CreatedDate | BIGINT | Null | Date created | |
| CreatedBy | BIGINT | Null | User id who created the survey. | |
| SurveyGroupId | BIGINT | Null | FK | References OPS_SurveyGroup table |
| DeleteStatus | INTEGER | Null | Deleted status (not is use) | |
| ContentType | INTEGER | Null | Survey content type:
|
|
| ParentId | BIGINT | Null | References OPS_Survey.SurveyId. Is NULL if not a child survey. | |
| LastEditedBy | BIGINT | Null | Id of the last user who has updated the survey |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK16 | OPS_Invitation.SurveyId | SurveyId |
| OPS_FK19 | OPS_Question.SurveyId | SurveyId |
| OPS_FK34 | OPS_Report.SurveyId | SurveyId |
| OPS_FK38 | OPS_Respondent.SurveyId | SurveyId |
| OPS_FK71 | SurveyGroupId | OPS_SurveyGroup.SurveyGroupId |
| OPS_FK72 | OPS_SurveyAttribute.SurveyId | SurveyId |
| OPS_FK73 | OPS_SurveyPage.surveyId | SurveyId |
| OPS_FK87 | OPS_SurveyPageAttribute.SurveyId | SurveyId |
| OPS_FK105 | OPS_PanelSignUpSurvey.SurveyId | SurveyId |
| OPS_FK102 | OPS_SurveyPiping.SurveyId | SurveyId |
| OPS_FK94 | OPS_Survey.ParentId | SurveyId |
| OPS_FK95 | OPS_SurveyComment.SurveyId | SurveyId |
One record represents one survey attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
Represents a page with questions (survey section).
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyPageId | BIGINT | Not null | PK | Unique id |
| FromQuestion | INTEGER | Null | Number of the first question on the page | |
| ToQuestion | INTEGER | Null | Number of the last question on the page | |
| PageNo | INTEGER | Null | Sequential position of the page. Starts at 0. | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
One record represents one survey page (section) attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyPageAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| SurveyPageId | BIGINT | Not null | FK | References OPS_SurveyPage table |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
Stores survey comments
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyCommentId | BIGINT | Not null | PK | Unique id |
| SurveyId | BIGINT | Not null | References OPS_SurveyId.SurveyId | |
| CommentText | TEXT | Null | The survey comment | |
| CommentBy | VARCHAR(50) | Null | Who added the comment | |
| EntryDate | BIGINT | Null | When the comment was added |
Stores survey piping elements info
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyPipingId | BIGINT | Not null | PK | Unique id |
| PipingName | VARCHAR(255) | Not Null | The name of piping element | |
| PipingType | INTEGER | Null | The piping type
|
|
| PipingSourceType | INTEGER | Null | The piping source type
|
|
| QuestionId | BIGINT | Null | Question id (set if piping source type is Question response) | |
| QuestionSourceType | INTEGER | Null | Since one question can have several inputs fields, this columns specifies which of the input is used for piping.
|
|
| IntextName | VARCHAR(255) | Null | The name of in-text element used for piping. | |
| MatrixCellCol | INTEGER | Null | Column of the matrix cell used for piping | |
| MatrixCellRow | INTEGER | Null | Row of the matrix cell used for piping | |
| URLParamName | VARCHAR(255) | Null | The name of url parameter used for piping (parameter name without "opdata_"). | |
| InviteeDataType | INTEGER | Null | Type of the invitee data used for piping:
|
|
| InviteeDataName | VARCHAR(255) | Null | The name of invitee attribute used for piping. | |
| FixedValue | VARCHAR(255) | Null | Piping value for fixed value piping type | |
| MultipleValuesDelim | VARCHAR(255) | Null | Delimiter for multiple piping values | |
| SurveyId | BIGINT | Not null | References OPS_SurveyId.SurveyId |
One question in a survey.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionId | BIGINT | Not null | PK | Unique id |
| QuestionNo | INTEGER | Null | The question number | |
| DisplayNo | VARCHAR(20) | Null | User defined number to display instead of default question number | |
| QuestionText | TEXT | Null | Question text | |
| QuestionType | INTEGER | Null | Type of the question, defines what kind of the question it is.
|
|
| TextBefore | TEXT | Null | Text before the question | |
| TextAfter | TEXT | Null | Text after the question | |
| QuestionLayout | VARCHAR(10) | Null | Layout of the question. Values:
|
|
| RelationKey | VARCHAR(10) | Null | Relation key | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK19 | SurveyId | OPS_Survey.SurveyId |
| OPS_FK20 | OPS_QuestionAttribute.QuestionId | QuestionId |
| OPS_FK22 | OPS_QuestionDropdown.QuestionId | QuestionId |
| OPS_FK23 | OPS_QuestionFreeText.QuestionId | QuestionId |
| OPS_FK24 | OPS_QuestionMatrix.QuestionId | QuestionId |
| OPS_FK28 | OPS_QuestionMultiple.QuestionId | QuestionId |
| OPS_FK30 | OPS_QuestionNumeric.QuestionId | QuestionId |
| OPS_FK31 | OPS_QuestionRating.QuestionId | QuestionId |
| OPS_FK33 | OPS_QuestionTagField.QuestionId | QuestionId |
| OPS_FK39 | OPS_ResponseDropdown.QuestionId | QuestionId |
| OPS_FK41 | OPS_ResponseEssay.QuestionId | QuestionId |
| OPS_FK43 | OPS_ResponseEssayNum.QuestionId | QuestionId |
| OPS_FK46 | OPS_ResponseEssayText.QuestionId | QuestionId |
| OPS_FK49 | OPS_ResponseFreeText.QuestionId | QuestionId |
| OPS_FK51 | OPS_ResponseMatrix.QuestionId | QuestionId |
| OPS_FK53 | OPS_ResponseMatrixNum.QuestionId | QuestionId |
| OPS_FK56 | OPS_ResponseMatrixText.QuestionId | QuestionId |
| OPS_FK59 | OPS_ResponseMultipleOption.QuestionId | QuestionId |
| OPS_FK61 | OPS_ResponseMultipleOptionNum.QuestionId | QuestionId |
| OPS_FK64 | OPS_ResponseMultipleOptionText.QuestionId | QuestionId |
| OPS_FK67 | OPS_ResponseNumeric.QuestionId | QuestionId |
| OPS_FK69 | OPS_ResponseRating.QuestionId | QuestionId |
| OPS_FK77 | OPS_Validator.QuestionId | QuestionId |
| OPS_FK79 | OPS_QuestionCondition.QuestionId | QuestionId |