-
Notifications
You must be signed in to change notification settings - Fork 4
Data dictionary
Yannick Warnier edited this page Oct 25, 2021
·
6 revisions
This page details the structure of the database and the meaning of each table and field. This project being under constant development, this document is very likely outdated already. Please refer to the document history for more information regarding the last update time.
This table represents the ESCO framework for occupations and skills, structured as needed for our use case.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| concept_type | varchar(255) | NO | NULL | Internal type code for ESCO | |
| concept_uri | varchar(255) | NO | NULL | The URI of this particular item in the ESCO framework | |
| code | varchar(255) | NO | NULL | Numerical code (usually part of the URI) | |
| preferred_label | longtext | NO | NULL | Short title | |
| alt_labels | longtext | YES | NULL | Alternative titles | |
| in_scheme | varchar(255) | YES | NULL | Comma-separated URIs of ESCO schemes | |
| description | longtext | NO | NULL | Long description |
This table collects messages meant for the administrator. They are used to notify the admin of important events on the platform.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| title | varchar(255) | NO | NULL | Subject | |
| is_read | tinyint(1) | NO | 0 | Whether it has been read by the admin or not | |
| created_at | datetime | YES | NULL | Timestamp | |
| read_at | datetime | YES | NULL | Timestamp |
This table describes the occupations (as in "jobs") listed in the ESCO framework and linked to ESCO groups (table isco_group)
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| isco_group_id | int(11) | YES | MUL | NULL | Link to the isco_group table, to place this occupation inside a category in the framework |
| concept_type | varchar(255) | NO | NULL | Always "Occupation" in this table | |
| concept_uri | varchar(255) | NO | NULL | The URL to the full description of the occupation in the online ESCO framework | |
| preferred_label | longtext | NO | NULL | A short description of the occupation | |
| alt_labels | longtext | NO | NULL | Alternative descriptins | |
| hidden_labels | longtext | YES | NULL | Internal ways of calling the occupation (like "dr" or "g.p.") | |
| status | varchar(255) | NO | NULL | Always "released" | |
| modified_at | datetime | YES | NULL | Timestamp | |
| regulated_profession_note | varchar(255) | NO | NULL | Link to the online ESCO framework to specify whether this profession is regulated or unregulated | |
| scope_note | varchar(255) | YES | NULL | Text describing whether this occupation includes or exlucdes some particular profession variation | |
| definition | longtext | YES | NULL | Long text definition of the occupation | |
| in_scheme | longtext | NO | NULL | Link to the online ESCO framework pages about concept schemes | |
| description | longtext | YES | NULL | Long text description of the occupation | |
| code | varchar(255) | NO | NULL | Numerical codes (may include separating dots) |
This table forms the relationship between the occupation and the skill tables.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| occupation_id | int(11) | NO | MUL | NULL | Link to occupation.id |
| skill_id | int(11) | NO | MUL | NULL | Link to skill.id |
| relation_type | varchar(255) | NO | NULL | Whether this skills is "optional" or "essential" for this occupation | |
| skill_type | varchar(255) | NO | NULL | Whether the skill is a "knowledge" or a "skill/competence" for this occupation |
Table holding the translations for occupations.
| name | type | length | default | Purpose |
|---|---|---|---|---|
| id | INT | 11 | Internal ID | |
| occupation_id | INT | 11 | Link to occupation.id | |
| locale | VARCHAR | 10 | ||
| preferred_label | LONGTEXT | |||
| alt_labels | LONGTEXT | NULL | ||
| hidden_labels | LONGTEXT | NULL | ||
| definition | LONGTEXT | |||
| description | LONGTEXT | NULL |
This table defines records of positions/vacancies available and registered and published by a recruiter user.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| user_id | int(10) unsigned | YES | MUL | NULL | Link to user.id |
| creator_id | int(10) unsigned | YES | MUL | NULL | Link to user.id (the creator of this record) |
| occupation_id | int(11) | YES | MUL | NULL | Optional link to occupation.id |
| name | varchar(255) | NO | NULL | The name of the vacancy | |
| location | varchar(255) | YES | NULL | The location, in words | |
| longitude | varchar(255) | YES | NULL | The longitude | |
| latitude | varchar(255) | YES | NULL | The latitude | |
| description | longtext | YES | NULL | A long text description of the vacancy. | |
| salary | double | YES | NULL | The offered salary. | |
| currency | enum('euro','złoty') | YES | NULL | The currency for the salary (currently we only support 'euro' and 'złoty'). | |
| created_at | datetime | YES | NULL | Timestamp. | |
| start_at | datetime | YES | NULL | Position available from the date | |
| end_at | datetime | YES | NULL | Position available until the date | |
| score | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search | |
| max_score | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search | |
| completion | int(10) unsigned | NO | 0 | Percentage of completion of training fields | |
| is_validated | tinyint(1) | NO | 0 | Whether this position has been validated or not. | |
| validated_at | datetime | YES | NULL | Timestamp. | |
| is_rejected | tinyint(1) | NO | 0 | Whether this position has been rejected by an admin or not. | |
| rejected_at | datetime | YES | NULL | Timestamp. | |
| distance | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search | |
| is_visible | tinyint(1) | NO | 1 | Whether this offer is published for other users to see or... not yet. | |
| is_validated | TINYINT(1) | YES | NULL | ||
| validated_at | DATETIME | YES | NULL | ||
| is_rejected | TINYINT(1) | YES | NULL | ||
| rejected_at | DATETIME | YES | NULL | ||
| distance | INT(10) | YES | NULL | Champs dynamique pour calcul de pondération lors d'une recherche de formation |
A list of skills required for this position/vacancy (link between the position and the skill tables).
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| position_id | int(11) | NO | PRI | NULL | Link to position.id |
| skill_id | int(11) | NO | PRI | NULL | Link to skill.id |
Skills list extracted from the ESCO framework.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| concept_type | varchar(255) | NO | NULL | Always KnowledgeSkillCompetence
|
|
| concept_uri | varchar(255) | NO | NULL | URI pointing to this skill in the online ESCO framework page. | |
| skill_type | varchar(255) | NO | NULL |
skill/competence, knowledge or skill/competence knowledge as defined in ESCO |
|
| reuse_level | varchar(255) | NO | NULL | Description of how widespread/reused this skill is across sectors of activity. | |
| preferred_label | longtext | NO | NULL | Skill label | |
| alt_labels | longtext | NO | NULL | Alternative labels | |
| hidden_labels | longtext | YES | NULL | Internal labels | |
| status | varchar(255) | NO | NULL | Whether it has been released
|
|
| modified_at | datetime | YES | NULL | Timestamp | |
| scope_note | longtext | YES | NULL | Text describing whether this skill includes others or not | |
| definition | longtext | YES | NULL | Definition of the skill. | |
| in_scheme | longtext | YES | NULL | Link to the online ESCO framework pages about concept schemes. | |
| description | longtext | YES | NULL | Description of the skill. |
Translations for the skill table.
| name | type | length | default | comment |
|---|---|---|---|---|
| id | INT | 11 | ||
| skill_id | INT | 11 | ||
| locale | VARCHAR | 10 | ||
| preferred_label | LONGTEXT | NULL | ||
| alt_labels | LONGTEXT | NULL | ||
| hidden_labels | LONGTEXT | NULL | ||
| definition | LONGTEXT | NULL | ||
| description | LONGTEXT | NULL |
List of training sessions registered on the platform. During the first phase of the project, these training sessions might not have dates. Later on, they should have start/end dates. As a training-recommendation platform, this table is kind of essential to the platform :-)
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| user_id | int(10) unsigned | YES | MUL | NULL | Link to user.id |
| creator_id | int(10) unsigned | YES | MUL | NULL | Link to user.id |
| occupation_id | int(11) | YES | MUL | NULL | Link to occupation.id |
| name | varchar(255) | NO | NULL | Name of the training | |
| location | varchar(255) | YES | NULL | Textual description of the location | |
| description | longtext | YES | NULL | Description of the training. | |
| price | double | YES | NULL | The cost of the training. | |
| created_at | datetime | YES | NULL | Timestamp. | |
| start_at | datetime | YES | NULL | At what date the training session starts. | |
| end_at | datetime | YES | NULL | At what date the training session ends. | |
| has_sessions | tinyint(1) | NO | 0 | Are there any sessions associated with this training? | |
| is_online | tinyint(1) | NO | 0 | Whether this training is at least partly online. | |
| is_online_monitored | tinyint(1) | NO | 0 | If online, whether this training is tutored. | |
| is_presential | tinyint(1) | NO | 0 | Whether this training is at least partly in person. | |
| url | varchar(255) | YES | NULL | URL for more info. | |
| files | varchar(255) | YES | NULL | Attached file. | |
| score | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search | |
| completion | int(10) unsigned | NO | 0 | Percentage of completion of training fields | |
| is_validated | tinyint(1) | NO | 0 | Whether this training has been approved by an admin. | |
| validated_at | datetime | YES | NULL | Timestamp. | |
| is_rejected | tinyint(1) | NO | 0 | Whether this training has been rejected by an admin. | |
| rejected_at | datetime | YES | NULL | Timestamp. | |
| longitude | varchar(255) | YES | NULL | Longitude | |
| latitude | varchar(255) | YES | NULL | Latitude | |
| duration_details | varchar(255) | YES | NULL | Textual description of the duration. | |
| duration_value | int(10) unsigned | YES | NULL | Numeric duration ("4" if "4 days") | |
| duration_time_to_seconds | int(11) | YES | NULL | Numeric duration in seconds | |
| duration_unity | enum('hours','days','weeks','months') | YES | NULL | Numeric duration scale (hours, days, weeks or months) | |
| currency | enum('euro','złoty') | YES | NULL | Currency in which the cost is expressed. | |
| max_score | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search | |
| avg_mark | double | YES | NULL | Hydrated dynamic fields for advanced search | |
| total_mark | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search | |
| distance | int(10) unsigned | NO | 0 | Hydrated dynamic fields for advanced search |
This table registers feedback votes from users on each training.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| training_id | int(11) | NO | MUL | NULL | Link to training.id. |
| user_id | int(10) unsigned | NO | MUL | NULL | Link to user.id. |
| mark | int(10) unsigned | NO | 0 | The score given by the user to this training. | |
| comment | longtext | YES | NULL | A comment left by the user about the training. | |
| created_at | datetime | YES | NULL | Timestamp. |
This table represents sessions (configured between two dates) of a given training.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| training_id | int(11) | NO | MUL | NULL | Link to training.id. |
| name | varchar(255) | NO | NULL | Name of the training session. | |
| location | varchar(255) | YES | NULL | Location in text. | |
| duration | varchar(255) | YES | NULL | Duration in text. | |
| description | longtext | YES | NULL | Description in text. | |
| price | varchar(255) | YES | NULL | Price | |
| start_at | datetime | YES | NULL | Start date for the session. | |
| end_at | datetime | YES | NULL | End date for the session. | |
| url | varchar(255) | YES | NULL | URL to get more info. | |
| files | varchar(255) | YES | NULL | Downloadable file provided by the training creator. |
This table stores the relationship between a training session and skills, both in terms of required skills to register to the training and in terms of skills to be acquired as a result of following the training.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| training_session_id | int(11) | NO | MUL | NULL | Link to training_session.id. |
| skill_id | int(11) | NO | MUL | NULL | Link to skill.id. |
| is_required | tinyint(1) | NO | 0 | 1 if this skill is required to enter the training session. 0 otherwise. | |
| is_to_acquire | tinyint(1) | NO | 0 | 1 if this skill will be acquired through the training session. 0 otherwise. |
This stable stores the relationship between a training and skills, both in terms of required skills to register to the training and in terms of skills to be acquired as a result of following the training.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| training_id | int(11) | NO | MUL | NULL | Link to training.id. |
| skill_id | int(11) | NO | MUL | NULL | Link to skill.id. |
| is_required | tinyint(1) | NO | 0 | 1 if this skill is required to enter the training. 0 otherwise. | |
| is_to_acquire | tinyint(1) | NO | 0 | 1 if this skill will be acquired through the training session. 0 otherwise. |
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(10) unsigned | NO | PRI | NULL | Internal ID. |
| lastname | varchar(255) | YES | NULL | User's lastname. | |
| firstname | varchar(255) | YES | MUL | NULL | User's firstname. |
| username | varchar(100) | YES | UNI | NULL | User's login name. |
| varchar(100) | NO | UNI | NULL | User's e-mail. | |
| api_token | varchar(100) | YES | UNI | NULL | API token. |
| created_at | datetime | YES | current_timestamp() | Timestamp. | |
| updated_at | datetime | YES | NULL | Timestamp. | |
| token_created_at | datetime | NO | NULL | Timestamp. | |
| roles | longtext | NO | NULL | String representing a serialized array of roles. e.g. a:1:{i:0;s:16:"ROLE_INSTITUTION";}
|
|
| password | varchar(255) | NO | NULL | User's password. | |
| homepage | varchar(255) | YES | NULL | User's homepage (only used for institutions) | |
| date_of_birth | datetime | YES | NULL | Date of birth (year for final users, date for institutions) | |
| address | longtext | YES | NULL | Address string (mostly just city and country, to calculate proximity). | |
| completion | int(10) unsigned | NO | 0 | Profile completion indicator (%) | |
| is_validated | tinyint(1) | NO | 0 | Whether this account has been validated. | |
| is_suspended | tinyint(1) | NO | 0 | Whether this account has been suspended. | |
| is_suspected | tinyint(1) | NO | 0 | Whether this account has been marked as suspicious (likely SPAM account that should be suspended, but still waiting for confirmation). | |
| is_searches_kept | tinyint(1) | NO | 1 | Whether we keep searches history for this user. | |
| code | int(10) unsigned | YES | NULL | Code sent by e-mail to validate the account | |
| code_created_at | datetime | YES | NULL | Timestamp. | |
| is_listening_position | tinyint(1) | NO | 1 | Whether this user is open to job offers. | |
| up_to_distance | int(10) unsigned | YES | NULL | Up to what distance (in km) this user is ready to drive/walk to work. | |
| professional_experience | INT(10) | YES | NULL | ||
| code | INT(10) | YES | NULL | ||
| code_created_at | DATETIME | YES | NULL |
Records of a user's training activities (whether he/she followed specific training sessions and what score they give to those). Although the user follows a training session, the vote is on the training in general.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID. |
| training_id | int(11) | NO | MUL | NULL | Link to training.id. |
| user_id | int(10) unsigned | NO | MUL | NULL | Link to user.id. |
| score | int(10) unsigned | NO | 0 | Score given by the user to this training. | |
| created_at | datetime | YES | NULL | Timestamp. | |
| updated_at | datetime | YES | NULL | Timestamp. |
Records of the user's work experience (which also gives us a look into their skills).
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID. |
| user_id | int(10) unsigned | NO | MUL | NULL | Link to user.id. |
| occupation_id | int(11) | NO | MUL | NULL | Link to occupatin.id. |
| is_current | tinyint(1) | NO | 0 | Whether this is a current job. | |
| is_previous | tinyint(1) | NO | 0 | Whether this is a past job. | |
| is_desired | tinyint(1) | NO | 0 | Whether this is a dream job. |
History of users' searches for jobs or training sessions.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID. |
| user_id | int(10) unsigned | NO | MUL | NULL | Link to user.id |
| occupation_id | int(11) | YES | MUL | NULL | Link to occupation.id |
| skill_id | int(11) | YES | MUL | NULL | Link to skill.id |
| created_at | datetime | YES | current_timestamp() | Timestamp. | |
| count_results | int(10) unsigned | NO | 0 | Number of results obtained at this point for the search. | |
| count_searches | int(10) unsigned | NO | 0 | Number of searches, so far, for the same skill or occupation search. | |
| is_active | tinyint(1) | NO | 1 | Is the search still visible in the user's history |
Record of skills associated with the user.
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | Internal ID |
| user_id | int(10) unsigned | NO | MUL | NULL | Link to user.id. |
| skill_id | int(11) | NO | MUL | NULL | Link to kill.id. |
| is_selected | tinyint(1) | NO | NULL | - |
Record of training followed by the user in the past (helps to generate the list of skills).
| Field | Type | Null | Key | Default | Purpose |
|---|---|---|---|---|---|
| user_id | int(10) unsigned | NO | PRI | NULL | Link to user.id. |
| training_id | int(11) | NO | PRI | NULL | Link to training.id. |