1. Having a clear documentation on the tables with the table names, field names, field definitions and table relationships

    Example of documentation for 1 table

Table

Variable Name

Type

Definition

table_example

name_variable

TEXT

This is an example


Example of documentation for table relationships

Table A

Variable Table A

Mapping Table

Variable Mapping Table

transactions

name_client

clients

name

2. Defining the primary key for each table

Primary key: = it enables to identify uniquely each record (=unique id for each row of the table)
It enables us to understand the level of aggregation of each table and make sure the data is consistant.

3. Understanding the relationships between Fieldpro database and external databases

If necessary, relationships between Fieldpro databases and external databases need to be defined to make sure we can link the data points.
For example, if external tables contain SKU sales, you might need to find the link between the SKU sales in the external database and the SKU List in Fieldpro system.

4. Defining the best way to use the data


Option A: Using the external database directly in the dashboards

- When there is no consolidation between external data and Fieldpro data: the KPIs are distinct and we don’t need to use both Fieldpro and external database at the same time
- When the clients are reassured to keep the data in their own database, we only “read” their records and won’t stock their data or write on their tables

Option B: Upload the data in Fieldpro system and use Fieldpro tables in the dashboards using a stream

-When KPIs are combining Fieldpro data and external data
-When the database structure is clear with primary keys defined
-When the tables are already aggregated to avoid stocking too much information in Fieldpro system Stocking information has a cost that needs to be evaluated before offering the solution to the client.

If you decide to upload an external table in Fieldpro system, the name of your table must be in small caps to avoid any issue and have a primary key clearly defined