Considerations on Where to Store Data

There are multiple ways to store and report location-specific data (other than concentrations), thanks to the flexibility of EQuIS databases. Some possibilities are described here. Consider the following factors to determine what is best in each situation:

•specificity of the data (e.g., per sample, location, subfacility)

•EQuIS formats used for loading data

•frequency of testing, as tables have different criteria for duplicate records

•EQuIS Schemas applied to the database

•reporting practices (which reports are currently being used or are planned to be used)

Data storage in a certain table may be less feasible if additional formats or reports (and therefore extra loading or reporting time) are involved.

If results are text-based (e.g., "TRUE" or "FALSE"), unit conversion or action levels may not work as expected; reports with Action Level Exceedance analyze numeric action levels.

The Database Schema page summarizes the types of data that each EQuIS module can store. Additional licensing, upgrading, or installation may be needed for some modules. For radiological data, see Radiological Data Management.

Common result and parameter tables are summarized below.

Record Type	Sample-Test-Result	Basic Result	Sample Parameter	Location Parameter
Data Tables	DT_SAMPLE DT_TEST DT_RESULT	DT_SAMPLE (optional) DT_BASIC_RESULT	DT_SAMPLE DT_SAMPLE_PARAMETER	DT_LOCATION DT_LOCATION_PARAMETER
Example of Standard Use	Analytical data, such as concentration of sodium	Collection results directly associated with a specific location depth but not necessarily a sample	Photoionization Detector (PID) readings	Legal Subdivision (LSD)
Format Example	Any that load analytical data	EQEDD, EQEDD_v2	EQEDD, EQEDD_v2	EQEDD, EQEDD_v2
Reference Tables*	RT_ANALYTIC_METHOD RT_ANALYTE	RT_ANALYTE RT_UNIT	RT_SAMPLE_PARAM_TYPE	RT_LOCATION_PARAM_TYPE
Unique Criteria for Incoming Records	SYS_SAMPLE_CODE + Test Alternate Key (TAK) + DT_RESULT.CAS_RN (per sample + parameter + TAK fields)	SYS_LOC_CODE + CAS_RN + DEPTH + RESULT_DATE (per location + parameter + depth + date)	SYS_SAMPLE_CODE + PARAM_CODE + MEASUREMENT_DATE (per sample + parameter + date)	SYS_LOC_CODE + PARAM_CODE + MEASUREMENT_DATE (per location + parameter + date)
Reporting	Most reports for Analytical Results (e.g., Analytical Results II), Statistics, and Action Level Exceedance (e.g., Action Level Exceedance II with Parameters)	Basic Results II, Basic Results Profile	Analytical Results with Sample Parameter (Table) Sample Parameter reports, such as Sample Parameters (Extra Fields)	Analytical Results II with Location Parameters Location Parameter reports, such as Location Parameters (Extra Fields)

* - for new references like analytes or parameters

Storing test data in the DT_RESULT table will create the most flexible reporting opportunities but may take up more database space. It may also affect report conversion if the results are text-only. Separating queries by matrix can help avoid unit conversion errors. With text-only results, the following may be advantageous:

•Having a defined unit (e.g., 'Unitless')

•If using Method Analyte Groups, adding the new analyte(s) to a group and define the unit there.

•If not using Method Analyte Groups, double-check for any conversion errors with the Result > Unit parameter defined.

The DT_SAMPLE_PARAMETER and DT_LOCATION_PARAMETER table options are limited in terms of how many results can be stored per sample or location. Consequently, any re-analyses would be difficult to store here. Action Level Exceedance reports are available for sample parameters and location parameters, but with fewer options than the Action Level Exceedance II reports for analytical values.

While EarthSoft Support can provide information on available options for specific data types, the final decision requires the users' professional judgment.