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 <TODO link to API "Extended Documentation">API documentation referenced elsewhere in the support system.</todo>
Example: Employees table
A typical Employees table in your DWH solution will be defined like this:
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;
In the above definition, you'll notice there are many integers for single worded field names. This is a typical pattern of "this is a relation to self or another table/API entity":
- region : refers to the abc_regions primary key [id]
- createdBy : self reference to another abc_employees record [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
It to shows integers for region and createdBy too. Regions has its own entity/endpoint:
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.