• 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 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.site 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.additive 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 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.vial 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.vial, the study.specimen table (accessible via the 'group by vial' links in LabKey Server) summarizes the data by collection. This table contains all fields from study.vial 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.vial and study.specimen 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.