Data structure
By design, SyncHub does very few data transformations. We do, however, make some minor changes to ensure consistency.
## Global fields
SyncHub provides the following fields on all endpoints, regardless of whether they are offered by the API or not.
#### WhenCreated and WhenModified
All data tables contain _WhenCreated_ and _WhenModified_ columns. These will be populated provided we can ascertain the creation date or modification date of a record. If the field is _not_ provided by the endpoint, then these fields will be NULL.
####RemoteID
The RemoteID is the unique identifier supplied by the data source. In the case of parent-child queries, where there is no clear unique identifier, we will generate a RemoteID using both parent and child identifiers.
####Nested data and parent-child relationships
Often data sources will provide nested data, for example sale and sale items. In order to put this data in a tabular format, SyncHub will create a second table for the child items and insert a parent remote ID column. In this example, the Sale Item table would have a SaleRemoteID column.
####Deleted records
Instead of removing data from your database, SyncHub will use an IsDeleted field to mark records which are no longer available at the data source. If a data source provides a deleted status field then we will update the IsDeleted field accordingly, otherwise tools like our [Deletion Management](/kb/deletionmanagement?api=ordoro) facility can populate this field.
##Data relationships
SyncHub draws data from cloud platforms and stores it in a relational format in your data warehouse. To use the warehouse, you need to know the relationships between the tables. There are two main methods you can use.
####Method 1 – as provided by the cloud platform
In many cases, your cloud platform will provide "relationship" fields out of the box. The naming scheme for these varies, but it is intuitive. In the example below, our ProductPrice table has a column called ProductGuid. It is logical that this refers to the Guid column of the Product table:
####Method 2 – SyncHub auto-relationships
The above method only works if the cloud platform formats its API in a relational format. However, many APIs return their data nested. In these cases, there is no parent column provided because it is inferred by that nested relationship.
By definition, your relational data warehouse does not store nested data – each entity is instead in its own table. In these scenarios, SyncHub will helpfully create additional columns which allow you to tie the child tables back to their parent tables:
- Every table has a RemoteID column – regardless of whether it is a parent or child table.
- If a table has a Child table, then the child will have a column named [Parent]RemoteID. In the example below, the column is called TaskRemoteID because the parent is the "Task" table:
