Vortex Student Database Schema

Introduction

The Mongo database contains all users, students and instructors, and their organization in classes. Each training session is recorded, results and metrics, in addition to scoring rules and the results. Some additional tables might be present for optional or prototype features.

In this document, we assume the reader is familiar with Mongo database.  The document describes the current state of the Vortex Student Database.

Collections

The basic collections contain information about the users, classes and training sessions

  • users : contains the information about all users 
  • classes : contains all the classes and the users in each class
  • sessionsV2 : contains each training session information
  • metrics : contains all the metrics gathered during the training sessions

Scoring collections are optional and contain the scoring rules for each scenario

  • scoring_factory : contains the default scoring rules for specific training scenarios, provided by CM Labs.
  • scoring_custom : contains the instructor-created scoring rules for specific training scenarios

Locales collection contains the translated strings to be used during report generation

  • locale_xx_YY : contains the translated text for language xx (international language code in lowercase) and for country YY (international country code in uppercase)

Some collections are used only for demo or experimental features and should be ignored.

  • bestcontainermove
  • learningpath

There is a deprecated collection (session) that can be present in some database, depending what version of simulators is running.


Users and classes

users collection
FieldTypeDescription
_idoidUnique ID for a user
namestringLogin name of the user. With this name, the user will be able to log in the Console. Must be a non-empty string with no whitespace.
versionintegerCurrent version is 620140801. Used for backward compatibility.
typestringIndicates the type of user. The type can be "STUDENT", "INSTRUCTOR" or "ADMIN". No other values are allowed.
fullnamestringThe full name of the user, that will be shown in the reports. It is usually in the form FirstName LastName. It should not be empty, but there is no error if it is.
activebooleanIndicates if the user is active or not. An inactive user cannot log in, and cannot be added to a training session by the instructor.
companystringAn optional string that can be used for the company for this user.
customidstringAn optional string that can be used if the school has its own id system.
password_hashstring256 bits cryptographic key
password_saltstring256 bits cryptographic key
IndexesDescription
_idStandard Mongo databse index on object ids
companyIndex by company
typeIndex by user type
nameIndex by user name
classes collection
FieldTypeDescription
_idoidUnique ID for a class
users_idarray of oidList of users _id that are in this class
versioninteger Current version is 620141210. Used for backward compatibility.
namestringThe name of the class.
IndexesDescription
_idStandard Mongo database index on object ids
users_idIndex by the users who are in the class

Sessions and Metrics

 

The sessionV2 collection contains all the informations about the student, the exercise, when, and any event that happened during a single training session.

sessionsV2 collection
FieldTypeDescription
_idoidUnique ID for a session
namestring(unused) Name of the session. Usually empty.
descriptionstring(unused) Description of the session. Usually empty
versionintegerCurrent version is 620200107. Used for backward compatibility.
start_timedateThe start date and time of the training session
users_idarray of oidUsers involved in this training session. It usually is 1 student and 1 instructor, it can be only 1 student on some simulators. Can be more students for tandem scenario
elapsed_timedoubleThe duration of the training session (in seconds)
equipmentarray of stringsThe training equipment as an array of 2 strings : ["Top Equipment", equipment name]
sub_equipmentarray of array of strings

The sub-equipment describes a variation on the main equipment.

It is an array of items [sub-equipment type, sub-equipment name] ... ] 

scenearray of stringsThe scene is the name of the training exercise : [exercise list name, exercise name]
bookmarksarray of bookmark documents

Bookmarks are tags that the instructor can enter during the training session.

See bookmark document definition

environmental_conditions

array of environmental_condition documents

Records all the initial weather conditions and any changes.

See environmental_condition document definition

malfunctionsarray of malfunction documents

Malfunctions that the instructor has triggered during the simulation session

See malfunction document definition

commentsstringInstructor comment entered at the end of the training session
resultstringEither "Pass", "Fail" or "None"
scoring_rule_setscoring_rule_set document

Copy of the scoring rules used during the training session.

See the scoring_rule_set document definition

video_recordingstringPath to the video recording of the training session. Not available on all simulators.
report_inforeport_info document

Contains only one field 

FieldTypeDescription
plotter_diameterdoubleThe maximal distance from the hook when drawing Hook Block positions on certain simulators.
vortex_versionstringThe Vortex Toolkit version when the session was run. Used for backward compatibility.
IndexesDescription
_idStandard Mongo database index on object ids
users_idIndex by users in the session
bookmark document
FieldTypeDescription
textstringAnnotation entered by the instructor
typeintegerInstructor coded type : 0 = Blue flag, 1 = Green flag, 2 = Orange flag, 3 = Red flag
idintegerSequential id of the bookmark in the session
timedoubleSession time the bookmark is entered, in seconds
environmental_condition document
FieldTypeDescription
timedoubleSession time this environmental condition has been set , in seconds
textstringDescription of the condition that changed
valuestringThe new value
malfunction document
FieldTypeDescription
timedoubleSession time this malfunction been triggered, in seconds
namestringMalfunction name
actionstringEither of  "FAULT_ON", "FAULT_OFF" or "FAULT_VALUE_CHANGED"
valuestringEither of  "FAULT_ON", "FAULT_OFF" or the actual value as a string
dimensionstringEither empty, or a string indicating the dimension. See References
scoring_rule_set document
FieldTypeDescription
versionintegerCurrent version is 0. Used for backward compatibility.
scenario_idstringId of the scenario used during this simulation session
scoring_rulesarray of scoring_rule document

List of rules that defines how the score is calculated

See scoring_rule document definition

score

doubleFinal score of the trainee in this session
scoring_rule document
FieldTypeDescription
versiondoubleCurrent version is 0. Used for backward compatibility.
minimumdoubleMinimum value for this rule. When the metric's value goes below the minimum, the deduction is applied.
maximumdoubleMaximum value for this rule. When the metric's value goes above the maximum, the deduction is applied.
deductiondoubleNumber of points deducted per trigger
metric_unique_namestringA unique name identifying the metric associated to this Scoring Rule
trigger_countintegerNumber of times this rule was triggered
trigger_timestampdoubleSession time when this rule was triggered the last time

The metrics collection contains all the metrics for all training sessions in the database. Each metric contains the oid of the session it belongs to. Those metrics are used for report generation.

metrics collection
FieldTypeDescription
_idoidUnique ID for this metric
namestringName of the metric
valuedoubleFor non-vector3 type metric : Value of the metric 
valueXdouble


For vector3 type metric : the value is (valueX, valueY, valueZ)

valueYdouble
valueZdouble
sessionoidUnique id of the training session when the metric was recorded
unitstring(obsolete) Contained the unit symbol for the metric. Replaced by dimension and hint.
dimensioninteger

Value indicating the dimension of the value (time, speed, etc..). 

See the list of Dimension values in the References

hintinteger

Value indicating how the dimension should be displayed 

See the list of Hint values in the References

versionintegerCurrent version is 620200107. Used for backward compatibility.
IndexesDescription
_idStandard Mongo database index on object ids
sessionIndex by the session from which they come
nameIndex by the metric name
session and timeIndex by the session and the time, so they are retrieved in the order they were saved

Scoring rules : factory and custom

Scoring_factory collection contains the default scoring rules for each scenario, as designed by CM-Labs. Those are hard coded but will be updated automatically when a new version of the Training product is installed. The scoring_custom collection contains the rules that an instructor has created or modified on their own system. Those are local to a system. Both collections have exactly the same structure and data, they only differ in the way they are used.

The structure of those collections is very similar to the scoring rule set document in the session.


scoring_factory and scoring_custom collections
FieldTypeDescription
_idoidUnique id for this scoring_factory
versionintegerCurrent version is 0. Used for backward compatibility.
scenario_idstringid of the scenario used during this simulation session
scoring_rulesarray of scoring_rule document

List of rules that defines how the score is calculated

See scoring_rule document definition

asset_version

stringThe Vortex Toolkit version when those scoring rules were saved. Used for backward compatibility.
IndexesDescription
_idStandard Mongo database index on object ids
scenario_idIndex by the scenario for which they are used
scoring_rule document
FieldTypeDescription
versiondoubleCurrent version is 0. Used for backward compatibility.
minimumdoubleMinimum value for this rule. When the metric's value goes below the minimum, the deduction is applied.
maximumdoubleMaximum value for this rule. When the metric's value goes above the maximum, the deduction is applied.
deductiondoubleNumber of points deducted per trigger
metricstringBase-64 serialization of the metrics information

Locales collections

The various locales collections that may or may not be present contain the translated strings that are used when generating reports. Each locale table has the same structure and contains the strings for one particular language. The specific language can be retrieved in the name of the collection :  locale_xx_YY : contains the translated text for language xx (international language code in lowercase) and for country YY (international country code in uppercase).

locale_xx_YY collection
FieldTypeDescription
_idoidUnique id for this translation
contextstringContext for this string (allows disambiguation between same string in different contexts) 
sourcestringSource in the original language (usually English), but can also be a translation key.
translationstringTranslated text in the target language
IndexesDescription
_idStandard Mongo database index on object ids
context and sourceIndex by the the context and the source string
source and translationIndex by the the source and translation

Obsolete sessions collection

The sessions collection is obsolete and new sessions are always added to the sessionsV2 collection. However, this collection may be present in the database of a client that has older simulators. It may even be active, as older simulators will write their sessions to this collection. All the sessions this this obsolete collection are shown in the Console and reports can be generated from them.

The structure of the sessions collection is very similar to the sessionsV2 collection

References 

Dimension : describe the type of value
valueDescription
0None (dimensionless)
1Length
2Volume
3Mass
4Time
5Angle
6Linear speed
7Linear acceleration
8Angular speed
9Angular acceleration
10Force
11Torque
12Energy
13Work
14Power
15Moment inertia
16Spring constant linear
17Spring damping linear
18Unit Length Spring constant linear
19Unit Length Spring damping linear
20Kinetic loss
21Percentage %
22Normalized
23Spring constant angular
24Spring damping angular 
25Unit Length Spring constant angular
26Unit Length Spring damping angular
27Damping linear velocity
28Damping angular velocity
29Density
30Linear Density
31Momentum
32Count
33Transformation matrix without scale
34Transformation matrix
35Pressure
36Scale
37Reciprocal of a linear spring constant
38Reciprocal of a angular spring constant
39Frequency
40Surface density
41Angular kinetic loss
42Area
43Volume flow rate
44Liquid volume
45Liquid volume rate
46Rotation rate
47Bucket efficiency with respect to fuel consumption
48Bucket efficiency over time
49Blade efficiency
50Temperature
Hint : modify how the dimension will be displayed
ValueDescription
0Standard
1Speed km/h (instead of m/s)
2Mass tonne (instead of kg)
3Power kW (instead of W)
4Pressure kPa (instead of Pa)
5Bucket efficiency tonne/L (instead of kg/L)
6Bucket efficiency tonne/h (instead of kg/s)
7Speed m/min (instead of m/s)
8Blade efficiency kg*km/L (instead of kg*m/L)
9Blade efficiency tonne*km/L (instead of kg*m/L)
10Length km (instead of m)
11Length mm (instead of m)
12Pressure bar (instead of Pa)