Tables

Dictionary v2.2.3 Documentation v2.1.0

The ODM database model has 21 tables in its full relational database. The full model is commonly referred to as “long” tables as it stores data with one measurement per row. Each table has mandatory and optional headers.

Data storage

Store all ODM data in the 21 recommended tables to ensure optimal interoprablity and support by the ODM validation toolkit and other ODM libraries.

Users can, however, create custom tables or ‘views’ by merging fields from any table. A common format for data entry format are “wide” table format, with one day per row and multiple measures or attributes per row. It is important to note that this guide does not provide instructions on generating wide tables.

Table types

There are five table types:

1) Regular report tables. The measures and samples tables are used for daily reporting of new measurements and information on sample collection. There are three tables that support regular reporting:- measureSets (optional) is used to link measures, sampleRelationships (optional) is used when samples are pooled or split, and qualityReports is used for detailed quality assurance and control measures.

2) Contact information tables. The sites, organizations, contacts, and addresses tables store contact information for the testing site and the person who performed the testing.

3) Protocol or methods tables. The protocols and instruments tables hold information on the methods used for sample collection or measurement. protocolSteps and protocolRelationships tables are used in conjunction with the protocols table.

4) Other report tables. The polygons table stores information on the sample area and the datasets table includes information on the data custodian and owner.

5) Dictionary reference tables. The parts table describes all elements of the ODM, including tables, table headers, measures, methods, categories, and units. The sets table stores collections of parts, such as units grouped together in a unitSet. The languages and translations tables support translations. The countries and zones tables standardize location information to the ISO-2 and ISO-3 to reduce ambiguity and free-text errors when populating these fields, while improving interoperability of location metadata.

Header roles

Each table in the ODM has column headers, also known as table variables, fields, or entity relationship attributes. The header serves as the top row and contains the variable name.

Primary key (PK) - With the exception of the dictionary tables, all tables have one primary key. The primary key serves as a unique identifier for each row in the table, meaning that a primary key value cannot be repeated.

Foreign key (FK) - Tables may have one or more foreign keys. A foreign key connects an entry to another table with a primary key. This allows for relationships to be established between tables, making it possible to link data between different tables.

Header (header) - This label is used in a table description when a header is not a primary or foreign key.

Mandatory, optional, mandatoryIf

Each header may be classified as either mandatory or optional. In the measures table, examples of mandatory headers are the measureID and measurement date. The ODM validation toolkit can be used to validate data, and will return an error if a table lacks a mandatory header or if there are missing row entries for mandatory fields.

Additionally, there are special headers called mandatoryIf headers. These headers are mandatory if certain conditions apply. For example, the protocol steps table has row entries that are either measures or methods. If the row entry is a measure, then a unit becomes mandatory since all measures must have a unit. Therefore, units are mandatoryIf in the protocol steps table.

Table list

Address table

partID: addresses. The table that contains information about addresses. Addresses can recorded for sites, organizations or contacts (indivduals). The Sites, Organizations, and Contacts tables include a link to the addresses table through Address ID.
  • Address Line 1 (addL1). Line 1 (the street name, number and direction) for a given address.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Address Line 2 (addL2). Line 2 (the unit number) for a given address.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • City (city). The city where a site or organization is located; part of the address.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • State, Province, or Region (stateProvReg). The state, province, or region where a site or organization is located; part of the address.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Postal or Zip Code (pCode). The zip code or postal code for a given address, specifying a specifc geographic area.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • ISO 3166-1 alpha-2 country code (isoCode). The ISO 3166-1 alpha-2 code, a two-letter country code which is also used to create the ISO 3166-2 country subdivision code and the Internet country code top-level domain.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • ISO 3166-2 code for country sub-domain (isoZone). The ISO 3166-2 codes for the names of the principal subdivisions (e.g., provinces, states, departments, regions) of all countries coded in ISO 3166-1.
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Contact table

partID: contacts. The table that contains information about a contact; a person who is the contact of a site or laboratory. adapt from verson 1 documentation
  • First name of contact (firstName). Specifies the first name of a given contact.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last name of contact (lastName). Specifies the last name of a given contact.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Contact email (email). Contact e-mail address, for the lab.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Contact phone (coPhone). Contact phone number, for the lab.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Role of contact (role). Specifies the organizational role of a given contact.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Countries look-up tables

partID: countries. Look up table for the possible country inputs.
  • ISO 3166-1 alpha-3 country code (isoCodeX). The ISO 3166-1 alpha-3 code, a three-letter country code which may allow a better visual association between the code and the country names than the 3166-1 alpha-2 code.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • ISO 3166-1 numeric country code (numCode). The ISO 3166-1 numeric code, a three-digit country code which is identical to that developed and maintained by the United Nations Statistics Division, with the advantage of script (writing system) independence, and hence useful for people or systems using non-Latin scripts.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Country code top-level domain (tld). The internet top-level domain generally used or reserved for a country, sovereign state, or dependent territory identified with a country code.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • English name for countries (nameEngl). English-language name of a given country.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Official state name (nameOfficial). Official english-language name of a given state.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Sovereign status of a country (sovereignty). Sovereign status of a country, indicating which state has the highest jurisdiction over a given territory.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Country exonym (countryExonym). The English name foreigners use for the country.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Capital city exonym (capitalExonym). The English name foreigners use for the country‚Äö√Ñ√¥s capital.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Country endonym (countryEndonym). The name locals use for their country.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Capital city endonym (capitalEndonym). The name locals use for their country‚Äö√Ñ√¥s capital.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • National language script (langScript). The language(s) and script(s) used for the country‚Äö√Ñ√¥s capital endonyms.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Country national phone prefix (phone). International dialing code for the country.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Coordinated universal time (UTC) zone (utc). UTC ‚Äö√Ñ√¨ Time zone.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Coordinated universal time (UTC) zone in daylight savings (utcDST). Time zone during Daylight Saving Time.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.

Dataset table

partID: datasets. A report table for capturing details about data’s parental data set and data custodians. Supplying attribution for data collectors.
  • Dataset creation date (datasetDate). Specifies the date a given dataset was created.
    Role: Header.
    Requirement: recommended.
    Data type: datetime.
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: recommended.
    Data type: varchar.
  • Description (descr). A detailed description of a measure, method, or attribute.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Instrument table

partID: instruments. The table that contains information about instruments. adapt from verson 1 documentation
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: recommended.
    Data type: varchar.
  • Model (model). Model number or version of the instrument.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Manufacturer (manufacturer). Manufacturer of an instrument.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Description (descr). A detailed description of a measure, method, or attribute.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Describe other instrument type, if applicable (insTypeOth). Description of the instrument in case it is not listed in instrumentType.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Index (index). Index number in case the measurement was taken multiple times.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Language Look-up table

partID: languages. Look up table for all languages, used to give structure to the translation table.
  • Language family (langFam). Specifies the language family of a given language for translation and language tracking purposes.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Language name (langName). Specifies the name of the language in roman alphabet characters for translation and language tracking purposes.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Native Name (natName). The native name of the language, i.e. what the language is called by its speakers.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • ISO639-1 (iso6391). The first part of the ISO 639 series of international standards for language codes. Part 1 covers the registration of two-letter codes. There are 183 two-letter codes registered as of June 2021. The registered codes cover the world’s major languages.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • ISO639-2B (iso6392B). A set of international standards that lists short codes for language names. These ISO639-2 are the three-letter codes defined in part two (ISO 639-2) of the standard, including the corresponding two-letter (ISO 639-1) codes where they exist. The ‘B’ specifies the bibliographic code (B code).
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • ISO639-2T (iso6392T). A set of international standards that lists short codes for language names. These ISO639-2 are the three-letter codes defined in part two (ISO 639-2) of the standard, including the corresponding two-letter (ISO 639-1) codes where they exist. The ‘T’ specifies the terminological code (T code).
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • ISO639-3 (iso6393). A set of international standards that lists short codes for language names. ISO 639-3 extends the ISO 639-2 alpha-3 codes with an aim to cover all known natural languages.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • ISO639-6 (iso6396). A set of international standards that lists short codes for language names. ISO 639-6 builds off ISO639-3 with the use of four-letter codes, and allowing users to differenciate between variants of languages and language families, such as histroical vs. revived versions of languages.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • First released version (firstReleased). The version in which a part was first released
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Last updated version (lastUpdated). The version in which the part was last updated.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Changes column (changes). A column for recording the changes from a previous version.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Measure report table

partID: measures. The table that contains information and details about a given measure adapt from verson 1 documentation
  • Analysis date start (aDateStart). Date the measurement or analysis was started.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Analysis date end (aDateEnd). Date the measurement or analysis was completed.
    Role: Header.
    Requirement: mandatory.
    Data type: datetime.
  • Report date (reportDate). The date a measure was reported.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Value (value). Value of a measure, observation or attribute.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Index (index). Index number in case the measurement was taken multiple times.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Measure license (measureLic). Specifies the access and use licensing for a given single measurement.
    Role: Header.
    Requirement: optional.
    Data type: categorical.
    Measure, method or attribute set: licSet
  • Reportable (reportable). Flag for whether a measure is reportable or not, based on confidence in the measure and methods applied.
    Role: Header.
    Requirement: optional.
    Data type: boolean.
  • Organization ID (organizationID). A unique identifier for the organization to which the reporter is affiliated.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Contact ID (contactID). A unique identifier for a given contact person.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Measure set report table

partID: measureSets. The table that identifies sets of measures. Examples of measure sets include a set of replicates, dilutions (used to generate a Ct curve) or varients that are identified in a single sample.
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Organization table

partID: organizations. The table that contains information about a laboratory. adapt from verson 1 documentation
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: recommended.
    Data type: varchar.
  • Description (descr). A detailed description of a measure, method, or attribute.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Organization Type (orgType). Specifies the type or purpose of a given organization.
    Role: Header.
    Requirement: recommended.
    Data type: categorical.
    Measure, method or attribute set: orgTypeSet
  • Organization level (orgLevel). The geographic level of an organization.
    Role: Header.
    Requirement: recommended.
    Data type: categorical.
    Measure, method or attribute set: orgLevelSet
  • Organization sector (orgSector). The sector of an organization
    Role: Header.
    Requirement: recommended.
    Data type: categorical.
    Measure, method or attribute set: orgSectorSet
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Parts Look-up table

partID: parts. Look up table containing all parts in of the data model. Contains all parts, including self-referential parts.
  • Label (label). A human readable label of a part.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Part types (partType). Part types describe the purpose or use of the part.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • Short names (shortName). Shortened names for tables and other important parts for use in wide names.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Part description (partDesc). The description of the part.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Part instruction (partInstr). Additional notes and instructions on how a part is used and/or defined.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Domain (domain). Domain is the highest level of describing of a measure.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • Group (group). Unique identifier for a group of measures. Mostly applicable for measures, methods, units, and aggregations.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • Class (class). A unique identifier for a class, which is akin to a sub-group; it’s a way of grouping parts within a given group. A group can have one or more classes to describe different parts of the class.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • Nomenclature (nomenclature). A classification system to report the measure class. Only applicable to variants, mutations, and diseases.
    Role: Header.
    Requirement: optional.
    Data type: categorical.
  • Ontology reference (ontologyRef). Ontology reference for a part.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • LaTeX expression (latExp). LaTeX expression used to generate formulas, symbols, etc. Mainly relevant for units.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Aggregation scale (aggragationScale). A scale used for an aggregation. Only applicable for measures and units.
    Role: Header.
    Requirement: optional.
    Data type: categorical.
  • Status (status). Whether the part is still active and can be used in the most current ODM version. Values are ‘active’ or ‘inactive’.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • First released version (firstReleased). The version in which a part was first released
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Last updated version (lastUpdated). The version in which the part was last updated.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Changes column (changes). A column for recording the changes from a previous version.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol steps table (protocolSteps). The table for collecting metadata on individual steps in a protocol, methodological process, or assay.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol Relationship table required headers (protocolRelationshipsRequired). Specifies the columns required in the Protocol Organization table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol steps table required headers (protocolStepsRequired). Specifies the columns required in a Protocol Steps table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol steps table column order (protocolStepsOrder). Specifies the order of the columns in a Protocol Steps table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Protocol relationships table (protocolRelationships). The table that contains the organizational information and details (such as order) about a given protocol, recorded as a series of protocol steps (protocolSteps).
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol relationships table column order (protocolRelationshipsOrder). Specifies the order of the columns in the Protocol Organization table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Measure report table (measures). The table that contains information and details about a given measure
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Measures table required headers (measuresRequired). Specifies the columns required in a Measures table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Measures table column order (measuresOrder). Specifies the order of the columns in a Measures table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Measure set report table (measureSets). The table that identifies sets of measures.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Measure sets table column order (measureSetsOrder). Specifies the order of the columns in a Measure Sets table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Measure Set table required headers (measureSetsRequired). Specifies the columns required in a Measure Sets table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Dataset table (datasets). A report table for capturing details about data’s parental data set and data custodians. Supplying attribution for data collectors.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Dataset table required headers (datasetsRequired). Specifies the columns required in a Datasets table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Datasets table column order (datasetsOrder). Specifies the order of the columns in a Datasets table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Sites table (sites). The table that contains information about a site; the location where an environmental sample was taken.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sites table required headers (sitesRequired). Specifies the columns required in a Sites table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sites table column order (sitesOrder). Specifies the order of the columns in a Sites table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Sample report table (samples). The table that contains information about a sample. A sample is defined as a representative volume of wastewater (or other forms of water or liquid), air, or surface area taken from a site.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sample table required headers (samplesRequired). Specifies the columns required in a Samples table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Samples table column order (samplesOrder). Specifies the order of the columns in a Samples table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Address table (addresses). The table that contains information about addresses. Addresses can recorded for sites, organizations or contacts (indivduals).
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Address table required headers (addressesRequired). Specifies the columns required in a Addresses table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Addresses table column order (addressesOrder). Specifies the order of the columns in the Addresses table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Contact table (contacts). The table that contains information about a contact; a person who is the contact of a site or laboratory.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Contact table required headers (contactsRequired). Specifies the columns required in a Contacts table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Contacts table column order (contactsOrder). Specifies the order of the columns in a Contacts table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Organization table (organizations). The table that contains information about a laboratory.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Organziation table required headers (organizationsRequired). Specifies the columns required in a Organizations table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Organizations table column order (organizationsOrder). Specifies the order of the columns in a Organizations table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Instrument table (instruments). The table that contains information about instruments.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Instrutment table required headers (instrumentsRequired). Specifies the columns required in the Instruments table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Instruments table column order (instrumentsOrder). Specifies the order of the columns in the Instruments table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Polygon table (polygons). The table that contains information about the geometry of a geographic area.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Polygon table required headers (polygonsRequired). Specifies the columns required in a Polygons table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Polygons table column order (polygonsOrder). Specifies the order of the columns in a Polygons table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Language Look-up table (languages). Look up table for all languages, used to give structure to the translation table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Language table required headers (languagesRequired). Required headers in the Languages table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Language table column order (languagesOrder). Specifies the order of the columns in the Languages table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Translation look-up table (translations). Look up table for translations of the description, label, and instruction for all parts.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Parts Look-up table (parts). Look up table containing all parts in of the data model.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Translation table required headers (translationsRequired). Required headers in the Translations table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Parts table column order (partsOrder). Order of headers in the Parts table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Translation table column order (translationsOrder). Specifies the order of the columns in the Translation table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Parts table required headers (partsRequired). Required headers in the Parts table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sets look-up table (sets). Look up table for all sets, managing how categorical inputs for various methods and attributes are grouped together.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sets table column order (setsOrder). Specifies the order of the columns in the Sets table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Sets table required headers (setsRequired). Required headers in the Sets table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Quality reports table (qualityReports). The table for recording the various quality metrics and indicators for samples and measures.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Quality report table required headers (qualityReportsRequired). Specifies the columns required in the Quality Reports table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Quality rerpots table column order (qualityReportsOrder). Specifies the order of the columns in the Quality Reports table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Sample relationships table (sampleRelationships). Table for recording the relationships between samples.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sample relationships table required headers (sampleRelationshipsRequired). Specifies the columns required in a Sample Relationships table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sample relationships table column order (sampleRelationshipsOrder). Specifies the order of the columns in a Sample Relationships table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Protocols table (protocols). The table for protocols.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocols table required headers (protocolsRequired). Specifies the columns required in the Protocols table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocols table column order (protocolsOrder). Specifies the order of the columns in the Protocols table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Countries look-up tables (countries). Look up table for the possible country inputs.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Counries table required headers (countriesRequired). Required headers in the countries table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Countries table column order (countriesOrder). Specifies the order of the columns in the countries table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Zones look-up table (zones). Look up table for the possible sub-national region or zone inputs.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Zones table required headers (zonesRequired). Required headers in the zones table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Zones table column order (zonesOrder). Specifies the order of the columns in the zones table.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Wide name table (wideNames). The table for wide names.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Wide name table required (wideNamesRequired). Column for indicating which fields in the wide names table are mandatory.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Wide name table order (wideNamesOrder). Column for indicating the order of the fields in the wide names table.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Data types (dataTypes). The data type for a part.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • Minimum value part support (minValue). The minimum value of part.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Maximum value part support (maxValue). The maximum value of a part.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Minimum length (minLength). The maximum value of measure.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: integer.
  • Maximum length (maxLength). The maximum length of the value of a part or measure.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: integer.

Polygon table

partID: polygons. The table that contains information about the geometry of a geographic area.
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: recommended.
    Data type: varchar.
  • Description (descr). A detailed description of a measure, method, or attribute.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Polygon Population (polyPop). An attribute of a polygon, which specifies the population of that polygon. A rough estimate of the number of human residents.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Type of geography (geoType). Type of geography that is represented by the polygon.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
    Measure, method or attribute set: geoTypeSet
  • European Petroleum Survey Group Coordinates (geoEPSG). The unique EPSG code specifying a given geospatial area.
    Role: Header.
    Requirement: mandatory.
    Data type: float.
  • Well-known text (geoWKT). Well-known text of the polygon
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • File location of polygon (fileLocation). The location of the file containing the geometry of the polygon.
    Role: Header.
    Requirement: optional.
    Data type: blob.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Organization ID (organizationID). A unique identifier for the organization to which the reporter is affiliated.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Contact ID (contactID). A unique identifier for a given contact person.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Protocol relationships table

partID: protocolRelationships. The table that contains the organizational information and details (such as order) about a given protocol, recorded as a series of protocol steps (protocolSteps). Protocols are a group of related protocol steps. For example a protocol step can describe a sample concentration, extraction, inhibition or a measurement. Protocols can include both methodID (a proceedure) and measureIDs (a measure). An example of a method is centrifugation; where the centriguation rotation speed is described as a measure.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Protocols table

partID: protocols. The table for protocols.
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Summary (summ). Short description of the assay and how it is different from the other assay methods.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol version (protocolVersion). Specifies the version of a method set.
    Role: Header.
    Requirement: optional.
    Data type: integer.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Protocol steps table

partID: protocolSteps. The table for collecting metadata on individual steps in a protocol, methodological process, or assay.
  • Summary (summ). Short description of the assay and how it is different from the other assay methods.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Protocol step version (stepVer). Specifies the version of a given protocol step.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Reference link (refLink). Link to the reference material for a part. May link to literature on a method, measure, etc.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Value (value). Value of a measure, observation or attribute.
    Role: Header.
    Requirement: mandatoryif.
    Data type: varchar.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Quality reports table

partID: qualityReports. The table for recording the various quality metrics and indicators for samples and measures.
  • Quality flag (qualityFlag). A field for reporting any quality concerns - of lack thereof - for a sample or measure.
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
  • Severity indicator (severity). An indicator of the severity or seriousness of a quality flag.
    Role: Header.
    Requirement: optional.
    Data type: categorical.
    Measure, method or attribute set: sevSet
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Sample relationships table

partID: sampleRelationships. Table for recording the relationships between samples. Samples can be pooled or split. The sample relationships table holds information on parent-child relationships between samples, and allow for tracking sample lineage for single and pooled samples.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Sample report table

partID: samples. The table that contains information about a sample. A sample is defined as a representative volume of wastewater (or other forms of water or liquid), air, or surface area taken from a site. Samples can be combined, split, stored and reused. The Sample relationships (sampleRelationships) is used to describe the relationships between samples.
  • Organization ID (organizationID). A unique identifier for the organization to which the reporter is affiliated.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Sample origin (origin). An attribute of a sample specifying the origin.
    Role: Header.
    Requirement: optional.
    Data type: categorical.
  • Sample collection type (collType). The type of collection.
    Role: Foreign Key.
    Requirement: mandatory.
    Data type: categorical.
    Measure, method or attribute set: collectSet
  • Collection period (collPer). Collection period. The time period over which the sample was collected, in hours. Alternatively, use collectionStart and collectionEnd.
    Role: Header.
    Requirement: mandatory.
    Data type: float.
  • Collection number (collNum). The number of subsamples that were combined to create the sample. Use NA for continuous, proportional or passive sampling.
    Role: Header.
    Requirement: mandatory.
    Data type: integer.
  • Pooled (pooled). Is this a pooled sample, and therefore composed of multiple child samples obtained at different sites
    Role: Header.
    Requirement: optional.
    Data type: boolean.
  • Collection date time (collDT). For grab samples this is the date, time and timezone the sample was taken.
    Role: Header.
    Requirement: mandatory.
    Data type: datetime.
  • Collection date time start (collDTStart). For integrated time averaged samples this is the date, time and timezone the sample was started being taken.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Collection date time end (collDTEnd). For integrated time average samples this is the date, time and timezone the sample was finished being taken.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Date sample was sent (sentDate). Date that the sample was sent from the collection site to the lab.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Date sample recieved (recDate). The date the sample was received at the laboratory for analysis.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Reportable (reportable). Flag for whether a measure is reportable or not, based on confidence in the measure and methods applied.
    Role: Header.
    Requirement: optional.
    Data type: boolean.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Collection period and number (collNumPer). Composite collection number.period.
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Sets look-up table

partID: sets. Look up table for all sets, managing how categorical inputs for various methods and attributes are grouped together. This table manages many-to-many relationships and category recycling within the ODM.
  • Label (label). A human readable label of a part.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Enumeration for set values (enumeration). The numeric value that corresponds to a given value in a set, defined in the sets table.
    Role: Header.
    Requirement: mandatory.
    Data type: integer.
  • Status (status). Whether the part is still active and can be used in the most current ODM version. Values are ‘active’ or ‘inactive’.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • First released version (firstReleased). The version in which a part was first released
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Last updated version (lastUpdated). The version in which the part was last updated.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Changes column (changes). A column for recording the changes from a previous version.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Sites table

partID: sites. The table that contains information about a site; the location where an environmental sample was taken. The site of an eviromental sample. Information in the site table does not regularly change. Consider using the MeasureReport table if the infomation changes often.
  • Sample shed (sampleShed). A geographic area, physical space, or structure. A sample is taken from a sampleshed for a representative measurement of a substance(s).
    Role: Header.
    Requirement: mandatory.
    Data type: categorical.
    Measure, method or attribute set: shedSet
  • Organization ID (organizationID). A unique identifier for the organization to which the reporter is affiliated.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Name (name). Name of a domain, specimen, group, class, attribute, unit, nomeclature or other entity.
    Role: Header.
    Requirement: recommended.
    Data type: varchar.
  • Description (descr). A detailed description of a measure, method, or attribute.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Primary reporting authority ID (repOrg1). The primary or most responsible authority for rountine surveillance reports or findings, or where the site is located.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Secondary reporting authority ID (repOrg2). The secondary, additional or alternative authority for rountine surveillance reports or findings, or where the site is located.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Health Region for a Site (healthRegion). A free-text variable for listing the health region for a given site.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Population Served (popServ). An attribute of a site, which specifies the population of/population served by a given site.
    Role: Header.
    Requirement: optional.
    Data type: categorical.
  • Latitude (geoLat). Geographical location, latitude in decimal coordinates, ie.: (45.424721)
    Role: Header.
    Requirement: mandatoryIf.
    Data type: float.
  • Longitude (geoLong). Geographical location, longitude in decimal coordinates, ie.: (-75.695000)
    Role: Header.
    Requirement: mandatoryIf.
    Data type: float.
  • European Petroleum Survey Group Coordinates (geoEPSG). The unique EPSG code specifying a given geospatial area.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: float.
  • Last edited (lastEdited). The date the entry was last updated.
    Role: Header.
    Requirement: optional.
    Data type: datetime.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Translation look-up table

partID: translations. Look up table for translations of the description, label, and instruction for all parts. The default language if a translation is not specified is English.
  • Label (label). A human readable label of a part.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Part description (partDesc). The description of the part.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Part instruction (partInstr). Additional notes and instructions on how a part is used and/or defined.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • First released version (firstReleased). The version in which a part was first released
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Last updated version (lastUpdated). The version in which the part was last updated.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Changes column (changes). A column for recording the changes from a previous version.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Notes (notes). A note used to describe details that are not captured in other attrbitutes
    Role: Header.
    Requirement: optional.
    Data type: varchar.

Wide name table

partID: wideNames. The table for wide names.
  • Label (label). A human readable label of a part.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • character length (charLength). The number of characters used for the wide names.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Description (descr). A detailed description of a measure, method, or attribute.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Wide name source (source). The database or table sourced for the wide name.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.
  • Measure identification (wide table) (wideMeasure). Unique identifier for a measure in a wide table only.¬¨‚Ć
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Specified protocol (wide table) (wideProtocol). Unique identifier for a protocol in a wide table only.¬¨‚Ć
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Specified attribute (wide table) (wideAttribute). Unique identifier for an attribute in a wide table only.¬¨‚Ć
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Wide name type (wideNameType). The type of wide name generated. Possible options are measure, protocol or attribute.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Report table name (wide table) (reportTableName). The report table that the wide name calls from.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Part type name (wide table) (partTypeName). The part type being used in a wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Compartment name (wide table) (compartmentName). The compartment referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Specimen name (wide table) (specimenName). The specimen referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Fraction name (wide table) (fractionName). The fraction analyzed referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Measure name (wide table) (measureName). The measure referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Method name (wide table) (methodName). The method referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Unit name (wide table) (unitName). The unit referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Aggregation name (wide table) (aggregationName). The aggregation referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.
  • Index (index). Index number in case the measurement was taken multiple times.
    Role: Header.
    Requirement: optional.
    Data type: varchar.
  • Attribute name (wide table) (attributeName). The attribute referenced for the wide name.
    Role: Header.
    Requirement: mandatoryIf.
    Data type: varchar.

Zones look-up table

partID: zones. Look up table for the possible sub-national region or zone inputs.
  • English name of country sub-domains (zoneName). The english-language name for a given country sub-domain.
    Role: Header.
    Requirement: mandatory.
    Data type: varchar.

Page created on Tue Oct 29 00:00:00 2024.