This table contains one row for each kit or protocol that may be used to assay for the concentration of a hormone in a sample. In addition to general information about the kit or protocol (e.g. the assayed hormone, description/discussion), this table also records information regarding how different kits/protocols for the same hormone can be compared to each other.
The Correction column
indicates how — or if — results from this
kit/protocol should be used with results from other
kits/protocols that assay the same hormone. To do this, it
indicates what adjustments should be made to correct the "raw"
results from this kit. The ESTROGENS, GLUCOCORTICOIDS, HORMONE_RESULTS, PROGESTERONES,
TESTOSTERONES and THYROID_HORMONES views all use this column and the
corrected_hormone() function to calculate a
"corrected" concentration, so all values in this column must
be legal correction inputs
for that function. Specifically: the text in this column must
be readable as a mathematical expression for how the "raw"
value should be adjusted, and when referring to the "raw"
value, the string %s must be used. See
corrected_hormone() for examples
of how these values should be recorded.
The system will return a warning for any rows whose
Correction does not include a
%s.
When no correction is needed, do not set the Correction column to NULL. "No
correction needed" is indicated with a Correction of
%s.
When the Correction
column is NULL, this is interpreted to mean that it is
unknown how to use or compare the kit's results with data from
other kits. In related views that provide a "corrected"
concentration, this corrected concentration for these results
may be NULL (as in HORMONE_RESULTS), or
the result may be omitted entirely (as in ESTROGENS, GLUCOCORTICOIDS,
etc.).
Example 4.2. Kits with no Correction, and NULL Correction
For a few years, the concentration of "hormone X" was
measured using kits made by the Mojo Jojo corporation. A
few years later, the Mojo Jojo kit was discontinued, and
Hormone X was instead measured using a kit from Utonium,
Incorporated. Analyses suggested that results from the
Utonium kit are more accurate than the Mojo Jojo kit. The
Utonium kit's Correction
would therefore be set to %s, and ideally
the Mojo Jojo kit's data would be corrected to allow
comparison with the Utonium data. However, the actual
measurements from the two kits are highly inconsistent with
each other, so a reliable correction factor for the Mojo
Jojo kit cannot be calculated. Because of this, the Correction column is set to NULL
for the Mojo Jojo kit. In a view showing concentrations of
hormone X (similar to how ESTROGENS shows
concentrations of estrogen), assay results from the Utonium
kit are included, while results from the Mojo Jojo kit are
omitted.
A unique identifier for this kit or protocol. This is an automatically generated sequential number that uniquely identifies the row.
This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The HORMONE_IDS.Hormone whose concentration is assayed by this kit or protocol.
This column may not be NULL.
A string of text indicating how to "correct" results from this kit/protocol so that its results may be used alongside results from other kits/protocols that assay the same hormone.
This column may be NULL, when an appropriate
correction has not been determined or is not
possible.
A textual description or discussion about the kit or protocol, including any miscellaneous comments or notes.
This column may be NULL. This column may not be empty, it must contain characters,
and it must contain at least one non-whitespace character.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
This table contains one row for every laboratory preparation that was performed on a sample as part of a specific series. Each preparation is recorded with a date, and textual comments may be also be noted.
Always use the HORMONE_PREPS view in place of this table. It contains additional related columns which may be of interest.
Each row's preparation must have taken place after the hormone sample was freeze-dried and sifted. That is, each row's Procedure_Date cannot be before the related HORMONE_SAMPLE_DATA.FzDried_Date and Sifted_Date columns.
The freeze-drying and sifting of fecal samples that are recorded in HORMONE_SAMPLE_DATA are arguably preparatory procedures. Those preparations are not included here because they affect the whole sample. Recording them in this table would incorrectly indicate that the effect of those preparations is limited to a single series.
The procedure cannot occur more than once in the same series. That is, the Procedure must be unique to the HPSId.
The system will return a warning if an ethanol extraction is recorded in the same series as any other preps[180].
A unique identifier for this preparation. This is an automatically generated sequential number that uniquely identifies the row.
This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The HORMONE_PREP_SERIES.HPSId of the series to which this preparation belongs.
This column may not be NULL.
The HORMONE_PREP_PROCEDURES.Procedure performed for this preparation.
This column may not be NULL.
The date on which this preparation finished. If a preparation spans multiple days, then the latest date should be used here.
This column may be NULL, when the date is
unknown.
Comments or miscellaneous information about this preparation.
This column may be NULL. This column may not be empty, it must contain characters,
and it must contain at least one non-whitespace character.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
This table contains one row for each series of laboratory preparations and hormone measurements performed on a specific sample.
It seems worthwhile to use an example to illustrate the meaning of a "series" in this context.
Example 4.3. A familiar "series" of events
One day, Little Miss Muffet decides she wants to have a snack. She gets a bowl of curds and whey, sits down on a tuffet, and proceeds to eat. Soon a spider comes along and sits next to her, frightening her and causing her to run away. Video dramatizations of the event often show her dropping, spilling, or otherwise losing the curds and whey.
Taken together, these events comprise a "series" whose component events could be divided into two groups: preparatory events (get food, sit on tuffet, get scared by spider) and results (run away, lose food). Each preparatory event is preparatory for both of the results, and neither result is dependent or contingent on the other result.
To accurately record these events in a database, Miss Muffet's three preparatory events should be connected to each of the two results, and vice versa. Ideally, separate tables of preparations and results should be in a "one-to-one" or "one-to-many" relationship, but the nature of these data prohibits such an arrangement. This inconvenient "many-to-many" relationship can be addressed by designating each of the many events as components of a single "series".
Similar to Miss Muffet, the process of measuring hormones extracted from a tissue sample is divided into preparatory procedures (in HORMONE_PREP_DATA) and results (in HORMONE_RESULT_DATA), and multiple preparation events may apply to each of multiple results. Their troublesome "many-to-many" relationship is managed by this table, in which related events are grouped into a single series.
This table includes a Series column, which
identifies the series for the sample. The first row for a
sample in this table must have a Series of
1, the second should be
2, and so on. To allow editing or
reordering this value, this rule is only checked after the
current transaction is committed.
A unique identifier for this series. This is an automatically generated sequential number that uniquely identifies the row.
This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The HORMONE_SAMPLE_DATA.TId of the sample that was worked on in this series.
This column may not be NULL.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
This table contains one row for the result of every assay performed to measure a hormone concentration in a sample. In addition to the assay's result — the concentration in nanograms of hormone metabolite per gram of fecal sample — each row records the series (from HORMONE_PREP_SERIES) to which this result belongs, the date of the assay, the identity of the "kit" or protocol used to perform the assay, and the mass of sample from which this hormone was extracted. Textual comments may be also be noted.
There are many views that may be preferred in place of this table. The HORMONE_RESULTS view adds some related columns that make this table more legible for human consumption. Also, there are several views dedicated to specific hormones (ESTROGENS, GLUCOCORTICOIDS, PROGESTERONES, TESTOSTERONES, THYROID_HORMONES) that show assay results with relevant information about the sample and any preps involved with generating the result.
Each row's assay must have taken place after all related preparatory procedures were performed. That is, each row's Assay_Date cannot be before the Procedure_Date of any HORMONE_PREP_DATA rows from the same series (with the same HPSId).
When assay results are generated in the lab, a sample may undergo more than one assay for the same hormone. When there are multiple results, it becomes necessary to determine what the "right" concentration for the sample actually is. Often, it may be best to use the average of all results. In some cases, the results from one kit may be universally preferred over results from another kit. And so on. Laboratory and data managers are presumed to be better-qualified to make such decisions, so those decisions should be made before adding data to this table. To help ensure that this is occurring, a hormone sample cannot have more than one result for each hormone, regardless of the series and the Kit. That is, the combination of the related HORMONE_PREP_SERIES.TId and HORMONE_KITS.Hormone must be unique[181].
The mass of fecal sample that was extracted and measured
in the assay is recorded in the Grams_Used column. This column
should not be NULL. However, in some rare circumstances the
mass can be unknown, in which case the column will be
NULL. Regardless, this is expected to be rare. The system
will return a warning for any assay whose Grams_Used is NULL.
A unique identifier for this assay result. This is an automatically generated sequential number that uniquely identifies the row.
This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The HORMONE_PREP_SERIES.HPSId of the series to which this assay result belongs.
This column may not be NULL.
The date that the assay was performed.
This column may be NULL, when the date is
unknown.
The initial mass of fecal sample from which the assayed hormone was extracted.
This column may be NULL, when the mass is
unknown.
The "raw" concentration of the hormone, in nanograms hormone per gram of fecal sample, determined by this assay.
This column may not be NULL.
Comments or miscellaneous information about this assay.
This column may be NULL. This column may not be empty, it must contain characters,
and it must contain at least one non-whitespace character.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
This table contains one row for every tissue sample used for hormone analysis; generally for hormone analysis these will be fecal samples. For each sample, the table records data that are only relevant to hormone analysis and would thus be inappropriate for inclusion in the TISSUE_DATA table.
Always use the HORMONE_SAMPLES view in place of this table. It contains additional related columns which may be of interest.
For various logistical reasons, it is often not practical for lab personnel to use the database's unique identifier for a sample (its TId) in the lab. Instead, they use a system of their own. The unique identifier used by hormone lab personnel — the "Hormone Sample ID" — is indicated in the HSId column[182].
To analyze a fecal sample for its hormone content, the sample must first be freeze-dried. Following that, the dry sample is sifted into a fine powder, at which point it is ready for whatever preparations are necessary for hormone analysis. The dates that the fecal sample is freeze-dried and sifted are recorded in this table, in the FzDried_Date and Sifted_Date columns, respectively.
A fecal sample cannot be sifted before it is freeze-dried; its Sifted_Date must be on or after its FzDried_Date.
This table attempts to keep an ongoing record of a fecal
sample's remaining mass in the Avail_Mass_g column. It is
left to the user to judge this column's accuracy, which
depends greatly on how diligently the lab personnel keep the
data manager(s) informed of changes. To assist users in making
these judgments, the date that the Avail_Mass_g was last updated
is recorded in the Avail_Date column. A sample's
remaining mass cannot be recorded without also recording this
date; the Avail_Mass_g
and Avail_Date columns
must both be NULL or both non-NULL.
Preparing a fecal sample for hormone extraction and any subsequent handling of the sample must be after the sample was collected. That is, all dates in this table (FzDried_Date, Sifted_Date, Avail_Date) must be after the sample's related TISSUE_DATA.Collection_Date.
The TISSUE_DATA.TId of the tissue sample and the unique identifier for the row.
This column cannot be changed and must not be
NULL.
The "Hormone Sample ID", another unique identifier for this sample. This is a number, created and maintained by lab personnel.
This column may not be NULL.
The date that this sample was freeze-dried.
This column may be NULL, when this date is
unknown.
The date that the freeze-dried sample was sifted.
This column may be NULL, when this date is
unknown.
The mass of this sample, in grams, that is available for use as of the Avail_Date.
This column may be NULL, when the remaining mass
(if any) is unknown.
The date that the Avail_Mass_g was determined.
This column may be NULL, when the remaining mass
(if any) is unknown.
Comments or miscellaneous information about this sample.
This column may be NULL. This column may not be empty, it must contain characters,
and it must contain at least one non-whitespace character.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
A table listing each analysis that has been performed to generate genetic hybrid scores for individual baboons, with basic information about each analysis.
Each analysis combines statistical techniques with the genetic data available at the time to estimate what proportion of each individual's genome came from ancestry of a specified other species[183]. These estimates are the so-called “hybrid scores”. After several years have elapsed, more individuals are available for scoring, which prompts a new analysis. For many reasons, each analysis may yield somewhat different scores for the same individual. A more-recent analysis does not necessarily negate or supersede an older one, so all analyses are stored here.
The HYBRIDGENE_ANALYSES.Date must be after the BIOGRAPH.Entrydate of all individuals scored in that analysis in HYBRIDGENE_SCORES. Similarly, the system will return a warning for each individual scored in an analysis where the related Date is before the individual's LatestBirth.
A unique integer that identifies the analysis.
This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The LAB_PERSONNEL.Initials of the person who performed the analysis.
It's technically possible to have more than one person involved with an analysis, but even in such cases there will certainly be a lead whose initials should fill this column.
This column may not be NULL.
The HYBRIDGENE_SOFTWARE.Software used to perform the analysis.
This column may not be NULL.
Notes or comments about the analysis.
This column may not be empty, it must contain characters, and it must contain at least one non-whitespace character.
This column may be NULL.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
A table listing all the hybrid scores determined by genetic hybridity analyses. Hybridity analyses use statistical tools that may also determine upper and lower confidence intervals[184]. This table also stores those values, if any.
The combination of Sname and HGAId must be unique.
In some analyses, upper and lower confidence intervals
are not generated, in which case the Upper_Conf and Lower_Conf will be NULL. The
system will return a warning in this case.
If either Upper_Conf
or Lower_Conf is provided,
then the other must also; the Upper_Conf and Lower_Conf must both be NULL
or both non-NULL.
When the confidence columns are not NULL, the
individual's Score must be
greater than its Lower_Conf (inclusive), and less
than its Upper_Conf
(inclusive).
A unique integer that identifies the row.
This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The HYBRIDGENE_ANALYSES.HGAId of the analysis in which this score was determined.
This column may not be NULL.
The individual's hybrid score for this
analysis. This value must be between 0
and 1 (inclusive).
This column may not be NULL.
The lower confidence interval for the hybrid
score. This value must be between 0 and
1 (inclusive).
This column may be NULL, but only when the
analysis did not generate a lower confidence.
The upper confidence interval for the hybrid
score. This value must be between 0 and
1 (inclusive).
This column may be NULL, but only when the
analysis did not generate a higher confidence.
The timestamp range during which this row's data are considered valid. See The Sys_Period Column for more information.
[180] Because in all current (as of this writing) laboratory protocols, methanol and solid-phase extractions are always in the same series, and ethanol extraction is always part of another.
[181] If this restriction is ever lifted, the hormone-specific views (e.g. ESTROGENS) will not be guaranteed to be one-row-per-sample. This isn't necessarily a problem, but it's a downstream effect that may not be immediately obvious and seems worth noting.
[182] For other examples of this, see the NUCACID_LOCAL_IDS and TISSUE_LOCAL_IDS tables, and the WP_REPORTS.WId column).
[183] Usually the olive baboon, Papio anubis.
[184] For discussion in this table, we use the term, "confidence interval" generally. It may not necessarily be an actual "confidence interval" as a statistician would use that term. The confidences recorded in this table may actually be another kind of interval, or another kind of confidence.