Data Model
Data is value
Thanks to our data connector, Mobility Work offers you a set of database tables that bring together your network's maintenance data.
This data can be consulted via a Business Intelligence interface or - quite simply - with Microsoft Excel (via an ODBC connection).
Aggregating and analyzing this data provides crucial information on equipment performance, maintenance trends and opportunities for improvement.
By combining this data with other relevant sources (MES, for example), it is possible to obtain a comprehensive overview that can be used to optimize maintenance programs and ensure ongoing equipment availability.
Our solution therefore offers added value to maintenance managers, enabling them to effectively manage and make optimum use of plant maintenance data.
Different types of key
id, short_id, legacy_id which foreign key to choose for joins?
- legacy_id: old key system, use it only if it's the only key corresponding to the primary key of the table you want to make your join with.
- my entity]_id is a database-independent key, which can take 2 formats depending on the table
- character string of 50 characters
- character string with the pattern xxxxxxxx-xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
- my entity]_short_id: equivalent to a hash of [my entity]_id
- Although "short", each key is guaranteed unique, so you can use it without risk of collision.
Date/Time according to which timezone.
- All Date/Time values are expressed in terms of your network's timezone.
No time limit for tables: all data has been present since the creation of your network.
Table details
_changelog
Changelog (evolutions) of the data model of our B.I. One line for each new version
| Key | Value | Comment |
|---|---|---|
| date | 2022-09-10 | Date of update (addition, deletion, modification) of our data model |
| change | - Added tag_groups table to map tags to groups |
- Added
tag_codecolumn ontagstable, you are suggested to rely on thetag_codefor universal reference instead oftag_id(ortag_legacy_id) - Added this changelog to keep you informed of changes on your db schema | Update description |
_parameters
Information on the network and date of last data update
| Key | Value | Comment |
|---|---|---|
| last_export_date | YYYY-MM-DD HH:MM:SS | Database updated every 24 hours |
| network.id | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | |
| network.id_legacy | xxx | |
| network.name | xxxxxxx Factory | |
| network.timezone | Europe/Paris |
activities
List of activities in your network
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| activity_id | int | NO | xxxxxx | activity id |
| task_id | varchar(255) | NO | xxxxxxxxxxxxxxxxxxxxxxxxxxx | Id of the task to which this activity is linked. |
| task_legacy_id | varchar(255) | NO | xxxxxx | Id of the task to which this activity is linked. |
| task_short_id | varchar(255) | NO | xxxxxx | Id of the task to which this activity is linked. |
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Id equipment for this activity. |
| member_id | char(36) | YES | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Id of the person who carried out the activity. |
| member_legacy_id | int | YES | xxxx | Id of the person who carried out the activity. |
| name | varchar(2000) | NO | Remplacement courroie | |
| duration_in_minutes | int | NO | 60 | Activity duration in minutes. |
| started_at | datetime | NO | YYYY-MM-DD HH:MM:SS | Date/time of activity start |
equipments
List of network equipment
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | equipment id |
| equipment_legacy_id | int | NO | 178777 | equipment id |
| name | varchar(255) | NO | EASYWASH XL 140 - PR BAC RETENTION | Equipment name |
| description | varchar(3000) | YES | Bac de rétention pour fontaine de dégraissage | Equipment description |
| cost_center | varchar(255) | YES | RM310-OPE-PAC | Equipment cost center |
| external_reference | varchar(255) | YES | MW-277bf489-def4-4dsc-befc-fbeUs65be1e6 | Your (ERP or other master data) reference |
| is_spare_part | varchar(5) | NO | “true” ou “false” | If true is a spare part. |
| archived_at | datetime | YES | YYYY-MM-DD hh:mm:ss | corresponds to the date on which the equipment had been archived |
| external_reference | varchar(255) | YES | Only for spare part - provide a way to access spare parts requirements |
equipments_relationships
Define relation between equipment
Id and legacy id are provide for explorer relation between equipments.
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | equipment id |
| equipment_legacy_id | int | NO | 178777 | equipment id |
| linked_equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | equipment id of linked equipment |
| linked_equipment_id | int | NO | 178777 | equipment id of linked equipment |
equipments_tags
Lists all (0..*) tags linked to a piece of equipment.
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | equipment id |
| equipment_legacy_id | int | NO | 635343 | equipment id |
| tag_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Tag id |
| tag_legacy_id | int | NO | 7654 | Tag is |
| tag_name | varchar(255) | NO | Machine critique | Tag name |
| tag_code | varchar(255) | NO | $preventive, mecanique, $critical_equipment | tag code to tags association table for easier cross networks / cross sources comparison |
spare_part_stocks
- Liste des spare part avec stock, stock mini et maxi…
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | equipment id |
| equipment_legacy_id | int | NO | 635343 | equipment id |
| external_reference | varchar(255 | mw-cjcwxwcx-987 | Your identifier. Several spare parts may have the same ID but a different location. Circumvent the lack of multiple locations. | |
| name | varchar(255) | NO | Name of spare part | Ex : 0__Motor Power 0_501 |
| storage_location | varchar(255) | YES | Location of spare part (free format) | ex : 32_65_007 |
| current_stock_quantity | decimal(10,0) | YES | Current stock | ex : 25 |
| minimum_stock | decimal(10,0) | YES | Minimum stock | |
| maximum_stock | decimal(10,0) | YES | Maximum stock | |
| unit_price_in_lowest_currency_unit | YES | Price in centimes | Ex : unit_price_in_base_currency_unit = 10,25 EUR | |
| unit_price_in_lowest_currency_unit = 1025 EUR | ||||
| unit_price_in_base_currency_unit | YES | Price in currency | ||
| unit_price_currency | char(3) | NO | Currency of spare part | Ex : EUR, USD |
spare_parts_movements_beta
Add spare parts movements, including replenishment, consumption and inventory, this is subject to change and/or be deleted at any time, do not depend on this in production.
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| movement_id | char(40) | NO | 011c117fe759c77ee1219a5bd6688b7ba0d08654 | Unique movement identifier |
| spare_part_legacy_id | int unsigned | NO | 162941 | Spare part legacy ID |
| spare_part_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Spare part ID |
| movement_reporter_legacy_id | int unsigned | YES | 2162 | Id of the user who performed the movement |
| movement_reporter_id | char(36) | YES | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | UUID of the user who performed the movement |
| spare_part_name | varchar(255) | NO | 0__LEROY SOMER MB 2201 MOOC i=10_402 | Name of spare part |
| movement_date | datetime | NO | 2020-11-11 23:00:00 | Movement date/time |
| movement_type | varchar(255) | NO | replenishment, consumption, physical_recount, return_of_consumption | Movement type |
| movement_metadata | json | YES | {"origin": "integrations"} | This field gives us the ability to add metadata over time to enrich the information available on this movement. |
| movement_quantity | decimal(10,0) | NO | 44 | Quantity of movement parts |
| task_id | varchar(255) | YES | SzU0Nk1NTU3SNUozTtYAAA | Id of the task if the movement is performed during a task. Example: consumption of spare parts in the activity of a task. |
| known_stock_quantity_before_movement | decimal(10,0) | YES | 21 | Quantity of spare parts prior to movement |
| unit_price_in_lowest_currency_unit | bigint | YES | 1200 | Tunit price in centimes |
| unit_price_in_base_currency_unit | decimal(10,0) | YES | 12 | Unit price in currency |
| unit_price_currency | char(3) | YES | EUR |
members
List of users on your network.
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| member_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | User id |
| member_legacy_id | int | NO | 56544 | USer id |
| first_name | varchar(255) | NO | laurent | Last name |
| last_name | varchar(255) | NO | xxxxxxxx | First name |
| varchar(255) | NO | [email protected] | ||
| role | varchar(255) | NO | admin, provider, tech, production, partner_api | User role, possible values : admin, provider, tech, production, partner_api |
meter_reading
List of meter reading on your network equipment.
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| measurement_date | datetime | NO | YYYY-MM-DD HH:MM:SS | Meter measurement date/time. |
| This date/time is expressed according to the time zone of your network. | ||||
| value | varchar(255) | NO | 1.2 | Measurement value. |
| The decimal separator is left up to you (often depending on your system of measurement "." in Europe "," in the U.S.). | ||||
| unit_symbol | varchar(255) | NO | Kw | Unit of measurement symbol. |
| unit_name | varchar(255) | NO | Kilowatt | Name of unit of measurement. |
| dimension | varchar(255) | NO | Power counters | Dimension |
| activity_id | int | NO | 761116 | id of measurement activity |
| task_id | varchar(255) | NO | xxxxxxxxxxxxxxxxxxxxx | id of measurement task |
| task_short_id | varchar(255) | NO | 851281 | id of measurement task |
| task_legacy_id | varchar(255) | NO | 852281 | id of measurement task |
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | id of measuring equipment |
| equipment_legacy_id | int | NO | 261280 | id of measuring equipment |
tag_groups
List of tag groups in your network.
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| tag_group_id | int | NO | 503 | Tag group id |
| tag_group_name | varchar(255) | NO | Type de maintenance | Tag group name |
tags
Tags list
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| tag_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Tag id |
| tag_legacy_id | int | NO | 897 | Tag id |
| tag_name | varchar(255) | NO | Correctifs | Tag name |
| tag_code | varchar(255) | NO | $corrective | Code identifier (unique) defining your tag independently of the database. If it begins with $, then it's a tag created by Mobility work and the code cannot be modified. The "hard" code ensures the code's consistency over time, for example in the case of a database. |
| tag_group_id | int | YES | 7654 | id of the tag group to which your tag belongs (if belongs to a tag group, otherwise Null) |
| tag_group_name | varchar(255) | YES | Type de maintenance | Name of the tag group to which your tag belongs if it belongs to a tag group, otherwise Null) |
tasks
Tasks list
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| task_id | varchar(255) | NO | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | |
| task_legacy_id | varchar(255) | NO | 5c4761e1 | |
| task_short_id | varchar(255) | NO | 5c4761e1 | |
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | UUID of the equipment linked to the task |
| description | varchar(3000) | NO | Prév (6m) / MP-147-31-02 Convoyeur entrée étiqueteuse | |
| scheduled_from | datetime | NO | YYYY-MM-DD HH:MM:SS | |
| scheduled_to | datetime | NO | YYYY-MM-DD HH:MM:SS | |
| done_date | datetime | YES | YYYY-MM-DD HH:MM:SS ou NULL | |
| estimated_stop_time | int | YES | 70 | Estimated stop time (minutes) |
| estimated_intervention_time | int | yes | 120 | Estimated intervention time (minutes) |
| status | varchar(255) | scheduled, in_progress, completed, canceled | ||
| is_part_of_maintenance_plan | varchar(5) | NO | “true” ou “false” | true if task is a part of a maintenance plan. false if not part of a maintenance plan. |
| maintenance_plan_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | UUID of the maintenance plan that generated the task |
| members_involved_count | int | YES | Number of involved members for the task | |
| activities_count | int | YES | Activities count for the task | |
| cumulated_duration_in_minutes | int | YES | Actual cumulated duration (minutes) spent on the tasks based on activities | |
| actual_task_duration_variation_percentage | int | YES | Variation percentage between actual & expected |
tasks_assignees
Link between user(s) assigned to a given task and the tasks.
- relationship table n..m
- allows you to list
- users linked to a task
- tasks linked to a user
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| task_id | varchar(255) | NO | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | Task id |
| task_legacy_id | varchar(255) | NO | 73536353 | Task id |
| task_short_id | varchar(255) | NO | 73536353 | Task id |
| member_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | User id |
| member_legacy_id | int | NO | 7363 | User id |
tasks_tags
Link between tags and tasks
- relationship table n..m
- allows you to list
- All tags associated with a task
- The set of tasks associated with a tag
| Key | Type | Null | Value | Comment |
|---|---|---|---|---|
| task_id | varchar(255) | NO | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | Task id |
| task_legacy_id | varchar(255) | NO | 2415869 | Task id |
| task_short_id | varchar(255) | NO | 2415869 | Task id |
| tag_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Tag id |
| tag_legacy_id | int | NO | 2098 | Tag id |
| tag_name | varchar(255) | NO | Machine critique | Tag name |
| tag_code | varchar(255) | NO | $preventive,mecanique, $critical_equipment | tag code to tags association table for easier cross networks / cross sources comparison |
maintenance_plans_beta
| Clef | Type | Null | Valeur | Commentaire |
|---|---|---|---|---|
| maintenance_plan_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Maintenance plan UUID |
| equipment_id | char(36) | NO | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Equipment UUID |
| description | tinyint(1) | NO | Nettoyage de la couche de carbonate sous le socle de support du broyeur (cave) | Maintenance plan description in free text format (supports Markdown format) |
| scheduled_from | datetime | NO | YYYY-MM-DD HH:MM:SS | Maintenance plan start date |
| scheduled_task_duration | int | YES | Task duration | |
| estimated_stop_time | int | YES | Planned downtime in minutes | |
| estimated_intervention_time | int | YES | Planned maintenance time in minutes | |
| freq | varchar(255) | YES | DAILY, WEEKLY, MONTHLY | Frequency days, week(s), month(s), year(s). This value may be zero for maintenance plans with no frequency: PLC maintenance plans. |
| interval | varchar(255) | YES | This value can be zero for maintenance plans with no intervals: PLC maintenance plans. | |
| status | varchar(255) | NO | scheduled, in_progress, completed, canceled | Task status |