Relationships

Geobank Mobile uses the Relationship data type to define parent-child table relationships and handles the scenario where there are multiple primary key fields in the parent table in a beneficial way. The process is demonstrated by the following example:

We have a Logging Profile with two tables, Site and Lithology. The definitions for each table are as follows:

Site

  • The table Site has three fields: Project, Site and DateEntered.
  • This table is the parent table in the parent-child relationship.
  • The fields Project and Site_ID comprise the Primary Key.

Lithology

  • The table Lithology has three fields: Site Field, Depth From and Depth To.
  • This table is the child table in the parent-child relationship.
  • The field Site is where we wish to define the Relationship Lookup.

Sample data in the Site table could look like the following:

Sample data in the Lithology table could look like the following:

In the case where there are multiple primary keys in the parent table, there are several important things to note:

  • In the “Edit Logging Profile” section, only one Relationship is needed to link a table to its parent. In the example shown above, the definition of a Relationship data type for the column Site_ID links the child table to its parent table Collar. The column selection of ‘Site_ID’ is simply for display purposes.

So choose the most appropriate key column in the parent table for display, but ensure that only one relationship to the parent table is defined.

  • Do not link a child table to its grand-parent table – this will create a triangular set of references which will not record relational data correctly.
  • Geobank Mobile analyses the primary key field for each column and substitutes the parent table's primary keys in the export. It "knows" what the grandparent value is (in this case 'Project1'). This means that when the table is exported, the data will look like the following:

In the underlying database, Geobank Mobile uses surrogate keys and this is the reason why parent tables with multi-column natural keys must be referenced only once by the child table (in the database there is only a single key column per table).