Specimen data imported into LabKey Server is stored in three related tables arranged as follows:

• The Specimens table holds information about the group of vials that comprise a specimen. You see fields from this table when you view specimens "by vial group."
• The Vial table holds all the information about a single vial (or or aliquot). You see fields from this table when you view specimens "by individual vial." There can be many Vial rows for any given Specimen row.
• The SpecimenEvent table closely matches the specimen archive file and contains fields that describe each individual event for a given vial or other aliquot. There can be many SpecimenEvent rows for a given Vial row. If you view History for an individual vial, you are seeing fields from this event table.

File-by-File Overview

Data destinations for each file in the specimen import archive:

• labs.tsv: This data is imported directly into the study.Location table.
• primary_types.tsv: This data is imported directly into the study.SpecimenPrimaryType table.
• derivatives.tsv: This data is imported directly into the study.SpecimenDerivative table.
• additives.tsv: This data is imported directly into the study.SpecimenAdditive table.
• specimens.tsv: See below.

Details for Specimens.tsv

The file import process for specimen.tsv is the most complex.

All fields from this file go into the study.SpecimenEvent table, which is accessed through the [history] links in the individual vial view or directly via the SpecimenEvent table in the schema browser.

The subset of fields that should be constant across locations (such as vial ID, vial volume, etc.) are also inserted into the study.SpecimenDetail table.

Fields that are not expected to be the same across location (freezer information, for example) are found only in study.SpecimenEvent.

Aggregating up one more level from study.SpecimenDetail, the study.SpecimenSummary table (accessible via the 'group by vial' links in LabKey Server) summarizes the data by collection. This table contains all fields from study.SpecimenDetail that are expected to be the same across all vials from a given collection (such as subject ID, visit, type, and draw timestamp).

Note that study.SpecimenDetail and study.SpecimenSummary also contain a number of calculated fields that are not found in the import data. These fields are used in a variety of places within the system.