Sample Tracker Data Model
Geobank has very specific requirements for the fields and data types in the Sample Tracker tables. Use this link to download a spreadsheet which shows the tables, fields and data types required. The system mappings enable you to use custom names, however the tables and fields must be present, even if unused, and must have the correct data types.
Historically, beginning with Geobank version 8, which "system" Sample Tracker Data Model to use could be chosen from two options, as shown in the following screenshot:
In Geobank version 9, the Database Deployment tool was introduced. As a result:
- Some Sample Tracker field names were changed to become more meaningful (LAB_METHOD, DESCRIPTION field etc.)
- System mapping tables were set to map fields to the "GBIS/Geobank Version 7" system field names.
The purpose of the (GB_SYS_TABMAP and GB_SYS_FLDMAP) system mapping tables is to facilitate the use of a flexible data model. A mapping is only required if system field names and user field names differ.
In Geobank version 10 (Geobank 2017 and Geobank 2018):
- The Sample Tracker Data Model has been extended. This is now referred to as the "Geobank Version 10" model.
- There have also been a few minor changes to other existing tables
If you are upgrading from Geobank 2017 (10.0), there is no impact on backward compatibility.
If you are upgrading from a version prior to Geobank 2017 (i.e. v9.0 or earlier) there may be some impact on backward compatibility, and you will therefore need to verify that your mappings are correct.
In Geobank version 11 (Geobank 2020 and Geobank 2021.5):
There have been no changes to the requirements for Sample Tracker tables, columns or their data types. Version 11 uses the Sample Tracker v10 data model.
The upgrade makes the following changes when upgrading from Geobank 2018(10.1) to Geobank 2021.5 (11):
-
If it exists, the mapping incorrectly referring to the ST_XS_STANDARD.QAQC_TYPE System column, is corrected to ST_XS_STANDARD.QC_TYPE, which is the column actually used by the system, the column it is mapped to is unchanged. This is a mapping data change that has no impact on the data structure.
-
If the Content database uses the “Data Model Framework” functionality, then the ‘uspGB_Admin_BuildFrameworkForValidation’ procedure is adjusted; please unsure the admin user performing the upgrade through Geobank’s User Interface has sufficient permission to execute an ALTER statement on this stored procedure. The adjustments made are:
- Table schemas as well as names are used in comparisons to ensure a correct match,
- The new GB11_... tables are excluded from the framework validation in addition to the existing GBX_ tables which were already excluded – this only has relevance if the configuration tables are in the content database.
- The special rules for the CL_WASH table have been removed.
- Validation of field mappings
The validation of field mappings is now more rigorous, thus the upgrade may report a missing field mapping and terminate before applying changes. Any missing field mappings should be added before the upgrade is restarted.
Note: The SQL view “GB.TABLEFIELD_MAPPING_VIEW” introduced in GB v10 is no longer used in v11 and can be removed once v10 has been decommissioned.
Mappings for the Sample Tracker Data Model
The table below shows those tables and fields in the database which may differ from the field names in the system data model. It shows both "GBIS/Geobank Version 7" and "Geobank Version 10" Sample Tracker system field names. All tables shown are in the dbo schema. For full table details, refer to the data model diagram:
Use the table below to verify that your mappings are correctly set for "Geobank Version 10" Sample Tracker.
If you need to run Geobank 2013 in parallel with the current version
In order to ensure that both versions are supported, each version of Geobank only requires a mapping if the USR_FIELD_NAME (the column name in your database) differs to the SYS_FIELD NAME for the version.
Thus for each of the rows in the table below, where the V7 SYS_FIELD_NAME is different to the v10 SYS_FIELD_NAME mapping, choose the SYS_FIELD_NAME value that is different to the actual column name in your database.
For example:
If a table in your database has DESCR and the V7 SYS_FIELD_NAME is DESCR, but the v10 SYS_FIELD_NAME is DESCRIPTION, then in your GB_SYS_FLDMAP mapping table, set SYS_FIELD_NAME as DESCRIPTION and USR_FIELD_NAME as DESCR.
This will support v10; v7 will work without a mapping, since the field name matches the name which v7 requires.
| SYS_TABLE_NAME | v7 SYS_FIELD_NAME | v 10 SYS_FIELD_NAME | Deployed USR_FLD_NAME |
|---|---|---|---|
| GB_COORD_SYSTEM | DESCR | DESCRIPTION | DESCRIPTION |
| GB_LKP_ATTR_NAME | DESCR | DESCRIPTION | DESCRIPTION |
| GB_LKP_CATEGORY | DESCR | DESCRIPTION | DESCRIPTION |
| GB_LKP_CODE | DESCR | DESCRIPTION | DESCRIPTION |
| GB_PROJECT | DESCR | DESCRIPTION | DESCRIPTION |
| ST_DESPATCH | DESCR | DESCR | DESCRIPTION |
| ST_DESPATCH_PREP | DESCR | DESCR | DESCRIPTION |
| ST_LKP_CATEGORY | DESCR | DESCRIPTION | DESCRIPTION |
| ST_LKP_CODE | DESCR | DESCRIPTION | DESCRIPTION |
| ST_MD_LAB | DESCR | DESCR | DESCRIPTION |
| ST_MD_LAB_COMBO | METHOD | METHOD | LAB_METHOD |
| ST_MD_LAB_LOCATION | DESCR | DESCR | DESCRIPTION |
| ST_MD_LAB_PREP | DESCR | DESCR | DESCRIPTION |
| ST_RESULT | METHOD | METHOD | LAB_METHOD |
| ST_RESULT | METHOD2 | METHOD2 | GENERIC_METHOD |
| ST_RESULT | ELEMENT | ELEMENT | LAB_ELEMENT |
| ST_RESULT | RESULT_TEXT | RESULT_TEXT | LAB_RESULT_TEXT |
| ST_RESULT | STORE_RESULT | LAB_RESULT_NUMERIC | LAB_RESULT_NUMERIC |
| ST_RESULT | STORE_UNITS | STORE_UNITS | LAB_UNITS |
| ST_RESULT | CALC_RESULT | RESULT | RESULT |
| ST_RESULT_STANDARD | METHOD | METHOD | LAB_METHOD |
| ST_RESULT_STANDARD | METHOD2 | GENERIC_METHOD | GENERIC_METHOD |
| ST_RESULT_STANDARD | ELEMENT | ELEMENT | LAB_ELEMENT |
| ST_RESULT_STANDARD | RESULT_TEXT | RESULT_TEXT | LAB_RESULT_TEXT |
| ST_RESULT_STANDARD | STORE_RESULT | LAB_RESULT_NUMERIC | LAB_RESULT_NUMERIC |
| ST_RESULT_STANDARD | STORE_UNITS | STORE_UNITS | LAB_UNITS |
| ST_RESULT_STANDARD | CALC_RESULT | RESULT | RESULT |
| ST_SYS_LKP_CODE | DESCR | DESCRIPTION | DESCRIPTION |
| ST_XS_ELEMENT | DESCR | DESCR | DESCRIPTION |
| ST_XS_FORMAT | FORMAT_DESCR | FORMAT_DESCRIPTION | FORMAT_DESCRIPTION |
| ST_XS_FORMAT_LAYOUT | DESCR | DESCRIPTION | DESCRIPTION |
| ST_XS_METHOD | METHOD | GENERIC_METHOD | GENERIC_METHOD |
| ST_XS_METHOD | DESCR | DESCRIPTION | DESCRIPTION |
| ST_XS_METHOD_ELEMENT | METHOD | METHOD | GENERIC_METHOD |
| ST_XS_PREP_FIELD | DESCR | DESCR | DESCRIPTION |
| ST_XS_STANDARD | DESCR | DESCRIPTION | DESCRIPTION |
| ST_XS_STANDARD_DETAIL | METHOD | GENERIC_METHOD | GENERIC_METHOD |
| ST_XS_UNIT | DESCR | DESCRIPTION | DESCRIPTION |