Opinio database description

Table of Contents

Introduction
Tables
OPS_SystemInfo
OPS_SystemAttribute
OPS_User
OPS_UserAttribute
OPS_UserGroup
OPS_UserGroupMember
OPS_ResourcePermission
OPS_SurveyGroup
OPS_Survey
OPS_SurveyAttribute
OPS_SurveyPage
OPS_SurveyPageAttribute
OPS_SurveyComment
OPS_SurveyPiping
OPS_Question
OPS_QuestionAttribute
OPS_QuestionTagField
OPS_QuestionRating
OPS_QuestionMultiple
OPS_QuestionMultipleOption
OPS_QuestionNumeric
OPS_QuestionMatrix
OPS_QuestionMatrixGroup
OPS_QuestionMatrixCell
OPS_QuestionFreeText
OPS_QuestionDropdown
OPS_QuestionComment
OPS_Dropdown
OPS_DropdownItem
OPS_Validator
OPS_ValidatorAttribute
OPS_BranchRating
OPS_BranchRatingOption
OPS_BranchMultiple
OPS_BranchMultipleOption
OPS_BranchNumeric
OPS_BranchDropdown
OPS_BranchDropdownOption
OPS_BranchMatrix
OPS_BranchMatrixOption
OPS_Invitation
OPS_Invitee
OPS_InviteeAttribute
OPS_Respondent
OPS_RespondentAttribute
OPS_ResponseFreeText
OPS_ResponseNumeric
OPS_ResponseEssay
OPS_ResponseEssayText
OPS_ResponseEssayNum
OPS_ResponseRating
OPS_ResponseDropdown
OPS_ResponseMultipleOption
OPS_ResponseMultipleOptionText
OPS_ResponseMultipleOptionNum
OPS_ResponseMatrix
OPS_ResponseMatrixText
OPS_ResponseMatrixNum
OPS_HeaderFooter
OPS_InvitationList
OPS_InvitationListItem
OPS_InvitationListItemAttr
OPS_Report
OPS_ReportAttribute
OPS_ReportElement
OPS_ReportElementAttribute
OPS_ReportElementQuestionNo
OPS_ReportQuestionInterval
OPS_ReportQuestionLabel
OPS_QuestionCondition
OPS_QuestionConditionOption
OPS_ReportFilter
OPS_ReportFilter_QuestionCond
OPS_ReportDrilldown
OPS_ReportRespondent
OPS_ReportFile
OPS_ReportPortal
OPS_ReportPortalAttribute
OPS_ReportPortalItem
OPS_Panel
OPS_PanelAttribute
OPS_PanelAttributeItem
OPS_PanelCategory
OPS_PanelSignUpSurvey
OPS_Panelist
OPS_PanelistAttribute
OPS_PanelistHistory
OPS_PanelUnverified
OPS_SampleDefinition
OPS_SampeCondition
OPS_SampeConditionValue
OPS_ScheduledTask
OPS_FailedEmailMsg
OPS_FailedEmailRecipient
OPS_Host
OPS_UploadedFile
OPS_UploadedFileCheck
OPS_PluginProperty
OPS_SequenceID
OPS_ContextGuideItem
OPS_RecentItem

Introduction

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
  • Hypersonic SQL
  • MySQL
  • Microsoft SQL Server
  • Oracle
Opinio 4.1 - 5.2.11
  • Hypersonic SQL
  • MySQL
  • Microsoft SQL Server
  • Oracle
  • PostgreSQL
  • IBM DB2
Opinio 6.0
  • Derby
  • MySQL
  • Microsoft SQL Server
  • Oracle
  • PostgreSQL
  • IBM DB2

In this document we use MySQL data types to describe table columns. Column types may vary from database to database.

18. December 2007

Tables

OPS_SystemInfo

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.

Foreign keys

Foreign key Child Parent
OPS_FK78 OPS_SystemAttribute.SystemInfoId SystemInfoId
OPS_FK91 OPS_Host.SystemInfoId SystemInfoId

OPS_SystemAttribute

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

Foreign keys

Foreign key Child Parent
OPS_FK78 SystemInfoId OPS_SystemInfo.SystemInfoId

OPS_User

Defines a user.

Columns

Column Data type Allow NULLs Key Notes
UserId BIGINT Not null PK Unique id
Login VARCHAR(20) Not null   Login name
UserName VARCHAR(100) Null   User's personal name
Password VARCHAR(250) Not null   Password
EncryptionKey VARCHAR(100) Null   Encryption key

Indices

Index Columns
User_Login Login

Foreign keys

Foreign key Child Parent
OPS_FK37 OPS_ResourcePermission.UserId UserId
OPS_FK74 OPS_UserAttribute.UserId UserId
OPS_FK75 OPS_UserGroupMember.UserId UserId
OPS_FK103 OPS_Panel.UserId UserId
OPS_FK98 OPS_RecentItem.UserId UserId

OPS_UserAttribute

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

Indices

Index Columns
UserAttribute_Us1 UserId

Foreign keys

Foreign key Child Parent
OPS_FK74 UserId OPS_User.UserId

OPS_UserGroup

Defines a group of Opinio users.

Columns

Column Data type Allow NULLs Key Notes
UserGroupId BIGINT Not null PK Unique id
UserGroupName VARCHAR(100) Null   Name of the user group
Description VARCHAR(100) Null   Description of the user group

Foreign keys

Foreign key Child Parent
OPS_FK76 OPS_UserGroupMember.UserGroupId UserGroupId

OPS_UserGroupMember

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

Indices

Index Columns
UserGroupMember_1 UserId
UserGroupMember_2 UserGroupId
UserGroupMember_3 UserId, UserGroupId

Foreign keys

Foreign key Child Parent
OPS_FK75 UserId OPS_User.UserId
OPS_FK76 UserGroupId OPS_UserGroup.UserGroupId

OPS_ResourcePermission

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:
  • 0 = all resources
  • 1 = user group
  • 2 = survey group (folder)
  • 3 = survey
  • 4 = custom report
  • 5 = dropdown list
  • 6 = header
  • 7 = footer
  • 8 = plugin
  • 9 = invitation list(reusable list of invitees)
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}
  • 0 - admin access
  • 1 = read access
  • 2 = modify access
  • 4 = create access
  • 5 = access to survey invitations (if ResourceType is survey)
  • 6 = access to survey reports (if ResourceType is survey)
  • 7 = permission to delete responses (if ResourceType is survey)
  • 8 = permission to add a new survey to the group (if ResourceType is survey group)
  • 9 = permission to add a new survey group to the group (if ResourceType is survey group)
  • 10 = permission to administrate users (if ResourceType is user group)
UserId BIGINT Null FK References OPS_User table

Indices

Index Columns
ResourcePermissi1 UserId
ResourcePermissi2 ResourceType, ResourceId, UserId

Foreign keys

Foreign key Child Parent
OPS_FK37 UserId OPS_User.UserId

OPS_SurveyGroup

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
  • 0 - name
  • 1 - date
  • 2 - date reverse
CreatedDate BIGINT Null   Created date
UpdatedDate BIGINT Null   Last updated date

Indices

Index Columns
SurveyGroup_Pare1 ParentGroupId

Foreign keys

Foreign key Child Parent
OPS_FK71 OPS_Survey.SurveyGroupId SurveyGroupId

OPS_Survey

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:
  • 0 - survey is a survey
  • 1 - survey is a question library
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

Indices

Index Columns
Survey_SurveyGro1 SurveyGroupId

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

OPS_SurveyAttribute

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

Indices

Index Columns
SurveyAttribute_1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK72 SurveyId OPS_Survey.SurveyId

OPS_SurveyPage

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

Indices

Index Columns
SurveyPage_Surve1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK73 SurveyId OPS_Survey.SurveyId
OPS_FK86 OPS_SurveyPageAttribute.SurveyPageId SurveyPageId

OPS_SurveyPageAttribute

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

Foreign keys

Foreign key Child Parent
OPS_FK86 SurveyPageId OPS_SurveyPage.SurveyPageId
OPS_FK87 SurveyId OPS_Survey.SurveyId

OPS_SurveyComment

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

Foreign keys

Foreign key Child Parent
OPS_FK95 OPS_Survey.SurveyId SurveyId

OPS_SurveyPiping

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
  • 1 - piping of type text (the only type currently supported)
PipingSourceType INTEGER Null   The piping source type
  • 1 - question response
  • 2 - survey URL parameter
  • 3 - invitee data
  • 4 - fixed value
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.
  • 1 - in-text element
  • 2 - response to question type (rating/numeric/multiple/matrix cell/dropdown)
  • 3 - multiple Other option
  • 4 - open-ended response
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:
  • 1 - invitee name
  • 2 - invitee email
  • 3 - invitee attribute
  • 4 - invitee id
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

Indices

Index Columns
SurveyPiping1 PipingName, SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK102 OPS_Survey.SurveyId SurveyId

OPS_Question

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.
  • 0 - no type
  • 1 - rating
  • 2 - numeric
  • 3 - multiple
  • 4 - matrix
  • 5 - dropdown
TextBefore TEXT Null   Text before the question
TextAfter TEXT Null   Text after the question
QuestionLayout VARCHAR(10) Null   Layout of the question. Values:
  • vertical
  • horizontal
RelationKey VARCHAR(10) Null   Relation key
SurveyId BIGINT Not null FK References OPS_Survey table

Indices

Index Columns
Question1 SurveyId, QuestionNo, QuestionId

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

OPS_QuestionAttribute

One record for one question attribute.

Columns

Column Data type Allow NULLs Key Notes
QuestionAttributeId BIGINT Not null PK Unique id
AttributeName VARCHAR(30) Not null   Short attribute name
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
QuestionId BIGINT Not null FK References OPS_Question table

Indices

Index Columns
QuestionAttribut1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK20 QuestionId OPS_Question.QuestionId

OPS_QuestionTagField

One record defines one essay field.

Columns

Column Data type Allow NULLs Key Notes
QuestionTagFieldId BIGINT Not null PK Unique id
TagFieldType INTEGER Not null   Essay field type
  • 1 - dropdown list
  • 2 - checkbox
  • 3 - text
  • 4 - numeric INTEGER
  • 5 - numeric decimal
TagId VARCHAR(255) Not null   Identification of the essay field inside one question.
FieldSize INTEGER Null   Size of the essay field. Applicable for text and numeric fields.
DropdownId BIGINT Null FK References OPS_Dropdown. Applicable for dropdown field type.
QuestionId BIGINT Not null FK References OPS_Question table

Indices

Index Columns
QuestionTagField1 QuestionId
QuestionTagField2 TagId, QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK32 DropdownId OPS_Dropdown.DropdownId
OPS_FK33 QuestionId OPS_Question.QuestionId

OPS_QuestionRating

Defines a rating/scale question.

Columns

Column Data type Allow NULLs Key Notes
QuestionRatingId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table.
MinText VARCHAR(100) Null   Label for minimum rating
MaxText VARCHAR(100) Null   Label for maximum rating
RatingLevel INTEGER Null   Number of levels in a rating question
ShowNA VARCHAR(5) Null   True if N/A option will is on. Values:
  • true
  • false
NALabel VARCHAR(50) Null   Label for N/A option

Indices

Index Columns
QuestionRating_Q1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK11 OPS_BranchRating.QuestionRatingId QuestionRatingId
OPS_FK13 OPS_BranchRatingOption.QuestionRatingId QuestionRatingId
OPS_FK31 QuestionId OPS_Question.QuestionId

OPS_QuestionMultiple

Defines the type of a multiple-choice question.

Columns

Column Data type Allow NULLs Key Notes
QuestionMultipleId BIGINT Not null PK Unique id
MultipleOn VARCHAR(5) Not null   Flag determining the multiple choice type:
  • true - single selection
  • false - multiple selection
ColumnCount INTEGER Not null   Number of display columns (to ease display if number of choices are numerous)
QuestionId BIGINT Not null FK References OPS_Question table

Indices

Index Columns
QuestionMultiple2 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK7 OPS_BranchMultiple.QuestionMultipleId QuestionMultipleId
OPS_FK9 OPS_BranchMultipleOption.QuestionMultipleId QuestionMultipleId
OPS_FK28 QuestionId OPS_Question.QuestionId
OPS_FK29 OPS_QuestionMultipleOption.QuestionMultipleId QuestionMultipleId

OPS_QuestionMultipleOption

One option in a multiple-choice question.

Columns

Column Data type Allow NULLs Key Notes
OptionText VARCHAR(255) Null   The text of the multiple choice option
OptionIndex INTEGER Not null   Position of the option. 0 is the first multiple choice option, 1 is the second, etc.
Image VARCHAR(255) Null   Defines an image in a multiple choice option
Layout INTEGER Null   Layout for the option
  • 0 - default layout (text to the right of the option)
  • 1 - option to the left of the text
  • 2 - option to the right of the text
  • 3 - option over the text
  • 4 - option under the text
ImageLayout INTEGER Null   Image layout
  • 0 - default layout (text to the right of the option)
  • 1 - image to the left of the text
  • 2 - image to the right of the text
  • 3 - image over the text
  • 4 - image under the text
QuestionMultipleId BIGINT Not null FK References OPS_QuestionMultiple table

Indices

Index Columns
QuestionMultiple1 QuestionMultipleId
QuestionMultiple3 OptionIndex, QuestionMultipleId

Foreign keys

Foreign key Child Parent
OPS_FK29 QuestionMultipleId OPS_QuestionMultiple.QuestionMultipleId

OPS_QuestionNumeric

Defines a numeric question.

Columns

Column Data type Allow NULLs Key Notes
QuestionNumericId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table
NumericType INTEGER Null   Defines type of number
  • 1 - INTEGER
  • 2 - decimal
PrefixLabel VARCHAR(255) Null   Label before the field
PostfixLabel VARCHAR(255) Null   Label after the field
FieldSize INTEGER Null   Size of the field

Indices

Index Columns
QuestionNumeric_1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK10 OPS_BranchNumeric.QuestionNumericId QuestionNumericId
OPS_FK30 QuestionId OPS_Question.QuestionId

OPS_QuestionMatrix

Defines matrix question type.

Columns

Column Data type Allow NULLs Key Notes
QuestionMatrixId BIGINT Not null PK Unique id
Column_Count INTEGER Not null   Number of columns in the matrix
Row_Count INTEGER Not null   Number for rows in the matrix
QuestionId BIGINT Not null FK References OPS_Question table

Indices

Index Columns
QuestionMatrix_Q1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK4 OPS_BranchMatrix.QuestionMatrixId QuestionMatrixId
OPS_FK6 OPS_BranchMatrixOption.QuestionMatrixId QuestionMatrixId
OPS_FK24 QuestionId OPS_Question.QuestionId
OPS_FK26 OPS_QuestionMatrixCell.QuestionMatrixId QuestionMatrixId
OPS_FK27 OPS_QuestionMatrixGroup.QuestionMatrixId QuestionMatrixId

OPS_QuestionMatrixGroup

One record represents one matrix group (a group of matrix cells).

Columns

Column Data type Allow NULLs Key Notes
QuestionMatrixGroupId BIGINT Not null PK Unique id
GroupName VARCHAR Null   Name of the cell group
Heading VARCHAR(255) Null   Group heading
GroupType INTEGER Null   Group type tells what kind of cells the group contains
  • 1 - rating type, contains cells of radio type, one cell per row can be selected
  • 2 - ranking type, contains cells of checkbox type, one cell per column x row can be selected
  • 3 - intersection type, contains cells of radio type, only one cell can be selected
  • 4 - any cell type, contains cells of checkbox type, any cell can be selected
  • 5 - custom type, contains cells of different types
BackgroundColour VARCHAR(20) Null   Background colour for this cell group
FromColumn INTEGER Not null   Start column for the cell group
ToColumn INTEGER Not null   End column for the cell group
FromRow INTEGER Not null   Start row for the cell group
ToRow INTEGER Not null   End row for the cell group
QuestionMatrixId BIGINT Not null FK References OPS_QuestionMatrix table

Indices

Index Columns
QuestionMatrixGr1 QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK27 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId

OPS_QuestionMatrixCell

One record represents one cell in a matrix.

Columns

Column Data type Allow NULLs Key Notes
QuestionMatrixCellId BIGINT Not null PK Unique id
MatrixCellType INTEGER Not null   Defines type of the matrix cell.
  • 1 - label
  • 2 - dropdown list
  • 3 - checkbox
  • 4 - radio button
  • 5 - text
  • 6 - numeric INTEGER
  • 7 - numeric decimal
  • 8 - image
ColumnPosition INTEGER Not null   Column position of the cell
RowPosition INTEGER Not null   Row position of the cell
Label VARCHAR(255) Null   Label for a label cell
DropdownId BIGINT Null FK References OPS_Dropdown.
FieldSize INTEGER Null   Field size for text and numeric cell types
BackgroundColour VARCHAR(20) Null   Background colour for this cell
TextColour VARCHAR(20) Null   Text colour for a label cell
CellName VARCHAR(255) Null   Optional cell name, used in reports
QuestionMatrixId BIGINT Not null FK References OPS_QuestionMatrix table

Indices

Index Columns
QuestionMatrixCe1 QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK25 DropdownId OPS_Dropdown.DropdownId
OPS_FK26 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId

OPS_QuestionFreeText

Free-text input for one question.

Columns

Column Data type Allow NULLs Key Notes
QuestionFreeTextId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table
Row_Count INTEGER Null   Number of rows for the input text box
Column_Count INTEGER Null   Number of columns for the input text box
Required VARCHAR(5) Null   True if required input (not in use)
Label VARCHAR(255) Null   Text box label
MaxLength INTEGER Null   Maximum length of user input (not in use)

Indices

Index Columns
QuestionFreeText1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK23 QuestionId OPS_Question.QuestionId

OPS_QuestionDropdown

Defines a dropdown question.

Columns

Column Data type Allow NULLs Key Notes
QuestionDropdownId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table
DropdownId BIGINT Not null FK References OPS_Dropdown table. Id of the dropdown to use in this question.
DropdownMultipleOn VARCHAR(5) Null   Multiple selection on/off. Values:
  • true
  • false
DropdownSize INTEGER Null   The size of the dropdown box

Indices

Index Columns
QuestionDropdown1 QuestionId
QuestionDropdown2 QuestionId, DropdownId

Foreign keys

Foreign key Child Parent
OPS_FK1 OPS_BranchDropdown.QuestionDropdownId QuestionDropdownId
OPS_FK3 OPS_BranchDropdownOption.QuestionDropdownId QuestionDropdownId
OPS_FK21 DropdownId OPS_Dropdown.DropdownId
OPS_FK22 QuestionId OPS_Question.QuestionId

OPS_QuestionComment

Stores question comments

Columns

Column Data type Allow NULLs Key Notes
QuestionCommentId BIGINT Not null PK Unique id
QuestionId BIGINT Not null   References OPS_Question.QuestionId
CommentText TEXT Null   The question comment
CommentBy VARCHAR(50) Null   Who added the comment
EntryDate BIGINT Null   When the comment was added

Foreign keys

Foreign key Child Parent
OPS_FK96 OPS_Question.QuestionId QuestionId

OPS_Dropdown

One record represents one dropdown. Dropdowns stored in this table are dropdowns for dropdown question, essay field, matrix cell and reusable resources.

Columns

Column Data type Allow NULLs Key Notes
DropdownId BIGINT Not null PK Unique id
DropdownName VARCHAR(255) Null   Name of dropdown list (applicable for reusable dropdowns)
DropdownLabel VARCHAR(255) Null   Dropdown label. This will be the default display of the dropdown, but the dropdown will have no value if this is selected.
MultipleOn VARCHAR(5) Null   Multiple selection. Values:
  • true
  • false
DropdownSize INTEGER Null   Size of the dropdown box
SortOn VARCHAR(5) Null   Sorting of dropdown items. Values:
  • true
  • false
QuestionId VARCHAR(5) Null   If the dropdown belongs to a question, QuestionId is set. If the dropdown is in the reusable resources (dropdown bank), the QuestionId is NULL or 0.
DropdownType int Null   Type of the dropdown:
  • 0 - dropdown belongs to reusable resources (dropdown bank)
  • 1 - dropdown belongs to a question essay field
  • 2 - dropdown belongs to a matrix cell
  • 3 - dropdown belongs to a question og type dropdown

Indices

Index Columns
Dropdown_Questio1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK14 OPS_DropdownItem.DropdownId DropdownId
OPS_FK21 OPS_QuestionDropdown.DropdownId DropdownId
OPS_FK25 OPS_QuestionMatrixCell.DropdownId DropdownId
OPS_FK32 OPS_QuestionTagField.DropdownId DropdownId

OPS_DropdownItem

One entry in a dropdown list.

Columns

Column Data type Allow NULLs Key Notes
ItemName VARCHAR(255) Not null   Name of the dropdown list entry (display label)
ItemValue VARCHAR(255) Null   Value of the dropdown list entry
SortValue INTEGER Null   Contains sequential numbers used for sorting. When selecting, items are sorted by SortValue, then ItemName
DropdownId BIGINT Not null FK References OPS_Dropdown table

Indices

Index Columns
DropdownItem_dro1 DropdownId

Foreign keys

Foreign key Child Parent
OPS_FK14 DropdownId OPS_Dropdown.DropdownId

OPS_Validator

Validator for a question. A validator can validate a simple text or numeric field, or a whole question.

Columns

Column Data type Allow NULLs Key Notes
ValidatorId BIGINT Not null PK Unique id
ValidatorType INTEGER Not null   Validator type defines what kind of validation will be done. Varies for different kinds of target types:

  • Target: numeric field

    • 1 - Numeric response is a number
    • 2 - Essay response is a number
    • 3 - Matrix cell response is a number
    • 4 - Tag field response is a number
    • 5 - Numeric response required
    • 6 - Essay response required
    • 7 - Matrix cell response required
    • 8 - Tag field response required
    • 9 - Maximum value for numeric response
    • 10 - Maximum value for an essay response
    • 11 - Maximum value for a matrix cell response
    • 12 - Maximum value for a tag field response
    • 13 - Minimum value for numeric response
    • 14 - Minimum value for an essay response
    • 15 - Minimum value for a matrix cell response
    • 16 - Minimum value for a tag field response
    • 17 - Valid range for numeric response
    • 18 - Valid range for an essay response
    • 19 - Valid range for a matrix cell response
    • 20 - Valid range for a tag field response

  • Target: text field

    • 1 - Free text required
    • 2 - Essay response required
    • 3 - Tag field response required
    • 4 - Matrix cell response required
    • 5 - Maximum text length for free text
    • 6 - Maximum text length for an essay field
    • 7 - Maximum text length for tag field
    • 8 - Maximum text length for matrix cell
    • 9 - Minimum text length for free text
    • 10 - Minimum text length for an essay field
    • 12 - Minimum text length for tag field
    • 13 - Minimum text length for matrix cell
    • 14 - Email Validator type for free text
    • 15 - Email Validator type for an essay field
    • 16 - Email Validator type for tag field
    • 17 - Email Validator type for matrix cell
    • 18 - Date type for free text
    • 19 - Date type for an essay field
    • 20 - Date type for tag field
    • 21 - Date type for matrix cell
    • 22 - Contains a word/text for free text
    • 23 - Contains a word/text for an essay field
    • 24 - Contains a word/text for tag field
    • 25 - Contains a word/text for matrix cell
    • 26 - Regular expressions for free text
    • 27 - Regular expressions for an essay field
    • 28 - Regular expressions for tag field
    • 29 - Regular expressions for matrix cell

  • Target: multiple-choice question

    • 1 - Response required
    • 2 - Minimum number of choices
    • 3 - Maximum number of choices
    • 4 - Number of choices

  • Target: rating question

    • 1 - Rating response required

  • Target: matrix question

    • 1 - Group response required
    • 2 - Group response ranking required
    • 3 - Response ranking, not required
    • 4 - Minimum number of choices for group
    • 5 - Maximum number of choices for group
    • 6 - Required number of choices for group
    • 7 - Minimum number of choices for group row
    • 8 - Maximum number of choices for group row
    • 9 - Required number of choices for group row
    • 10 - Minimum number of choices for group column
    • 12 - Maximum number of choices for group column
    • 13 - Required number of choices for group column
    • 14 - Sum of cells equals
    • 15 - Sum of cells is less then
    • 16 - Sum of cells is greater then

  • Target: essay question

    • 1 - Sum of field inputs equals
    • 2 - Maximum value for the sum of fields
    • 3 - Minimum value for the sum of fields

  • Target: dropdown

    • 1 - Dropdown response required
    • 2 - Minimum number of choices
    • 3 - Maximum number of choices
    • 4 - Minimum number of choices
    • 5 - Essay field response required
    • 6 - Matrix cell response required
    • 7 - Multiple response required
TargetType INTEGER Not null   Target type defines which field / question type this validator will validate.
  • 1 - numeric question/field
  • 2 - text field
  • 3 - multiple question
  • 4 - rating question
  • 5 - matrix question
  • 6 - essay field
  • 7 - dropdown question/field
TargetIndex1 INTEGER Null   Index 1 for target. Can be index for multiple option in multiple question, or index for essay field in essay, or column for the first cell to validate in matrix question.
TargetIndex2 INTEGER Null   Index 2 for target. Row for the first cell to validate (in matrix question).
TargetIndex3 INTEGER Null   Index 3 for target. Column for the last cell to validate (in matrix question)
TargetIndex4 INTEGER Null   Index 3 for target. Row for the last cell to validate (in matrix question)
LongTargetIndex BIGINT Null   Long value for target. Used most for matrix group id.
LongValue1 BIGINT Null   The comparing long value 1. Example: maximum value for numeric, or maximum text length, start value for valid range.
LongValue2 BIGINT Null   The comparing long value 2. Example: end value for valid range.
TextValue VARCHAR(255) Null   The comparing text value. Example: a word that response value must contain.
BigTextValue TEXT Null   The comparing big text value. Like TextValue.
DoubleValue1 float Null   The comparing double value 1. Example: maximum value for numeric, or start value for valid range
DoubleValue2 float Null   The comparing double value 2. Example: End value for valid range.
Description VARCHAR(255) Null   Validator description
ErrorMessage VARCHAR(255) Null   Error message to show to the user if validation fails.
TargetId VARCHAR(255) Null   Text target id. Used for in-text elements.
QuestionId BIGINT Not null FK References OPS_Question table

Indices

Index Columns
Validator1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK77 QuestionId OPS_Question.QuestionId
OPS_FK92 ValidatorId OPS_ValidatorAttribute.ValidatorId

OPS_ValidatorAttribute

One record represents one validator attribute.

Columns

Column Data type Allow NULLs Key Notes
ValidatorAttributeId BIGINT Not null PK Unique id
AttributeKey VARCHAR(255) Not null   Validator attribute key
AttributeValue VARCHAR(255) Null   String value of the attribute
ValidatorId BIGINT Not null FK References OPS_Validator table

Indices

Index Columns
ValidatorAttribute1 ValidatorId

Foreign keys

Foreign key Child Parent
OPS_FK92 ValidatorId OPS_Validator.ValidatorId

OPS_BranchRating

One record defines the branching information for a condition of type rating/scale. Rating branch allows to filter out some questions after response to a rating question.

Columns

Column Data type Allow NULLs Key Notes
BranchRatingId BIGINT Not null PK Unique id
BranchType INTEGER Null   Branch types:
  • 0 - type 'one', respondent must select one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
BranchTitle VARCHAR(255) Null   Name of the branch condition.
FilterArray VARCHAR(255) Not null   Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5.
QuestionRatingId BIGINT Not null FK References OPS_QuestionRating table

Indices

Index Columns
BranchRating_Que1 QuestionRatingId

Foreign keys

Foreign key Child Parent
OPS_FK11 QuestionRatingId OPS_QuestionRating.QuestionRatingId
OPS_FK12 OPS_BranchRatingOption.BranchRatingId BranchRatingId

OPS_BranchRatingOption

One record represents one option for a rating/scale branching condition. One or many options make up a complete rating condition, connected to the OPS_BranchRating table.

Columns

Column Data type Allow NULLs Key Notes
Value INTEGER Null   Rating option position. Starts at 0.
BranchRatingId BIGINT Not null FK References OPS_BranchRating table
QuestionRatingId BIGINT Not null FK References OPS_QuestionRating table

Indices

Index Columns
BranchRatingOpti1 BranchRatingId
BranchRatingOpti2 QuestionRatingId
BranchRatingOpti3 Value, BranchRatingId, QuestionRatingId

Foreign keys

Foreign key Child Parent
OPS_FK12 BranchRatingId OPS_BranchRating.BranchRatingId
OPS_FK13 QuestionRatingId OPS_QuestionRating.QuestionRatingId

OPS_BranchMultiple

One record represents branching condition of type multiple choice. Multiple branch allows to filter out some questions based on the response to a multiple-choice question.

Columns

Column Data type Allow NULLs Key Notes
BranchMultipleId BIGINT Not null PK Unique id
BranchType INTEGER Null   Branch types:
  • 0 - type 'at least one', respondent must select at least one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
  • 2 - type 'all', respondent must select all values in the condition
BranchTitle VARCHAR(255) Null   Name of the branch condition.
FilterArray VARCHAR(255) Not null   Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5.
QuestionMultipleId BIGINT Not null FK References OPS_QuestionMultiple

Indices

Index Columns
BranchMultiple_Q1 QuestionMultipleId

Foreign keys

Foreign key Child Parent
OPS_FK7 QuestionMultipleId OPS_QuestionMultiple.QuestionMultipleId
OPS_FK8 OPS_BranchMultipleOption.BranchMultipleId BranchMultipleId

OPS_BranchMultipleOption

One record represents option of a branch condition of type multiple choice. One or many options make up a complete multiple condition, connected to the OPS_BranchMultiple table.

Columns

Column Data type Allow NULLs Key Notes
Value INTEGER Null   Position of the multiple option. Starts at 0.
BranchMultipleId BIGINT Not null FK References OPS_BranchMultiple table
QuestionMultipleId BIGINT Not null FK References OPS_QuestionMultiple table

Indices

Index Columns
BranchMultipleOp1 BranchMultipleId
BranchMultipleOp2 QuestionMultipleId
BranchMultipleOp3 Value, BranchMultipleId, QuestionMultipleId

Foreign keys

Foreign key Child Parent
OPS_FK8 BranchMultipleId OPS_BranchMultiple.BranchMultipleId
OPS_FK9 QuestionMultipleId OPS_QuestionMultiple.QuestionMultipleId

OPS_BranchNumeric

One question represents a branching condition of type numeric. Numeric branch allows to filter out some questions if response to a numeric question is greater than minLongValue(minDoubleValue) or/and less then maxLongValue(maxDoubleValue) inclusive.

Columns

Column Data type Allow NULLs Key Notes
BranchNumericId BIGINT Not null PK Unique id
BranchType INTEGER Null   Branch types:
  • 0 - type 'max', condition evaluates to true if response value is less or equals the maximum value.
  • 1 - type 'min', condition evaluates to true if response value is greater or equals the minimum value
  • 2 - type 'range', condition evaluates to true if response value is in the specified range, inclusive bounds.
BranchTitle VARCHAR(255) Null   Name of the branch condition.
FilterArray VARCHAR(255) Not null   Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5.
MinLongValue BIGINT Null   Minimum INTEGER value for the numeric condition
MaxLongValue BIGINT Null   Maximum INTEGER value for the numeric condition
MinDoubleValue DOUBLE Null   Minimum double value for the numeric condition
MaxDoubleValue DOUBLE Null   Maximum double value for the numeric condition
QuestionNumericId BIGINT Not null FK References OPS_QustionNumeric table

Indices

Index Columns
BranchNumeric_Qu1 QuestionNumericId

Foreign keys

Foreign key Child Parent
OPS_FK10 QuestionNumericId OPS_QuestionNumeric.QuestionNumericId

OPS_BranchDropdown

One record represents branching condition of type dropdown. Dropdown branch allows to filter out some questions based on response to a dropdown question

Columns

Column Data type Allow NULLs Key Notes
BranchDropdownId BIGINT Not null PK Unique id
BranchType INTEGER Null   Branch types:
  • 0 - type 'at least one', respondent must select at least one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
  • 2 - type 'all', respondent must select all values in the condition
BranchTitle VARCHAR(255) Null   Name of the branch condition.
FilterArray VARCHAR(255) Not null   Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5.
QuestionDropdownId BIGINT Not null FK References OPS_QuestionDropdown table

Indices

Index Columns
BranchDropdown_Q1 QuestionDropdownId

Foreign keys

Foreign key Child Parent
OPS_FK1 QuestionDropdownId OPS_QuestionDropdown.QuestionDropdownId
OPS_FK2 OPS_BranchDropdownOption.BranchDropdownId BranchDropdownId

OPS_BranchDropdownOption

One record represents option (item) of a branch condition of type dropdown. One or many options make up a complete dropdown condition, connected to the OPS_BranchDropdown table.

Columns

Column Data type Allow NULLs Key Notes
TextValue VARCHAR(255) Null   The condition value (dropdown item value)
BranchDropdownId BIGINT Not null FK References OPS_BranchDropdown table
QuestionDropdownId BIGINT Not null FK References OPS_QuestionDropdown table

Indices

Index Columns
BranchDropdownOp1 BranchDropdownId
BranchDropdownOp2 QuestionDropdownId

Foreign keys

Foreign key Child Parent
OPS_FK2 BranchDropdownId OPS_BranchDropdown.BranchDropdownId
OPS_FK3 QuestionDropdownId OPS_QuestionDropdown.QuestionDropdownId

OPS_BranchMatrix

One record represents branching condition of matrix type. Matrix branch allows to filter out some questions based on the response to a matrix question (branching on radio buttons and checkboxes only).

Columns

Column Data type Allow NULLs Key Notes
BranchMatrixId BIGINT Not null PK Unique id
BranchType INTEGER Null   Branch types:
  • 0 - type 'at least one', respondent must select at least one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
  • 2 - type 'all', respondent must select all values in the condition
BranchTitle VARCHAR(255) Null   Name of the branch condition.
FilterArray VARCHAR(255) Not null   Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5.
QuestionMatrixId BIGINT Not null FK References OPS_QuestionMatrix table

Indices

Index Columns
BranchMatrix_Que1 QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK4 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId
OPS_FK5 OPS_BranchMatrixOption.BranchMatrixId BranchMatrixId

OPS_BranchMatrixOption

One option in a matrix condition. One or many options make up a complete matrix condition, connected to the OPS_BranchMatrix table.

Columns

Column Data type Allow NULLs Key Notes
ColumnValue INTEGER Null   The column position of the option.
RowValue INTEGER Null   The row position of the option.
BranchMatrixId BIGINT Not null FK References OPS_BranchMatrix table
QuestionMatrixId BIGINT Not null FK References OPS_QuestionMatrix table

Indices

Index Columns
BranchMatrixOpti1 QuestionMatrixId
BranchMatrixOpti2 BranchMatrixId
BranchMatrixOpti2 ColumnValue, RowValue, BranchMatrixId, QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK5 BranchMatrixId OPS_BranchMatrix.BranchMatrixId
OPS_FK6 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId

OPS_Invitation

Defines survey invitation.

Columns

Column Data type Allow NULLs Key Notes
InvitationId BIGINT Not null PK Unique id
InvitationName VARCHAR(255) Not null   Invitation name
FromName VARCHAR(50) Not null   Name of invitation author
FromEmail VARCHAR(100) Not null   Email of invitation author
InvitationDate BIGINT Not null   Date when the invitation will be sent, in milliseconds
Reminder1Date BIGINT Null   Date when the first reminder should be sent, in milliseconds
Reminder2Date BIGINT Null   Date when the second reminder should be sent, in milliseconds
Reminder3Date BIGINT Null   Date when the third reminder should be sent, in milliseconds
Reminder4Date BIGINT Null   Date when the fourth reminder should be sent, in milliseconds
Reminder5Date BIGINT Null   Date when the fifth reminder should be sent, in milliseconds
ReminderInterval INTEGER Null   DEPRECATED. Interval between each reminder, in milliseconds
ReminderTime BIGINT Null   DEPRECATED
ReminderCount INTEGER Not null   DEPRECATED. The number of times to send reminder to the invitees who have not responded.
ContentType VARCHAR(20) Not null   Email content type. Values:
  • text/html
  • text/plain
InvitationSubject VARCHAR(255) Not null   The subject in the email with invitation.
InvitationMessage TEXT Not null   The content of the email with invitation.
ReminderSubject VARCHAR(255) Null   The subject in the email with reminder.
ReminderMessage TEXT Null   The content in the email with reminder.
LastReminderSubject VARCHAR(255) Null   The subject in the email with last reminder.
LastReminderMessage TEXT Null   The content in the email with last reminder.
SurveyId BIGINT Not null FK References OPS_Survey table
TaskId BIGINT Not null   Id of the scheduled task
InvitationSentDate BIGINT Null   Time in milliseconds when invitation was actually sent
Reminder1SentDate BIGINT Null   Time in milliseconds when the first reminder was actually sent
Reminder2SentDate BIGINT Null   Time in milliseconds when the second reminder was actually sent
Reminder3SentDate BIGINT Null   Time in milliseconds when the third reminder was actually sent
Reminder4SentDate BIGINT Null   Time in milliseconds when the fourth reminder was actually sent
Reminder5SentDate BIGINT Null   Time in milliseconds when the fifth reminder was actually sent

Indices

Index Columns
Invitation_Surve1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK16 SurveyId OPS_Survey.SurveyId
OPS_FK18 OPS_Invitee.InvitationId InvitationId

OPS_Invitee

One record for one invitee.

Columns

Column Data type Allow NULLs Key Notes
InviteeId BIGINT Not null PK Unique id
InviteeName VARCHAR(100) Null   Name of the invitee
InviteeEmail VARCHAR(100) Null   Email of the invitee
SentDate BIGINT Null   Date when the invitation to this invitee was sent, in milliseconds
IdKey VARCHAR(100) Null   Key to identify the invitee
ReminderCount INTEGER Null   Number of reminders sent
Status INTEGER Null   Status of the invitee:
  • 0 - invitation is pending
  • 1 - the invitation has been sent
  • 2 - reminder has been sent
  • 3 - error occurred while sending invitation
  • 4 - error occurred while sending reminder
  • 5 - email address is invalid
RespondentId BIGINT Null   Id of the respondent. Exists when the invitee has responded to the survey. Equals -1 if responded, but the invitation is anonym.
InvitationId BIGINT Not null FK References OPS_Invitation table
AttributesExist VARCHAR(5) Null   True if invitee attributes exist in the OPS_InviteeAttribute table. Values:
  • true
  • false

Indices

Index Columns
Invitee_Responde1 RespondentId
Invitee_Invitati1 InvitationId

Foreign keys

Foreign key Child Parent
OPS_FK18 InvitationId OPS_Invitation.InvitationId
OPS_FK97 InviteeId OPS_InviteeAttribute.InviteeId

OPS_InviteeAttribute

One record represents one invitee attribute.

Columns

Column Data type Allow NULLs Key Notes
InviteeAttributeId BIGINT Not null PK Unique id
AttributeName VARCHAR(30) Not null   Short attribute name
StringValue VARCHAR(255) Null   String value of the attribute
InviteeId BIGINT Not null FK References OPS_Invitee table
InvitationId BIGINT Not null FK References OPS_Invitation table

Indices

Index Columns
InviteeAttribute_1 InviteeId

Foreign keys

Foreign key Child Parent
OPS_FK97 InviteeId OPS_Invitee.InviteeId

OPS_Respondent

Each time a new respondent responds to a survey, one record is created.

Columns

Column Data type Allow NULLs Key Notes
RespondentId BIGINT Not null PK Unique id
SurveyId BIGINT Not null FK References OPS_Survey table
IPAddress VARCHAR(50) Null   Respondent's IP address
Ticket VARCHAR(20) Null   Ticket to the survey
EntryDate BIGINT Null   Timestamp of the first response
CompletedDate BIGINT Null   Timestamp of the last response
LastResponse INTEGER Null   Number of the last question responded
DeleteStatus INTEGER Null   (not in use)
LastUpdated INTEGER Null   Timestamp for last updated
AttributesExist VARCHAR(5) Null   True if respondent attributes exist in the OPS_RespondentAttribute table. Values:
  • true
  • false
LanguageCode VARCHAR(10) Null   Language code for the last selected survey language.

Indices

Index Columns
Respondent1 SurveyId, RespondentId, EntryDate, CompletedDate
Respondent2 CompletedDate, SurveyId
Respondent_Surve1 SurveyId, IPAddress, CompletedDate

Foreign keys

Foreign key Child Parent
OPS_FK38 SurveyId OPS_Survey.SurveyId
OPS_FK40 OPS_ResponseDropdown.RespondentId RespondentId
OPS_FK42 OPS_ResponseEssay.RespondentId RespondentId
OPS_FK44 OPS_ResponseEssayNum.RespondentId RespondentId
OPS_FK47 OPS_ResponseEssayText.RespondentId RespondentId
OPS_FK50 OPS_ResponseFreeText.RespondentId RespondentId
OPS_FK52 OPS_ResponseMatrix.RespondentId RespondentId
OPS_FK54 OPS_ResponseMatrixNum.RespondentId RespondentId
OPS_FK57 OPS_ResponseMatrixText.RespondentId RespondentId
OPS_FK60 OPS_ResponseMultipleOption.RespondentId RespondentId
OPS_FK62 OPS_ResponseMultipleOptionNum.RespondentId RespondentId
OPS_FK65 OPS_ResponseMultipleOptionText.RespondentId RespondentId
OPS_FK68 OPS_ResponseNumeric.RespondentId RespondentId
OPS_FK70 OPS_ResponseRating.RespondentId RespondentId
OPS_FK85 OPS_ReportRespondent.RespondentId RespondentId
OPS_FK93 OPS_RespondentAttribute.RespondentId RespondentId

OPS_RespondentAttribute

One record represents one respondent attribute.

Columns

Column Data type Allow NULLs Key Notes
RespondentAttributeId BIGINT Not null PK Unique id
AttributeName VARCHAR(30) Not null   Short attribute name
StringValue VARCHAR(255) Null   String value of the attribute
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
RespondentAttribute_1 RespondentId

Foreign keys

Foreign key Child Parent
OPS_FK93 RespondentId OPS_Respondent.RespondentId

OPS_ResponseFreeText

Answer to a free text.

Columns

Column Data type Allow NULLs Key Notes
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
TextValue TEXT Null   The free text answer

Indices

Index Columns
ResponseFreeText1 RespondentId
ResponseFreeText4 QuestionId
*ResponseFreeText5 QuestionId, RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK49 QuestionId OPS_Question.QuestionId
OPS_FK50 RespondentId OPS_Respondent.RespondentId

OPS_ResponseNumeric

Answer to a numeric question.

Columns

Column Data type Allow NULLs Key Notes
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
LongValue BIGINT Null   The numeric answer, INTEGER/long
DecValue DOUBLE Null   The numeric answer, decimal

Indices

Index Columns
ResponseNumeric_1 QuestionId
ResponseNumeric_2 RespondentId
*ResponseNumeric_3 QuestionId, RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK67 QuestionId OPS_Question.QuestionId
OPS_FK68 RespondentId OPS_Respondent.RespondentId

OPS_ResponseEssay

Response to an essay field. One record exists for each essay field response.

Columns

Column Data type Allow NULLs Key Notes
ResponseEssayId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
FieldIndex smallint Null   Index of the essay field, starts at 0

Indices

Index Columns
ResponseEssay_Qu1 QuestionId, FieldIndex
ResponseEssay1 RespondentId
*ResponseEssay2 QuestionId, RespondentId, FieldIndex

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK41 QuestionId OPS_Question.QuestionId
OPS_FK42 RespondentId OPS_Respondent.RespondentId
OPS_FK45 OPS_ResponseEssayNum.ResponseEssayId ResponseEssayId
OPS_FK48 OPS_ResponseEssayText.ResponseEssayId ResponseEssayId

OPS_ResponseEssayText

Response to an essay field of type text and dropdown.

Columns

Column Data type Allow NULLs Key Notes
TextValue VARCHAR(255) Null   The text answer or value of the selected dropdown item
ResponseEssayId BIGINT Not null FK References OPS_ResponseEssay table
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
*ResponseEssayTex1 ResponseEssayId
ResponseEssayTex2 QuestionId
ResponseEssayTex3 RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK46 QuestionId OPS_Question.QuestionId
OPS_FK47 RespondentId OPS_Respondent.RespondentId
OPS_FK48 ResponseEssayId OPS_ResponseEssay.ResponseEssayId

OPS_ResponseEssayNum

Response to a numeric field.

Columns

Column Data type Allow NULLs Key Notes
LongValue BIGINT Null   Response of type INTEGER/long
DecValue DOUBLE Null   Response of type decimal
ResponseEssayId BIGINT Not null FK References OPS_ResponseEssay table
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
*ResponseEssayNum1 ResponseEssayId
ResponseEssayNum2 QuestionId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK43 QuestionId OPS_Question.QuestionId
OPS_FK44 RespondentId OPS_Respondent.RespondentId
OPS_FK45 ResponseEssayId OPS_ResponseEssay.ResponseEssayId

OPS_ResponseRating

Response to a rating question.

Columns

Column Data type Allow NULLs Key Notes
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
Value INTEGER Null   The selected option position. Starts at 1, 0 for N/A option.

Indices

Index Columns
*ResponseRating1 QuestionId, RespondentId
ResponseRating_Q1 QuestionId
ResponseRating_R1 RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK69 QuestionId OPS_Question.QuestionId
OPS_FK70 RespondentId OPS_Respondent.RespondentId

OPS_ResponseDropdown

Response to a dropdown question. Multiple records exist for one dropdown question if multiple selection is on.

Columns

Column Data type Allow NULLs Key Notes
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
TextValue VARCHAR(100) Not null   Value of the selected dropdown item

Indices

Index Columns
ResponseDropdown1 QuestionId, RespondentId
ResponseDropdown2 QuestionId, TextValue

Foreign keys

Foreign key Child Parent
OPS_FK39 QuestionId OPS_Question.QuestionId
OPS_FK40 RespondentId OPS_Respondent.RespondentId

OPS_ResponseMultipleOption

Response to a multiple-choice question. Multiple records exist for one multiple-choice question if multiple selection is on.

Columns

Column Data type Allow NULLs Key Notes
ResponseMultipleOptionId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
OptionIndex smallint Null   The index of the selected multiple option. Starts at 0.

Indices

Index Columns
ResponseMultiple2 QuestionId, OptionIndex
ResponseMultiple5 RespondentId, QuestionId
*ResponseMultiple7 QuestionId, RespondentId, OptionIndex

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK59 QuestionId OPS_Question.QuestionId
OPS_FK60 RespondentId OPS_Respondent.RespondentId
OPS_FK63 OPS_ResponseMultipleOptionNum.ResponseMultipleOptionId ResponseMultipleOptionId
OPS_FK66 OPS_ResponseMultipleOptionText.ResponseMultipleOptionId ResponseMultipleOptionId

OPS_ResponseMultipleOptionText

Response to a text field (or dropdown list) for a selected option. Currently used to store response to 'Other' field.

Columns

Column Data type Allow NULLs Key Notes
TextValue VARCHAR(255) Null   Text response to a field or value of the selected dropdown item
ResponseMultipleOptionId BIGINT Not null FK References OPS_ResponseMultipleOption table
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
*ResponseMultiple1 ResponseMultipleOptionId
ResponseMultiple3 QuestionId
ResponseMultiple4 RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK64 QuestionId OPS_Question.QuestionId
OPS_FK65 RespondentId OPS_Respondent.RespondentId
OPS_FK66 ResponseMultipleOptionId OPS_ResponseMultipleOption.ResponseMultipleOptionId

OPS_ResponseMultipleOptionNum

Response to an numeric field for a selected option. Not in use.

Columns

Column Data type Allow NULLs Key Notes
LongValue BIGINT Null   Response to a numeric field in multiple question, type INTEGER/long
DecValue DOUBLE Null   Response to a numeric field in multiple question, type decimal
ResponseMultipleOptionId BIGINT Not null FK References OPS_ResponseMultipleOption table
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
*ResponseMultiple6 ResponseMultipleOptionId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK61 QuestionId OPS_Question.QuestionId
OPS_FK62 RespondentId OPS_Respondent.RespondentId
OPS_FK63 ResponseMultipleOptionId OPS_ResponseMultipleOption.ResponseMultipleOptionId

OPS_ResponseMatrix

Response to a matrix question, one record for each cell response.

Columns

Column Data type Allow NULLs Key Notes
ResponseMatrixId BIGINT Not null PK Unique id
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table
RowPosition INTEGER Not null   Row position of the cell
ColumnPosition INTEGER Not null   Column position of the cell

Indices

Index Columns
*ResponseMatrix1 QuestionId, RespondentId, ColumnPosition, RowPosition
ResponseMatrix2 RespondentId, QuestionId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK51 QuestionId OPS_Question.QuestionId
OPS_FK52 RespondentId OPS_Respondent.RespondentId
OPS_FK55 OPS_ResponseMatrixNum.ResponseMatrixId ResponseMatrixId
OPS_FK58 OPS_ResponseMatrixText.ResponseMatrixId ResponseMatrixId

OPS_ResponseMatrixText

Response to a matrix cell of type text and dropdown.

Columns

Column Data type Allow NULLs Key Notes
TextValue VARCHAR(255) Null   Text response or value of the selected dropdown item
ResponseMatrixId BIGINT Not null FK References OPS_ResponseMatrix table
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
*ResponseMatrixTe1 ResponseMatrixId
ResponseMatrixTe2 ResponseMatrixId, TextValue
ResponseMatrixTe3 QuestionId
ResponseMatrixTe4 RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK56 QuestionId OPS_Question.QuestionId
OPS_FK57 RespondentId OPS_Respondent.RespondentId
OPS_FK58 ResponseMatrixId OPS_ResponseMatrix.ResponseMatrixId

OPS_ResponseMatrixNum

Response to a matrix cell with numeric field.

Columns

Column Data type Allow NULLs Key Notes
LongValue BIGINT Null   Response of type INTEGER/long
DecValue DOUBLE Null   Response of type decimal
ResponseMatrixId BIGINT Not null FK References OPS_ResponseMatrix table
QuestionId BIGINT Not null FK References OPS_Question table
RespondentId BIGINT Not null FK References OPS_Respondent table

Indices

Index Columns
*ResponseMatrixNu1 ResponseMatrixId
ResponseMatrixNu2 QuestionId
ResponseMatrixNu3 RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK53 QuestionId OPS_Question.QuestionId
OPS_FK54 RespondentId OPS_Respondent.RespondentId
OPS_FK55 ResponseMatrixId OPS_ResponseMatrix.ResponseMatrixId

OPS_HeaderFooter

Stores reusable headers/footers (bank).

Columns

Column Data type Allow NULLs Key Notes
HeaderFooterId BIGINT Not null PK Unique id
HeaderFooterName VARCHAR(20) Null   Header/footer name
HeaderFooterText TEXT Null   Header/footer content
UserId BIGINT Null   Id of the user who created the resource
Type INTEGER Not null   Resource type:
  • 1 - header
  • 2 - footer

OPS_InvitationList

Stores reusable list of invitees.

Columns

Column Data type Allow NULLs Key Notes
InvitationListId BIGINT Not null PK Unique id
InvitationListName VARCHAR(255) Not null   Name of the invitation list
SortOn INTEGER Null   Alphabetic sort value:
  • 0 - off
  • 1 - on

Foreign keys

Foreign key Child Parent
OPS_FK17 OPS_InvitationListItem.InvitationListId InvitationListId

OPS_InvitationListItem

One record for one invitee in reusable invitation list.

Columns

Column Data type Allow NULLs Key Notes
ItemId BIGINT Not Null PK Unique id
Email VARCHAR(100) Not null   Email of invitee
InviteeName VARCHAR(100) Null   Invitee name
SortValue INTEGER Null   Sort value, 0 if alphabetic sort is on.
InvitationListId BIGINT Null FK References OPS_InvitationList table

Indices

Index Columns
InvitationListIt1 InvitationListId

Foreign keys

Foreign key Child Parent
OPS_FK17 InvitationListId OPS_InvitationList.InvitationListId
OPS_FK118 OPS_InvitationListItemAttr.ItemId ItemId

OPS_InvitationListItemAttr

One record for one invitee attribute in the reusable invitee list.

Columns

Column Data type Allow NULLs Key Notes
ItemAttributeId BIGINT Not Null PK Unique id
AttributeName VARCHAR(50) Not null   Attribute name
StringValue VARCHAR(255) Null   Attribute value
ItemId BIGINT Not Null FK References OPS_InvitationListItem table
InvitationListId BIGINT Not Null FK References OPS_InvitationList table

Indices

Index Columns
InvListItemAttr ItemId

Foreign keys

Foreign key Child Parent
OPS_FK118 ItemId OPS_InvitationListItemAttr.ItemId

OPS_Report

Defines survey report.

Columns

Column Data type Allow NULLs Key Notes
ReportId BIGINT Not null PK Unique id
SurveyId BIGINT Not null FK References OPS_Survey
ReportName VARCHAR(100) Null   Report name used in the administration module
Heading VARCHAR(255) Null   Displayed report heading
Description TEXT Null   Description of the report
Header TEXT Null   Report header
Footer TEXT Null   Report footer
Font VARCHAR(100) Null   Report text font family
HeadingFont VARCHAR(100) Null   Report heading font family
FontSize INTEGER Null   Report text font size
HeadingFontSize INTEGER Null   Report heading font size
BgColour VARCHAR(20) Null   Report background colour
FontColour VARCHAR(20) Null   Report text font colour
HeadingFontColour VARCHAR(20) Null   Report heading font colour
Css VARCHAR(100) Null   Report css
RecalculatingPeriode BIGINT Not null   Period between report recalculation in minutes. Used if CacheOn is true.
CacheOn VARCHAR(5) Null   Cache report:
  • true
  • false
AvailableForResp VARCHAR(5) Null   True if respondents can access the report. Values:
  • true
  • false
IncludeIncompl VARCHAR(5) Null   True if the report will include incomplete responses. Values:
  • true
  • false
CreatedBy BIGINT Null   Id of the user who created the report.
CreatedDAte BIGINT Null   Date when the report was created.
LastModifiedDate BIGINT Null   Date when the report was modified last time.
LastFilledDate BIGINT Null   Date when the report was filled last time
ContentType INTEGER Null   Report type:
  • 0 - main report
  • 1 - drilldown report
Status INTEGER Null   Report status
  • 0 - active report, applies both for main and drilldowns
  • 1 - scheduled for deletion. For now applies only to drilldowns

Indices

Index Columns
Report_SurveyId1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK34 SurveyId OPS_Survey.SurveyId
OPS_FK35 OPS_ReportElement.ReportId ReportId
OPS_FK81 OPS_ReportFilter.ReportId ReportId
OPS_FK84 OPS_ReportRespondent.ReportId ReportId
OPS_FK88 OPS_ReportAttribute.ReportId ReportId
OPS_FK90 OPS_ReportElementAttribute.ReportId ReportId
OPS_FK99 OPS_ReportDrilldown.ReportId ReportId
OPS_FK100 OPS_ReportQuestionLabel.ReportId ReportId
OPS_FK101 OPS_ReportQuestionInterval.ReportId ReportId

OPS_ReportAttribute

One record represents one report attribute.

Columns

Column Data type Allow NULLs Key Notes
ReportAttributeId 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
ReportId BIGINT Not null FK References OPS_Report table

Indices

Index Columns
ReportAttr1 ReportId

Foreign keys

Foreign key Child Parent
OPS_FK88 ReportId OPS_Report.ReportId

OPS_ReportElement

Columns

Column Data type Allow NULLs Key Notes
ReportElementId BIGINT Not null PK Unique id
ReportId BIGINT Not null FK References OPS_Report table
SortValue INTEGER Not null   Position of the report element. Starts at 0.
ElementName VARCHAR(255) Not null   Report element name used in the administration module.
Heading VARCHAR(255) Null   Report element heading, displayed in the report.
Description TEXT Null   Report element description
NumericPrecision INTEGER Null   Number of digits after comma in report values.
ReportTypes VARCHAR(255) Null   Report types, separated by comma and enclosed in braces. Example: {1, 2, 10}
  • 1 - summary report
  • 2 - detailed statistics
  • 3 - cross tabulation
  • 4 - free text comments
  • 5 - comments in multiple choice 'other'-field
  • 6 - comments in matrix cells (cells of type text)
  • 7 - comments in essay fields of type text
  • 8 - horizontal bar chart
  • 9 - vertical bar chart
  • 10 - pie chart
ShowNonSelectedItems VARCHAR(5) Null   True is non selected options should be displayed in the report element. True/false

Indices

Index Columns
ReportElement_Re1 ReportId

Foreign keys

Foreign key Child Parent
OPS_FK35 ReportId OPS_Report.ReportId
OPS_FK36 OPS_ReportElementQuestionNo.ReportElementId ReportElementId
OPS_FK89 OPS_ReportElementAttribute.ReportElementId ReportElementId

OPS_ReportElementAttribute

One record represents one report element attribute.

Columns

Column Data type Allow NULLs Key Notes
ReportElementAttributeId 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
ReportElementId BIGINT Not null FK References OPS_ReportElement table
ReportId BIGINT Not null FK References OPS_Report table

Indices

Index Columns
ReportElementAttr1 ReportId
ReportElementAttr2 ReportElementId

Foreign keys

Foreign key Child Parent
OPS_FK89 ReportElementId OPS_ReportElement.ReportElementId
OPS_FK90 ReportId OPS_Report.ReportId

OPS_ReportEl