Semantic Data Model for an Online Survey
Overview
The following semantic data model describes the structure and relationships of the entities required to manage and conduct an online survey. It covers the definition of surveys, questions, answer options, survey responses, and optional personal details of respondents.
- Survey: Holds metadata of a specific survey.
- Question: Defines the individual questions of a survey.
- AnswerOption: Represents predefined answer options per question.
- SurveyResponse: Stores a completed survey.
- ResponseAnswer: Links a survey result with the answers to its questions.
- Respondent: Optional, personal details about a participating person.
- Enumerations: GenderEnum, AgeGroupEnum, QuestionTypeEnum.
erDiagram
Survey ||--o{ Question : "contains"
Question ||--o{ AnswerOption : "has"
SurveyResponse ||--|| Survey : "belongs to"
SurveyResponse ||--|{ ResponseAnswer : "consists of"
ResponseAnswer ||--|| Question : "answers"
ResponseAnswer }o--|| AnswerOption : "selects"
SurveyResponse }o--|| Respondent : "associated with"
Respondent }o--|| GenderEnum : "has"
Respondent }o--|| AgeGroupEnum : "has"
Question }o--|| QuestionTypeEnum : "has"
Entities
Survey
| Attribute | Data Type | PK | FK | Required? | Description | Notes/Constraints |
|---|
| SurveyID | UUID | x | | yes | Unique identifier of the survey. | Generated as UUID. |
| Title | String (max. 200) | | | yes | Title of the survey. | Not empty; trimmed. |
| Description | Text (max. 2000) | | | no | Detailed description and purpose of the survey. | Optional; trimmed. |
| StartDate | Date | | | no | Start date when the survey becomes active. | ISO 8601 (YYYY-MM-DD). |
| EndDate | Date | | | no | End date until which the survey can be answered. | ISO 8601 (YYYY-MM-DD); ≥ StartDate. |
| CreatedBy | String (max. 100) | | | yes | Creator (e.g., name or organization). | Not empty; trimmed. |
| CreatedAt | Date/Time | | | yes | Timestamp of creation. | Automatically set; ISO 8601 (UTC). |
Question
| Attribute | Data Type | PK | FK | Required? | Description | Notes/Constraints |
|---|
| QuestionID | UUID | x | | yes | Unique identifier of the question. | Generated as UUID (UUIDv4). |
| SurveyID | UUID | | x | yes | Foreign key to the survey this question belongs to. | FK; references existing Survey. |
| OrderNumber | Integer | | | yes | Order of the question within the survey. | Integer ≥ 1; unique per Survey. |
| QuestionText | Text (max. 1000) | | | yes | Wording of the question. | Not empty; trimmed. |
| QuestionType | Enum | | | yes | Type of question. Determines whether rating scale, choice, or free text is allowed. | See enum QuestionTypeEnum. |
| IsMandatory | Boolean | | | yes | Indicates whether the question must be answered. | |
AnswerOption
| Attribute | Data Type | PK | FK | Required? | Description | Notes/Constraints |
|---|
| OptionID | UUID | x | | yes | Unique identifier of the answer option. | Generated as UUID (UUIDv4). |
| QuestionID | UUID | | x | yes | Foreign key to the related question. | FK; references existing Question. |
| OptionText | String (max. 200) | | | yes | Text of the answer option (e.g., “Very good”). | Not empty; trimmed. |
| Value | String (max. 100) | | | yes | Value stored internally for this option. | Unique per Question; regex: ^[A-Za-z0-9_-]+$. |
| IsOtherOption | Boolean | | | no | Marks whether this is a free‑text option. | Set to true for “Open” questions. |
SurveyResponse
| Attribute | Data Type | PK | FK | Required? | Description | Notes/Constraints |
|---|
| ResponseID | UUID | x | | yes | Unique identifier of the survey result. | Generated as UUID (UUIDv4). |
| SurveyID | UUID | | x | yes | Foreign key to the survey. | FK; references existing Survey. |
| RespondentID | UUID | | x | no | Reference to the optional respondent. | GDPR‑relevant; if set → ConsentGiven = true. |
| SubmittedAt | Date/Time | | | yes | Submission timestamp. | Automatically set; ISO 8601 (UTC). |
| ConsentGiven | Boolean | | | yes | Indicates whether the respondent consented to processing personal data. | Must be true if personal data present (RespondentID, Email, GenderCode, AgeGroupCode). |
ResponseAnswer
| Attribute | Data Type | PK | FK | Required? | Description | Notes/Constraints |
|---|
| AnswerID | UUID | x | | yes | Unique identifier of the answer. | Generated as UUID (UUIDv4). |
| ResponseID | UUID | | x | yes | Foreign key to the survey result. | FK; references existing SurveyResponse. |
| QuestionID | UUID | | x | yes | Reference to the answered question. | FK; references existing Question. |
| OptionID | UUID | | x | no | Selected answer option (choice questions). | May be set only when QuestionType != Open; must belong to QuestionID. |
| FreeTextResponse | Text (max. 2000) | | | no | Free‑text answer (open questions only). | May be set only when IsOtherOption or QuestionType = Open. |
Respondent
| Attribute | Data Type | PK | FK | Required? | Description | Notes/Constraints |
|---|
| RespondentID | UUID | x | | yes | Unique identifier of the respondent. | Generated as UUID. |
| Email | String (max. 254) | | | no | Respondent’s email address. | GDPR‑relevant; optional; format: email (RFC 5322); regex example: ^[^\s@]+@[^\s@]+.[^\s@]{2,}$. |
| GenderCode | Enum | | x | no | Gender specification. | See enum GenderEnum; optional; default: NotSpecified. |
| AgeGroupCode | Enum | | x | no | Age group of the respondent. | See enum AgeGroupEnum; optional; default: NotSpecified. |
| CreatedAt | Date/Time | | | yes | Creation timestamp of the respondent record. | Automatically set; ISO 8601 (UTC). |
| LastConsentChangeAt | Date/Time | | | no | Timestamp of last consent change. | ISO 8601 (UTC); ≥ CreatedAt. |
Enumerations
| Name | Values | Description |
|---|
| GenderEnum | Male, Female, NonBinary, NotSpecified | Respondent’s gender specification. |
| AgeGroupEnum | Age_0_18, Age_19_35, Age_36_60, Age_61_99, NotSpecified | Respondent’s age group. |
| QuestionTypeEnum | Rating, SingleChoice, MultipleChoice, Open | Type of question. |