Understanding Data Relationships and Key Tables
Data Relationships
An understanding of the relationships between key tables that you may encounter when bulk importing or editing data is fundamental to good data management. The description below describes the relationships between the main data tables used in ESdat which are available for bulk importing and editing in the Data Migration Module.
Locations and Wells
Most monitoring data in ESdat is associated with a Location, and potentially a Well. There may be one or multiple Wells (or piezometers, screened intervals) for each Location.
Locations can also be grouped into Monitoring Zones, and multiple Monitoring Zones may be present for each Site.
Further discussion of the use of Locations and Wells in ESdat is covered in the specific article at https://help.esdat.net/en/article/locations
Chemistry Results
Chemistry data is stored primarily in the Chemistry Samples and Chemistry Results tables.
A Chemistry Sample is typically associated with a Location and, if relevant, a Well. There will often be multiple Chemistry Samples associated with a single Location (and Well if relevant).
Chemistry Samples are also often associated with a Monitoring Round.
Chemistry Samples associated with laboratory results are also associated with a Lab Sample Delivery Group and Laboratory Report.
All Chemistry Samples within a Project have a unique Sample Identifier (called a SampleCode). Chemistry Results are connected to a Sample by having a matching SampleCode. There will typically be multiple Chemistry Results associated with a single Chemistry Sample.
Borehole and Geology Data
ESdat stores borehole and geology data that would be encountered in a groundwater or contaminated site investigation,as well as the more common Geotech data.
Tables and their relationships are shown below:
Descriptions of these tables are below:
Table | Description |
Locations | All information related to a Location that is not specific to a Borehole (for example co-ordinates and description), |
Boreholes | All information related to a Borehole (example Drilling Company, Total Depth), each record is a 1:1 with Locations. |
Wells | Piezometers, Pipes, ie for groundwater monitoring of specific screened intervals |
Geology | Lithology, as well as specific components (eg Hardness, Mineralization, Colour, Grain Size, USCS etc..) |
Geology_Defect_Mapping | Information about rock fractures or defects (such as roughness, dip, planarity, spacing etc..) |
Geology Samples | Information about recovered cores, such as Recovery Length, RQD length, Core Number etc..) |
Stratigraphy | Record the depths of Stratigraphic Units |
Borehole Depth Comments | Comments related to specified Comment Types at depth, or to track specific parameter changes with depth that are not otherwise catered for in the other tables. |
Borehole Waterstrikes | Groundwater strikes and recoveries |
Borehole Penetration Resistance | Record changes in penetration resistance with depth |
Borehole Advancement | Record the rate of advancement, and changes in drilling method, depth etc.. with depth. |
Borehole Backfill | Backfill in the borehole, such as Filter Pack/Gravel Pack, Bentonite, Cuttings etc.. |
Borehole SPT | Standard Penetration Test data and results |
Aquifer Tests | Rising Head, Falling Head and Pump Test results. (Note ESdat doesn’t perform pumping test analysis, this is intended just to store the results such as Hydraulic Conductivity, Transmissivity etc..) |
Groundwater Levels
Groundwater depths (and/or elevations) are stored in the Groundwater and NAPL Levels table, Each Record requires a Location Code, Well, Date/Time and optionally a Monitoring Round.
Groundwater levels entered as Depths are converted in ESdat outputs as an Elevation, Depth below Top of Casing and Depth below Surface. In order to provide this conversion the Top of Casing must be populated in the Wells table, and the Elevation must be populated in the Locations table.
ESdat also supports changes in the Wells Top of Casing, as may be encountered due to subsidence or “benching” in mining operations. Groundwater depth to elevation calculations are able to take these TOC changes into account. Further information on the Wells Datavariant TOC table is available at https://help.esdat.net/en/article/timevarient-toc
Field Monitoring
The ESdat Field Portal & the ESdat Field App collects Field Chemistry, Groundwater and other data. Chemistry Results and Groundwater tables and relationships are already described above. This section describes their relationships and usage in relation to field data collection specifically.
Table | Description |
Monitoring Rounds | One entry per monitoring round |
Location Visits | Information about each location visited in a Monitoring Round |
Location Visits CustomFieldValues | Custom Attributes (data) that were collected for the Location Visit |
Chemistry Samples | One entry for each field sample |
Chemistry Samples CustomFieldValues | Custom Attributes associated with a sample |
Chemistry Samples Stabilisation | Groundwater Stabilisation results – typically collected prior to sampling |
Chemistry Results | Field results for a Sample (pH, EC, Turbidity etc..) |
Groundwater and NAPL Levels | Groundwater Dip Reading |
Groundwater and NAPL Levels CustomFieldValues | Custom Attributes associated with a groundwater reading |
Site Documents | Not visible directly in ESdat Online (but a view with the data is available in Data Explorer), only visible in ESdat Server. Stores photos or other documents collected in the field app and other parts of ESdat. |