The data model

The Arctos data model

This is an overview of the Arctos data model, with key tables, and how some of the key data elements appear in the flat table. It is not a full ER diagram, more a map of the territory. The diagram is also available as a PDF with clickable links to the relevant table documentation in Arctos.

Key:

• outline boxes: represent the panels within the Arctos catalog record page
• colored tables: primary tables in the Arctos DB
• p. key: primary key in the main, original data table
• f. key(s): foreign keys in the original data table
• fields: other primary fields in the original data table
• flat: the fields in flat which represent data from the original table
• Edges:
  • n ➡ 1: multiple records of the first table relate to a single record in the target table
  • n ↔ n: multiple records of the first table relate to multiple record in the target table
  • 1 ↔ 1: a single record of the first table relates to a single record in the target table

The diagram was made with GraphViz. Source.

Mapping ALA physical objects to Arctos data model

(In the following, Arctos concepts are in bold type, physical objects or concepts are in italic type, and identifiers are in monospaced type)

• SPECIMENS
  • The physical unit that is represented by the Arctos Catalog Record is the Specimen (aka Collection).
  • Each specimen has a unique ALAAC (ALA accession number), so there should be a one-to-one mapping of Arctos GUID to ALAAC.
• PARTS
  • Each Specimen may contain one or more elements of several kinds:
    • a whole organism (common for herbs), or
    • a part of an organism (leaf, twig, flower, e.g., from a tree), or
    • a paper label.
  • These physical elements are represented in Arctos as Parts.
  • Each Part is of a type, controlled by the ctspecimen_part_name code table.
  • At a minimum, every Arctos Record has one Part, of default type whole organism.
  • In reality, there may be several whole organisms in a collection (common for small, alpine plants). These may be modeled as separate Parts in Arctos if it is useful, but usually are represented as a single Part of type whole organism.
  • Cases where it is useful to indicate separate elements as additional Parts (see this specimen for a full set of elements)
    • A part of an organism, e.g., a leaf (Part type: leaf) has been sent for destructive sequencing,
    • A part of an organism, e.g., a flower (Part type: flower) exists in an alcohol collection.
    • A DNA sample exists separate from the specimen, in a local or external lab (Part type: DNA).
    • A physically large plant is cut up and pasted to several herbarium sheets (Part types: whole organism and/or leaf)
    • There is an annotation label on the herbarium sheet, other than a ‘det slip’ (which are recorded in Arctos as additional Identifications). This is represented as a Part of type media.
• CONTAINERS
  • Physically, all elements of a collection exist somewhere, usually attached to a single herbarium sheet.
  • Arctos models this by allowing each Part to be contained in a Container. Usually a whole organism occurs in/on a Container of type herbarium sheet.
  • Each Container may have a barcode, but Parts do no themselves have barcodes.
  • Different Parts of one Catalog record may exist in different Containers
  • NOTE: In rare cases, a single Container may contain Parts from different Records. For example, rarely two individuals of different species were attached to the same herbarium sheet (and so cannot be called a single specimen). They should be assigned to different Records with different GUID and ALAAC, even though the collector and event info is the same. Each of the Records will have Parts that share the same herbarium sheet Container (and barcode) with the other Record.
  • Containers themselves can be housed hierarchically in other Containers which each have barcodes and descriptions. This permits herbarium sheets to be found: cabinet > shelf > folder > sheet.
  • To enter data for a large specimen than has been split up and attached to several herbarium sheets, a single Catalog Record with a single ALAAC should have several Parts, with each of these Parts housed in a different Container of type herbarium sheet, each with a different barcode.
  • A wet collection of a flower should be a separate Part of a Record, with type flower, and contained in a Container of type vial.

Identifiers

Using the above model, the following should be true:

1. Each GUID should have a single ALAAC (note: unique ALAACs are not enforced by Arctos)
2. Rarely, each GUID and ALAAC may have one or more barcodes
3. Rarely, a single barcode may be associated with more than one GUID and ALAAC

Anny cases of multiple ALAAC for a single GUID should be investigated and duplicate records deleted. Because cases (2) and (3) above are rare, they should be checked manually.