Diversity Descriptions
The following objects are not included:
- Logging tables
- Enumeration tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
TABLES
Table Annotation
One record per annotated object (Char, State, Descr. etc.)
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| object_id | int | Reference to object to which the annotation belongs, references BaseEntity (foreign key) | NO | Refers to table BaseEntity |
| language_code | nvarchar (3) | Language of annotation text (ISO 639 language code) | NO | - |
| contributor_id | int | Contributors of annotations may be different from contributors of the object that is being annotated. Each annotation has only a single contributor. | NO | Refers to table Contributor |
| timestamp | datetime | Date and time when the annotation was entered. Annotations are never changed and only the most recent annotation is valid/current.Default value: getdate() | NO | - |
| content | nvarchar (MAX) | Free-form text annotating an object in the database, e.g. a descriptor, state, modifier, description etc. | NO | - |
| is_internal | tinyint | Set to 1 if text is a project internal note that should not normally be present on public pages. Goal is reduction of “annotation noise”, not confidentiality. At least project contributors will be able to read internals.Default value: ‘0’ | NO | - |
| is_latest_revision | tinyint | Set to 1 if this annotation is the latest by a given contributor. Annotations can never be changed, but each revision creates a new version. Only the latest version is valid.Default value: ‘0’ | NO | - |
| MEMO_WhichVersionsInheritIsNotSolved | int | - | NO | - |
Depending on:
- BaseEntity
- Contributor
Table BaseEntity
The BaseEntity is used within the database to provide unique keys
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal object ID, unique across all tables in database (primary key) | NO | - |
| table_id | int | References the table name of table entries associated with the BaseEntity; NULL indicates orphaned entries and may be used for garbage collection of otherwise deleted object idsDefault value: NULL | YES | Refers to table BaseEntityTable_Enum |
| specific_rights_text | nvarchar (255) | To be used where the licence for an object differs from the default project licenceDefault value: NULL | YES | - |
| specific_licence_uri | nvarchar (255) | URI of licence, where different from project licenceDefault value: NULL | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntityTable_Enum
Table CacheDatabase
Table holding the cache databases connected to the database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Server | varchar (50) | The name or IP of the server where the cache database is located | NO | - |
| DatabaseName | varchar (50) | The name of the cache database | NO | - |
| Port | smallint | The port of the server where the cache database is located | NO | - |
| Version | varchar (50) | The version of the cache database | YES | - |
Table CacheMappingDescription
Table holding the description key mappings for the cache database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| project_id | int | Reference to the description project (foreign key) | NO | Refers to table Project |
| description_id | int | Reference to the description to which these data belong (foreign key) | NO | Refers to table Description |
| target_key | int | Mapped key value for the target of the cache database. | NO | - |
Depending on:
- Description
- Project
Table CacheMappingDescriptor
Table holding the descriptor key mappings for the cache database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| project_id | int | Reference to the descriptor project (foreign key) | NO | Refers to table Project |
| descriptor_id | int | Reference to the descriptor to which these data belong (foreign key) | NO | Refers to table Descriptor |
| target_key | int | Mapped key value for the target of the cache database. | NO | - |
| last_state_key | int | Highest value of state key. Relevant for key_mapping_level > 0Default value: (0) | YES | - |
Depending on:
- Descriptor
- Project
Table CacheMappingState
Table holding the categorical state key mappings for the cache database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| project_id | int | Reference to the state project (foreign key) | NO | Refers to table Project |
| state_id | int | Reference to the categorical state to which these data belong (foreign key) | NO | Refers to table CategoricalState |
| target_key | int | Mapped key value for the target of the cache database. | NO | - |
Depending on:
- CategoricalState
- Project
Table CacheProject
Table holding the project settings for the cache database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| project_id | int | Reference to the description project (foreign key) | NO | Refers to table Project |
| withheld_data_level | tinyint | Flag for handling of withheld descriptor data. 0=withhold description; 1=withhold descriptor; 2=do not withhold dataDefault value: (0) | YES | - |
| key_mapping_level | tinyint | Flag for handling of key mapping. 0=re-build mapping for each update; 1=keep first mapping; 2=additionally take numeric values of alternate ID for items; >2 as 2 but re-initialize tableDefault value: (1) | YES | - |
| last_char_key | int | Highest value of character key. Relevant for key_mapping_level > 0Default value: (0) | YES | - |
| last_item_key | int | Highest value of item key. Relevant for key_mapping_level > 0Default value: (0) | YES | - |
| last_changes | datetime | The recent date when data within the project had been changedDefault value: getdate() | YES | - |
Depending on:
- Project
Table CategoricalSamplingData
The categorical data recorded for a sampling event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | - |
| sampling_unit_id | int | Reference to the sampling unit (foreign key) | NO | Refers to table SamplingUnit |
| state_id | int | Reference to the state (descriptor is implicit in state_id) (foreign key) | NO | Refers to table CategoricalState |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| modifier_id | int | Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL | YES | Refers to table Modifier |
| frequency_value | int | Number of times this category was observed within a single sampling unitDefault value: NULL | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- CategoricalState
- Modifier
- SamplingUnit
Table CategoricalState
The categorical states available for categorical descriptors
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| descriptor_id | int | Reference to the descriptor to which the state belongs (foreign key) | NO | Refers to table Descriptor |
| label | nvarchar (255) | Short label (or name) of descriptor state | NO | - |
| abbreviation | nvarchar (255) | Abbreviated label of descriptor stateDefault value: NULL | YES | - |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the descriptor state definition | YES | - |
| wording | nvarchar (255) | Optional separate wording for natural language generation (label will be used if this is missing)Default value: NULL | YES | - |
| display_order | int | A positive number defining the sequence in which descriptor states are displayedDefault value: ‘0’ | NO | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- Descriptor
afterDeleteCategoricalState
afterInsertCategoricalState
trgCrCategoricalState
Table CategoricalSummaryData
The categorical data of a description
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| description_id | int | Reference to the description to which these data belong (foreign key) | NO | Refers to table Description |
| state_id | int | Reference to the state (the descriptor is implicit in state_id) (foreign key) | NO | Refers to table CategoricalState |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| modifier_id | int | Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL | YES | Refers to table Modifier |
| frequency_id | int | Optional reference to a frequency modifier definition (e.g. “rarely”, “usually”, “mostly”) (foreign key)Default value: NULL | YES | Refers to table Frequency |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- CategoricalState
- Description
- Frequency
- Modifier
trgCrCategoricalSummaryData
Table Contribution
Multiple contributors may contribute in different roles
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| object_id | int | Reference to an object which the contributor has created or contributed to, references BaseEntity (foreign key) | NO | Refers to table BaseEntity |
| contributor_id | int | Reference to a contributor (foreign key) | NO | Refers to table Contributor |
| role | nvarchar (255) | TODO: expand enumeration, perhaps rather as linked vocabulary?Default value: ‘contributor’ | NO | - |
| timestamp | datetime | Date and time of a contributionDefault value: getdate() | NO | - |
Depending on:
- BaseEntity
- Contributor
Table Contributor
People contibuting to a work. Roles are recorded in Contribution
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| nvarchar (255) | User’s email addressDefault value: NULL | YES | - | |
| web_pseudonym | nvarchar (255) | Optional alternative name to display publicly on the webDefault value: NULL | YES | - |
| avatar_uri | nvarchar (255) | Optional URI to an image representing the userDefault value: NULL | YES | - |
| real_name | nvarchar (255) | Optional name the user gave (full name)Default value: NULL | YES | - |
| linked_agent_uri | nvarchar (255) | Linking to agents managed in an external componentDefault value: NULL | YES | - |
Table Description
The description in the database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| label | nvarchar (255) | Short label (or name) of descriptionDefault value: NULL | YES | - |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the description definition | YES | - |
| project_id | int | Each description belongs to exactly one project that determines its terminology (required, foreign key) | NO | Refers to table Project |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
| alternate_id | nvarchar (30) | Optional alternate id of a description item - should be unique within one project, but not checked by databaseDefault value: NULL | YES | - |
| wording | nvarchar (255) | Optional separate wording for natural language generation (label will be used if this is missing)Default value: NULL | YES | - |
Depending on:
- BaseEntity
- Project
afterDeleteDescription
afterInsertDescription
Table DescriptionProject
The projects where descriptions are accessible
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| description_id | int | ID of the description that belongs to the project (required, foreign key) | NO | Refers to table Description |
| project_id | int | ID of the project to which the description belongs (required, foreign key) | NO | Refers to table Project |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data set | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database. | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set last | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated. | YES | - |
Depending on:
- Description
- Project
trgCrDescriptionProject
Table DescriptionScope
The scope of the description
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| description_id | int | Reference to the description to which these data belong (foreign key) | NO | Refers to table Description |
| label | nvarchar (255) | Short label (or name) of scopeDefault value: NULL | YES | - |
| dwbURI | nvarchar (500) | Reference to DiversityWorkbench componentDefault value: NULL | YES | - |
| type | nvarchar (255) | Scope type (“GeographicArea”, “SamplingPlot”, “Citation”, “Observation”, “Specimen”, “TaxonName”, “OtherConcept”, “Stage”, “Part” or “Sex”)Default value: NULL | YES | - |
| other_scope_id | int | Reference to further scopes, e.g. stage, sex etc. (foreign key)Default value: NULL | YES | Refers to table OtherScope |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Description
- OtherScope
trgCrDescriptionScope
Table Descriptor
Descriptor (= characters, features) define variables
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| label | nvarchar (255) | Short label (or name) of descriptor | NO | - |
| abbreviation | nvarchar (255) | Abbreviated label of descriptorDefault value: NULL | YES | - |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the descriptor definition | YES | - |
| display_order | int | A positive number defining the sequence in which descriptors are displayedDefault value: ‘0’ | NO | - |
| subclass | nvarchar (255) | The four character subclasses of SDD are all combined here in one entity and distinguished by this attribute (“categorical”, “quantitative”, “text” or “sequence”)Default value: ‘categorical’ | NO | - |
| statistical_measurement_scale | nvarchar (255) | Scale of descriptor: Categorical may be nominal (unordered, “red/green/blue”) or ordinal (ordered, “bad/medium/good”); Quantitative may be interval (°C) or ratio (mass, length, time, K)Default value: ’nominal' | NO | - |
| usually_exclusive | tinyint | Applicable to categorical (nominal/ordinal) descriptors only. If usually exclusive = 1, then by default the user interface allows only entering one state. Nevertheless, multiple states in the data are valid.Default value: ‘0’ | NO | - |
| state_collection_model | nvarchar (255) | Handling of multiple values: OrSet/AndSet: unordered set combined with or/and, OrSeq/AndSeq: ordered sequence combined with or/and, WithSeq: example is “green with brown”, Between: an example is “oval to elliptic”Default value: ‘OrSet’ | NO | - |
| mandatory | tinyint | Is the scoring of this descriptor mandatory (required) in each item?Default value: ‘0’ | NO | - |
| repeatability | nvarchar (255) | How reliable and consistent are repeated measurements or scorings of the descriptor by different observers and on different objects? (“ignore”, “very low”, “low”, “below average”, “slightly below average”, “standard”, “slightly above average”, “above average”, “high” or “very high”)Default value: ‘standard’ | NO | - |
| availability | nvarchar (255) | How available is the descriptor or concept for identification? (“ignore”, “very low”, “low”, “below average”, “slightly below average”, “standard”, “slightly above average”, “above average”, “high” or “very high”)Default value: ‘standard’ | NO | - |
| measurement_unit | nvarchar (255) | A measurement unit (mm, inch, kg, °C, m/s etc.) or dimensionless scaling factorDefault value: NULL | YES | - |
| measurement_unit_precedes_value | tinyint | Set to 1 if the measurement unit precedes the valueDefault value: ‘0’ | NO | - |
| values_are_integer | tinyint | Set to 1 if the values are integerDefault value: ‘0’ | NO | - |
| min_plausible_value | float | Applicable to quantitative descriptors only; in support of a plausibility check for values. Example: for tree height this could be 0, i.e. only positive values allowedDefault value: ‘-1.79e308’ | NO | - |
| max_plausible_value | float | Applicable to quantitative descriptors only; in support of a plausibility check for values. Example: for tree height this could be 99Default value: ‘1.79e308’ | NO | - |
| sequence_type | nvarchar (255) | Type of molecular sequence, “Nucleotide” or “Protein”. The value “Nucleotide” covers RNA and DNA sequencesDefault value: ‘Nucleotide’ | NO | - |
| symbol_length | tinyint | The number of letters in each symbol. Nucleotides are always codes with 1-letter symbols, but proteins may use 1 or 3-letter codes (e.g. A or Ala for alanine)Default value: ‘1’ | NO | - |
| enable_ambiguity_symbols | tinyint | Set to 1 if ambiguity symbols are supported in the sequence string, e.g. R, Y, S, W for nucleotides or B, Z for proteinsDefault value: ‘1’ | NO | - |
| gap_symbol | nvarchar (3) | A string identifying the “gap” symbol used in aligned sequences. The gap symbol must always be symbol_length longDefault value: NULL | YES | - |
| wording_before | nvarchar (255) | Representation for natural language output, inserted before the states/valueDefault value: NULL | YES | - |
| wording_after | nvarchar (255) | Representation for natural language output, inserted after the states/valueDefault value: NULL | YES | - |
| data_entry_note | nvarchar (MAX) | A note or prompt when entering or dealing with data | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
| wording | nvarchar (255) | Optional separate wording for natural language generation (label will be used if this is missing)Default value: NULL | YES | - |
Depending on:
- BaseEntity
afterDeleteDescriptor
afterInsertDescriptor
Table DescriptorInapplicability
The descriptor dependency rules
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| controlled_descriptor_id | int | Reference to the descriptor to which the rule will apply, e.g. which will be inapplicable if controlling state applies to the same description (foreign key) | NO | Refers to table Descriptor |
| controlling_state_id | int | Reference to the controlling categorical state; if present in a description, controlled descriptor is affected according to rule | NO | Refers to table CategoricalState |
| rule | nvarchar (255) | The kind of rule creating a descriptor inapplicability (“inapplicable-if”, “applicable-if”, “inapplicable-computed-from-applicable”)Default value: ‘inapplicable-if’ | NO | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- CategoricalState
- Descriptor
Table DescriptorMapping_C2C
Mapping of a categorical state to another categorical state
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| from_state_id | int | Reference to a categorical state that shall be mapped to target state | NO | Refers to table CategoricalState |
| to_state_id | int | Reference to a categorical state that shall be the target state | NO | - |
Depending on:
- CategoricalState
Table DescriptorMapping_Q2C
Mapping of a quantitative descriptor to a categorical state
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (foreign key) | NO | - |
| from_descr_id | int | Reference to quantitative descriptor that shall be mapped | NO | Refers to table Descriptor |
| from_lowerbound | float | - | NO | - |
| from_upperbound | float | - | NO | - |
| from_statistical_measure_id | int | - | NO | Refers to table StatisticalMeasure_Enum |
| from_statmeas_class_TO_CONSIDER | int | - | NO | - |
| to_state_id | int | Reference to a categorical state that shall be the target state | NO | Refers to table CategoricalState |
Depending on:
- CategoricalState
- Descriptor
- StatisticalMeasure_Enum
Table DescriptorStatusData
The status data of a descriptor for a certain description
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| description_id | int | Reference to the description to which these data belong (foreign key) | NO | Refers to table Description |
| descriptor_id | int | Reference to the descriptor to which the status value belongs (foreign key) | NO | Refers to table Descriptor |
| datastatus_id | int | Reference to a status definition. Status is like a marker why data is missing or in need of revision (examples: “unknown”, “not applicable”, “to be checked”, “data withheld” etc.) (foreign key) | NO | Refers to table DataStatus_Enum |
| notes | nvarchar (MAX) | Free-form text detailing additional information (rarely used, not multilingual) | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- DataStatus_Enum
- Description
- Descriptor
trgCrDescriptorStatusData
Table DescriptorTree
The root and definition of a descriptor tree
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| is_complete | tinyint | Set to 1 if this tree includes the largest possible set of descriptors for the taxon set associated with a project (i.e. the “master-descriptor tree”, other trees being subsets)Default value: ‘0’ | NO | - |
| label | nvarchar (255) | Short label (or name) of descriptor tree | NO | - |
| type | nvarchar (255) | “mixed”, “part-of”, “property” or “generalization” | NO | - |
| project_id | int | Each tree belongs to exactly one project (required, foreign key) | NO | Refers to table Project |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- Project
afterDeleteDescriptorTree
afterInsertDescriptorTree
Table DescriptorTreeNode
The descriptor tree nodes representing either nodes of the tree or descriptors (“leafes” of the tree)
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| parent_node_id | int | Reference to a parent node, creating a true tree; NULL for a root node (foreign key)Default value: NULL | YES | Refers to table DescriptorTreeNode |
| label | nvarchar (255) | Short label (or name) of internal node associated with a concept; NULL for a descriptor nodeDefault value: NULL | YES | - |
| abbreviation | nvarchar (255) | Abbreviated label of nodeDefault value: NULL | YES | - |
| display_order | int | A positive number defining the sequence in which child nodes are displayedDefault value: ‘0’ | NO | - |
| descriptortree_id | int | Reference to a descriptor tree; all nodes must have a direct link to the tree definition (foreign key) | NO | Refers to table DescriptorTree |
| descriptor_id | int | Reference to a descriptor; NULL if the present node is an inner concept node defining a hierarchy of nodes or descriptors (foreign key)Default value: NULL | YES | Refers to table Descriptor |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the node definition | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- Descriptor
- DescriptorTree
afterDeleteDescriptorTreeNode
afterInsertDescriptorTreeNode
trgCrDescriptorTreeNode
Table DescriptorTreeNodeRecFrequency
Selection of recommended frequency values for descriptor tree parts or single descriptors
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| node_id | int | Reference to the descriptor tree node (foreign key) | NO | Refers to table DescriptorTreeNode |
| frequency_id | int | Reference to the recommended frequency modifier (foreign key) | NO | Refers to table Frequency |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- DescriptorTreeNode
- Frequency
Table DescriptorTreeNodeRecModifier
Selection of recommended modifier values for descriptor tree parts or single descriptors
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| node_id | int | Reference to the descriptor tree node (foreign key) | NO | Refers to table DescriptorTreeNode |
| modifier_id | int | Reference to the recommended modifier (foreign key) | NO | Refers to table Modifier |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- DescriptorTreeNode
- Modifier
Table DescriptorTreeNodeRecStatMeasure
Selection of recommended statistical measures for descriptor tree parts or single descriptors
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| node_id | int | Reference to the descriptor tree node (foreign key) | NO | Refers to table DescriptorTreeNode |
| measure_id | int | Reference to the recommended measure (foreign key) | NO | Refers to table StatisticalMeasure_Enum |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- DescriptorTreeNode
- StatisticalMeasure_Enum
Table Frequency
Definition of frequency modifier values
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| label | nvarchar (255) | Short label (or name) of frequency modifier (e.g. “very rare”, “rare”, “usually” etc.) | NO | - |
| lower_estimate | float | Each frequency modifier defines a frequency range, this is the estimated lowest frequencyDefault value: ‘0’ | NO | - |
| upper_estimate | float | As above, estimate of upper range for the border; note: ranges may overlap!Default value: ‘1’ | NO | - |
| display_order | int | A positive number defining the sequence in which frequency modifiers are to be displayedDefault value: ‘0’ | NO | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
afterDeleteFrequency
afterInsertFrequency
Table Identifier
Objects may have multiple external identifiers
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| object_id | int | Reference to object that is identified by the uri, references BaseEntity (foreign key) | NO | Refers to table BaseEntity |
| uri | nvarchar (255) | URI (globally unique identifier, including IRIs) for the object (descriptor/character, taxon, etc.). Local IDs should be entered using a consistent method (e.g. if organisation x uses local id “char 123” create “x.org:EXTERNAL_LINK:char:123”) | NO | - |
| match | nvarchar (255) | Match of URI to linked object, see definitions according to skos:exactMatch etc.Default value: ’exact' | NO | - |
Depending on:
- BaseEntity
Table ImportMapping
The import mappings to support mapping of external file keys to database keys
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| object_id | int | Reference to the object to which the mapping belongs, references BaseEntity (foreign key) | NO | Refers to table BaseEntity |
| session_id | int | Reference to the import session to which the mapping belongs, references ImportMapping (foreign key) | NO | Refers to table ImportSession |
| table_name | varchar (50) | Name of the import table | NO | - |
| parent_key | varchar (255) | Symbolic key of the parent table | NO | - |
| external_key | nvarchar (255) | External key in the import file | NO | - |
Depending on:
- BaseEntity
- ImportSession
afterInsertImportMapping
Table ImportSession
The import sessions to support import of data spread over several files
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| project_id | int | Each import session belongs to exactly one project (required, foreign key) | NO | Refers to table Project |
| detail | nvarchar (MAX) | Detail text explaining the import session definition | YES | - |
| create_timestamp | datetime | Date and time when the import session was createdDefault value: getdate() | NO | - |
| update_timestamp | datetime | Date and time when the import session was updatedDefault value: getdate() | NO | - |
Depending on:
- Project
Table Modifier
Definition of modifier values
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| class | nvarchar (255) | Grouping of modifiers into classes (“Certainty”, “Seasonal”, “Diurnal”, “TreatAsMisinterpretation” or “OtherModifierClass”) | NO | - |
| label | nvarchar (255) | Short label (or name) of modifier (e.g. “strong”, “probably” etc.) | NO | - |
| display_order | int | A positive number defining the sequence in which modifiers are to be displayedDefault value: ‘0’ | NO | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
afterDeleteModifier
afterInsertModifier
Table MolecularSequenceData
The molecular sequence data of a description
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| description_id | int | Reference to description to which these data belong (foreign key) | NO | Refers to table Description |
| descriptor_id | int | Reference to a molecular sequence descriptor to which the sequence text belongs (foreign key) | NO | Refers to table Descriptor |
| sequence | nvarchar (MAX) | Molecular sequence text referring to information on one descriptor | NO | - |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Description
- Descriptor
trgCrMolecularSequenceData
Table MolecularSequenceSamplingData
The molecular sequence data recorded for a sampling event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| sampling_unit_id | int | Reference to a sampling unit (foreign key) | NO | Refers to table SamplingUnit |
| descriptor_id | int | Reference to a molecular sequence descriptor to which the sequence text belongs (foreign key) | NO | Refers to table Descriptor |
| sequence | nvarchar (MAX) | Molecular sequence text referring to information on one descriptor | NO | - |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Descriptor
- SamplingUnit
Table OtherScope
Scope values for description scope values for scope types “Other scope”, “Part”, “Stage” and “Sex”
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| label | nvarchar (255) | Short label (or name) of scope (e.g. “male”, “female”, “adult”, “juvenile” etc.) | NO | - |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the scope and delimitation of the present description | YES | - |
| type | nvarchar (255) | Describes the scope type (“sex”, “stage”, “part” or “other”) | NO | - |
| sex_status_id | int | Reference to sex status value according SDD V5.1 (foreign key)Default value: NULL | YES | Refers to table SexStatus_Enum |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- SexStatus_Enum
afterDeleteOtherScope
afterInsertOtherScope
Table Project
Projects define separated workareas in a single database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| parent_project_id | int | Reference to a parent project, creating a subproject; NULL for a root project (foreign key)Default value: NULL | YES | Refers to table Project |
| label | nvarchar (255) | Short label (or name) of project (or subproject); each project defines a separate work environment within a shared database by means of defining sets of descriptors (through DescriptorTree) | NO | - |
| rights_text | nvarchar (255) | The default rights and licence statement for the entire project; BaseEntity provides means to override this for individual objectsDefault value: NULL | YES | - |
| licence_uri | nvarchar (255) | URI of licence for the project; BaseEntity provides means to override this for individual objectsDefault value: NULL | YES | - |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the project (or subproject) | YES | - |
| primary_language_code | nvarchar (3) | Language of primary text version (fields directly in tables, may be translated in table Translation); Examples: “de”, “en” etc. (ISO 639 language code) | NO | - |
| ProjectProxyID | int | Reference to a project definition of DiversityProjects that allows control of access rightsDefault value: (0) | NO | Refers to table ProjectProxy |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
| wording | nvarchar (255) | Optional separate wording for natural language generation and cache database (label will be used if this is missing) | YES | - |
Depending on:
- BaseEntity
- ProjectProxy
afterDeleteProject
afterInsertProject
Table Project_AvailableScope
Scope values available for a certain project
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| project_id | int | Reference to the project for which a shall be available (foreign key) | NO | Refers to table Project |
| scope_id | int | Reference to a scope definition in OtherScope (foreign key) | NO | Refers to table OtherScope |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- OtherScope
- Project
Table ProjectUser
The projects that a user can access
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LoginName | nvarchar (50) | A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc.. | NO | Refers to table UserProxy |
| ProjectID | int | ID of the project defined in DiversityProjects - negative value for local project | NO | Refers to table ProjectProxy |
| ReadOnly | bit | If the user has only read access to data of this projectDefault value: (0) | YES | - |
Depending on:
- ProjectProxy
- UserProxy
Table QuantitativeSamplingData
The quantitative data recorded for a sampling event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| measure_id | int | Optional statistical measure (minimum, mean, maximum, ranges, sample size, variance etc.) recorded in value; if NULL, value is a direct measurement (foreign key)Default value: NULL | YES | Refers to table StatisticalMeasure_Enum |
| sampling_unit_id | int | Reference to a sampling unit (foreign key) | NO | Refers to table SamplingUnit |
| descriptor_id | int | Reference to descriptor which the values record (foreign key) | NO | Refers to table Descriptor |
| value | float | The value for the statistical measure or single measurement/recording | NO | - |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| modifier_id | int | Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL | YES | Refers to table Modifier |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Descriptor
- Modifier
- SamplingUnit
- StatisticalMeasure_Enum
Table QuantitativeSummaryData
The quantitative data of a description
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| measure_id | int | Reference to the statistical measure (minimum, mean, maximum, ranges, sample size, variance etc.) recorded in value (foreign key) | NO | Refers to table StatisticalMeasure_Enum |
| description_id | int | Reference to the description to which these data belong (foreign key) | NO | Refers to table Description |
| descriptor_id | int | Reference to the descriptor which the values record (foreign key) | NO | Refers to table Descriptor |
| value | float | The value of the statistical measure | NO | - |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| modifier_id | int | Optional reference to a modifier definition (e.g. “strongly”, “at the base”, “in autumn”) (foreign key)Default value: NULL | YES | Refers to table Modifier |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Description
- Descriptor
- Modifier
- StatisticalMeasure_Enum
trgCrQuantitativeSummaryData
Table Resource
Hyperlinks to separate rich text/media objects
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | Refers to table BaseEntity |
| terminology_role | nvarchar (255) | Role in relation to descriptors or states (“unknown”=role not known or not specified; “diagnostic”=optimized for identification; “iconic”=icon/thumbnail, needs text; “normative”=defines a resource object; “primary”=display always, informative without text; “secondary”=display only on request)Default value: ‘unknown’ | NO | - |
| ranking_for_terminology | tinyint | Ranking of the resource with respect to terminology; range: 0 to 10Default value: NULL | YES | - |
| descriptor_id | int | Optional reference to a descriptor (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL | YES | Refers to table Descriptor |
| state_id | int | Optional reference to a categorical state (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL | YES | Refers to table CategoricalState |
| taxon_role | nvarchar (255) | Role in relation to taxa or descriptions (“unknown”=role not known or not specified; “diagnostic”=optimized for identification; “iconic”=icon/thumbnail, needs text; “normative”=defines a resource object; “primary”=display always, informative without text; “secondary”=display only on request)Default value: ‘unknown’ | NO | - |
| ranking_for_taxon_use | tinyint | Ranking of the resource with respect to taxa or descriptions; range: 0 to 10Default value: NULL | YES | - |
| taxon_id | int | Optional reference to a taxon (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL | YES | - |
| description_id | int | Optional reference to a description (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL | YES | Refers to table Description |
| scope_id | int | Reference to the scope of the resource (e.g. “sex”, “stage”, “season” etc.)Default value: NULL | YES | - |
| display_embedded | tinyint | false/0: display as link, true/1: display as embedded media objectDefault value: ‘0’ | NO | - |
| label | nvarchar (255) | Short label of (or caption for) the resource, e.g. a text displayed below an image or instead of the link | NO | - |
| detail | nvarchar (MAX) | Additional detail text explaining or commenting on the resource definition | YES | - |
| language_code | nvarchar (3) | Language of the resource itself, use zxx for language neutral/no linguistic content (ISO 639-1)Default value: NULL | YES | - |
| rights_text | nvarchar (255) | The rights and licence statement for the resourceDefault value: NULL | YES | - |
| licence_uri | nvarchar (255) | URI of licence for the resourceDefault value: NULL | YES | - |
| display_order | int | A positive number defining the sequence in which multiple resources are displayedDefault value: ‘0’ | NO | - |
| node_id | int | Optional reference to a descriptor tree node (at least 1 of description_id, node_id, descriptor_id, state_id or taxon_id must be present) (foreign key)Default value: NULL | YES | Refers to table DescriptorTreeNode |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- CategoricalState
- Description
- Descriptor
- DescriptorTreeNode
afterDeleteResource
afterInsertResource
Table ResourceVariant
Different resource variants/instances/service access points
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record, references BaseEntity (primary key) | NO | - |
| resource_id | int | Reference to the resource to which these variants provide access points (foreign key) | NO | Refers to table Resource |
| variant_id | int | Reference to the definition of a variant class e.g. “tiny sample”, “small sample”, “lower quality”, “medium quality”, “good quality” or “best quality” (foreign key) | NO | Refers to table ResourceVariant_Enum |
| url | nvarchar (255) | URL of the resource variant | NO | - |
| pixel_width | int | Where applicable (still- or moving image): the width in pixelDefault value: NULL | YES | - |
| pixel_height | int | Where applicable (still- or moving image): the height in pixelDefault value: NULL | YES | - |
| duration | int | Where applicable (sound or moving image): the duration in secondsDefault value: NULL | YES | - |
| size | int | The size of the resource in bytesDefault value: NULL | YES | - |
| mime_type | nvarchar (255) | Type of the resource as MIME type like image/jpeg; color as color/hexrgbDefault value: NULL | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Resource
- ResourceVariant_Enum
Table SamplingEvent
A sampling event may contain many sampling units
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | Refers to table BaseEntity |
| description_id | int | Reference to the description to which these sampling data belong (foreign key) | NO | Refers to table Description |
| label | nvarchar (255) | Free-form text identifying a sampling event to humans. Sampling events have time, location, perhaps purpose and act as a container for multiple sampling units.Default value: NULL | YES | - |
| detail | nvarchar (MAX) | Free-form text giving detail or description of sampling event | YES | - |
| date_time | datetime | Date and time when the event occurred; single point or start of durationDefault value: NULL | YES | - |
| datetimespan_end | datetime | Optional end of a time span or duration within which or during which the event occurredDefault value: NULL | YES | - |
| geographic_area | nvarchar (255) | A geographic area at which the event occurredDefault value: NULL | YES | - |
| geographic_area_uri | nvarchar (255) | Reference to a geographic area in DiversityGazetteersDefault value: NULL | YES | - |
| coord_dec_latitude | float | Latitude of geographical coordinates in signed decimal degreesDefault value: NULL | YES | - |
| coord_dec_longitude | float | Longitude of geographical coordinates in decimal degreesDefault value: NULL | YES | - |
| coord_literal | nvarchar (255) | Free-form string, either in addition to or instead of code (a mapping to the controlled vocabulary may be unavailable or considered ambiguous)Default value: NULL | YES | - |
| coord_geodeticdatum | nvarchar (50) | Optional, only where knowledge of the geodetic datum is readily available; defaults to WGS84 used in GPSDefault value: NULL | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- Description
afterDeleteSamplingEvent
afterInsertSamplingEvent
Table SamplingUnit
Sampling unit data
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| sampling_event_id | int | Reference to a sampling event; keeps paired measurements together (several categorical or quantitative observations at the same time on the same object or object part) (foreign key) | NO | Refers to table SamplingEvent |
| collection_specimen | nvarchar (255) | A collection specimen that is represented by the sampling unitDefault value: NULL | YES | - |
| collection_specimen_uri | nvarchar (500) | Reference to a collection specimen in DiversityCollectionDefault value: NULL | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- SamplingEvent
Table Tag
Objects may have multiple tags (= keywords)
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| tag | nvarchar (255) | Tag (= keyword) to categorize objects like descriptors, states, descriptions | NO | - |
| object_id | int | Reference to object to which the tag belongs, references BaseEntity (foreign key) | NO | Refers to table BaseEntity |
Depending on:
- BaseEntity
Table TextDescriptorData
The text data of a description
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| description_id | int | Reference to description to which these data belong (foreign key) | NO | Refers to table Description |
| descriptor_id | int | Reference to a text descriptor to which the free-form text belongs (foreign key) | NO | Refers to table Descriptor |
| content | nvarchar (MAX) | Free-form text referring to information on one descriptor | NO | - |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the values | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Description
- Descriptor
trgCrTextDescriptorData
Table TextSamplingData
Free-form text data recorded for a sampling event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| sampling_unit_id | int | Reference to a sampling unit (foreign key) | NO | Refers to table SamplingUnit |
| descriptor_id | int | Reference to descriptor to which the free-form text belongs (foreign key) | NO | Refers to table Descriptor |
| content | nvarchar (MAX) | Free-form text referring to information on one descriptor | NO | - |
| notes | nvarchar (MAX) | Free-form text detailing special cases or additional data beyond the data | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- Descriptor
- SamplingUnit
Table TODO_MultilingualCharDataNotes
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| table_is_just_a_note-Work_needs_to_be_done | int | - | NO | - |
| memo_char_and_taxon_notes_is_already_redundant_due_to_annotation | int | - | NO | - |
| memo_char_data_are_not_an_object_thus_not_annotated | int | - | NO | - |
Table Translation
The translations of entries related to BaseEntity
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| id | int | Database-internal ID of this record (primary key) | NO | - |
| object_id | int | Reference to the object to which the translation belongs, references BaseEntity (foreign key) | NO | Refers to table BaseEntity |
| language_code | nvarchar (3) | Language of representation translation (ISO 639 language code) | NO | - |
| column_id | int | References the translated column name | NO | Refers to table TranslationColumn_Enum |
| contents | nvarchar (MAX) | Translated contents | YES | - |
| LogInsertedBy | nvarchar (50) | ID of the creator of this data setDefault value: [dbo].UserID | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Depending on:
- BaseEntity
- TranslationColumn_Enum