Database - Linking Samples to Observations

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Modules > Alive >

Database - Linking Samples to Observations

Samples and observations can be related in a few different ways. In some scenarios, multiple observations are combined into a single sample (e.g., catching and observing individual small fish then grouping them together into a single sample). In other scenarios, a single observation may be divided into multiple samples, while others still may have a simple one-to-one relationship.

 

EQuIS Alive handles each of these scenarios by employing the AT_SAMPLE_OBSERV table:

 

AT_SAMPLE_OBSERV Field

Description

FACILITY_ID

The key that links to DT_FACILITY

SAMPLE_ID

The key that links to DT_SAMPLE

SURVEY_OBSERV_ID

The key that links to DT_SURVEY_OBSERV

EBATCH

For tracking additions made through EDP

EUID

Database unique identifier

 

Each record in this table points to a sample and an observation. This allows for one-to-many, many-to-one, one-to-one, and many-to-many relationships.

 

In some cases, a sample code is generated on the spot while taking the observations. In this scenario, the sample code can be stored in OBSERV_SAMPLE_CODE in DT_SURVEY_OBSERV.

 

Once the sample data has been loaded into EQuIS and assigned a SAMPLE_ID, the following SQL script can be run to populate the AT_SAMPLE_OBSERV table.

 

insert into at_sample_observ (facility_id, sample_id, survey_observ_id)

select ds.facility_id, ds.sample_id, survey_observ_id

from dt_survey_observ so join dt_sample ds on so.observ_sample_code = ds.sys_sample_code

where not exists (select * from at_sample_observ a where a.facility_id = ds.facility_id and a.sample_id = ds.sample_id and a.survey_observ_id = so.survey_observ_id)