Entités et relations

Mise à jour 04 février 2026 22:07

TrackTik's Data Warehouse's tables and fields are directly correlated and populated from its API. This means that you can infer the meaning and relations of most tables and fields based on the API documentation referenced elsewhere in the support system.

Exemple : Table des employés

Une table Employés typique de votre solution DWH sera définie comme suit :

CREATE TABLE `abc_employees` (
`id` int NOT NULL,
`customId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`firstName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`lastName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`jobTitle` varchar(255) DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`status` enum('INACTIVE','ACTIVE','TERMINATED','BANNED') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`region` int DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`employmentProfile` int DEFAULT NULL,
`gender` enum('M','F','B') DEFAULT NULL,
`age` int DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
`ageGroup` varchar(255) DEFAULT NULL,
`address` int DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`notificationSettings` int DEFAULT NULL,
`language` enum('EN_US','FR','RO','ES','DE','NL','PT','SV','ZH-HANS','TH') DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`primaryPhone` varchar(255) DEFAULT NULL,
`secondaryPhone` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`lastLoggedInOn` datetime DEFAULT NULL,
`avatar` text,
`createdBy` int DEFAULT NULL,
`createdOn` datetime DEFAULT NULL,
`updatedBy` int DEFAULT NULL,
`updatedOn` datetime DEFAULT NULL,
`_update_time` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `region` (`region`),
KEY `employmentProfile` (`employmentProfile`),
KEY `gender` (`gender`),
KEY `address` (`address`),
KEY `notificationSettings` (`notificationSettings`),
KEY `language` (`language`),
KEY `status` (`status`),
KEY `createdBy` (`createdBy`),
KEY `updatedBy` (`updatedBy`),
KEY `updateTime` (`_update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Dans la définition ci-dessus, vous remarquerez qu'il y a beaucoup de nombres entiers pour les noms de champs à un seul mot. Il s'agit d'un modèle typique qui signale, « il s'agit d'une relation avec soi-même ou avec une autre table/entité API » :

region : fait référence à la clé primaire de abc_regions [id]
createdBy : référence à une autre inscription abc_employees [id]

In the TrackTik API documentation, endpoint /employees, it is generally the same. The below is a bit different because the API was recently updated to have additional fields, and the API can return 1:many arrays in JSON format that the DWH SQL/table paradigm cannot per row of data in a recordset.

Source: https://partnerships.staffr.net/rest/v1/2020-01-01/core/entities#tag/employees

Il affiche également des nombres entiers pour la région et createdBy. Les régions ont leur propre entité/point de terminaison :

Regions
https://partnerships.staffr.net/rest/v1/2020-01-01/core/entities#tag/regions/operation/listRegions

This means that when seeking to explore what data entity a field is pointing to, in order to perform a JOIN, you can refer to the API documentation.

Articles dans cette section