Diversity Collection
VIEWS
The following objects are not included:
- System objects
- Objects marked as obsolete
- Previous versions of objects
View AnnotationEvent
Annotations linked to table CollectionEvent
| Column | Data type | Description | Nullable |
|---|---|---|---|
| AnnotationID | int | ID of the annotation (primary key) | NO |
| CollectionEventID | int | ID of the collection event. Refers to PK of table CollectionEvent | NO |
| ReferencedAnnotationID | int | If an annotation refers to another annotation, the ID of the referred annotation | YES |
| AnnotationType | nvarchar (50) | The type of the annotation as defined in AnnotationType_Enum, e.g. Reference | NO |
| Title | nvarchar (50) | Title of the annotation | YES |
| Annotation | nvarchar (MAX) | The annotation entered by the user | NO |
| URI | varchar (255) | The complete URI address of a resource related to the annotation. May be link to a module, e.g. for the annotation type reference | YES |
| ReferenceDisplayText | nvarchar (500) | The title of the reference. If the entry is linked to an external module like DiversityReferences, the cached display text of the referenced data set | YES |
| ReferenceURI | varchar (255) | If the entry is linked to an external module like DiversityReferences, the link to the referenced data set | YES |
| SourceDisplayText | nvarchar (500) | The name of the source. If the entry is linked to an external module like DiversityAgents, the cached display text of the referenced data set | YES |
| SourceURI | varchar (255) | If the entry is linked to an external module like DiversityAgents, the link to the referenced data set | YES |
| IsInternal | bit | If an annotation is restricted to authorized users of the database | YES |
Depending on:
- Annotation
View AnnotationPart
Annotations linked to table CollectionSpecimenPart
| Column | Data type | Description | Nullable |
|---|---|---|---|
| AnnotationID | int | ID of the annotation (primary key) | NO |
| SpecimenPartID | int | ID of the collection specimen part. Refers to PK of table CollectionSpecimenPart | NO |
| ReferencedAnnotationID | int | If an annotation refers to another annotation, the ID of the referred annotation | YES |
| AnnotationType | nvarchar (50) | The type of the annotation as defined in AnnotationType_Enum, e.g. Reference | NO |
| Title | nvarchar (50) | Title of the annotation | YES |
| Annotation | nvarchar (MAX) | The annotation entered by the user | NO |
| URI | varchar (255) | The complete URI address of a resource related to the annotation. May be link to a module, e.g. for the annotation type reference | YES |
| ReferenceDisplayText | nvarchar (500) | The title of the reference. If the entry is linked to an external module like DiversityReferences, the cached display text of the referenced data set | YES |
| ReferenceURI | varchar (255) | If the entry is linked to an external module like DiversityReferences, the link to the referenced data set | YES |
| SourceDisplayText | nvarchar (500) | The name of the source. If the entry is linked to an external module like DiversityAgents, the cached display text of the referenced data set | YES |
| SourceURI | varchar (255) | If the entry is linked to an external module like DiversityAgents, the link to the referenced data set | YES |
| IsInternal | bit | If an annotation is restricted to authorized users of the database | YES |
Depending on:
- Annotation
View AnnotationSpecimen
Annotations linked to table CollectionSpecimen
| Column | Data type | Description | Nullable |
|---|---|---|---|
| AnnotationID | int | ID of the annotation (primary key) | NO |
| CollectionSpecimenID | int | ID of the collection specimen. Refers to PK of table CollectionSpecimen | NO |
| ReferencedAnnotationID | int | If an annotation refers to another annotation, the ID of the referred annotation | YES |
| AnnotationType | nvarchar (50) | The type of the annotation as defined in AnnotationType_Enum, e.g. Reference | NO |
| Title | nvarchar (50) | Title of the annotation | YES |
| Annotation | nvarchar (MAX) | The annotation entered by the user | NO |
| URI | varchar (255) | The complete URI address of a resource related to the annotation. May be link to a module, e.g. for the annotation type reference | YES |
| ReferenceDisplayText | nvarchar (500) | The title of the reference. If the entry is linked to an external module like DiversityReferences, the cached display text of the referenced data set | YES |
| ReferenceURI | varchar (255) | If the entry is linked to an external module like DiversityReferences, the link to the referenced data set | YES |
| SourceDisplayText | nvarchar (500) | The name of the source. If the entry is linked to an external module like DiversityAgents, the cached display text of the referenced data set | YES |
| SourceURI | varchar (255) | If the entry is linked to an external module like DiversityAgents, the link to the referenced data set | YES |
| IsInternal | bit | If an annotation is restricted to authorized users of the database | YES |
Depending on:
- Annotation
View AnnotationUnit
Annotations linked to table IdentificationUnit
| Column | Data type | Description | Nullable |
|---|---|---|---|
| AnnotationID | int | ID of the annotation (primary key) | NO |
| IdentificationUnitID | int | ID of the IdentificationUnit. Refers to PK of table IdentificationUnit | NO |
| ReferencedAnnotationID | int | If an annotation refers to another annotation, the ID of the referred annotation | YES |
| AnnotationType | nvarchar (50) | The type of the annotation as defined in AnnotationType_Enum, e.g. Reference | NO |
| Title | nvarchar (50) | Title of the annotation | YES |
| Annotation | nvarchar (MAX) | The annotation entered by the user | NO |
| URI | varchar (255) | The complete URI address of a resource related to the annotation. May be link to a module, e.g. for the annotation type reference | YES |
| ReferenceDisplayText | nvarchar (500) | The title of the reference. If the entry is linked to an external module like DiversityReferences, the cached display text of the referenced data set | YES |
| ReferenceURI | varchar (255) | If the entry is linked to an external module like DiversityReferences, the link to the referenced data set | YES |
| SourceDisplayText | nvarchar (500) | The name of the source. If the entry is linked to an external module like DiversityAgents, the cached display text of the referenced data set | YES |
| SourceURI | varchar (255) | If the entry is linked to an external module like DiversityAgents, the link to the referenced data set | YES |
| IsInternal | bit | If an annotation is restricted to authorized users of the database | YES |
Depending on:
- Annotation
View CollectionAgent_Core
Table CollectionAgent restricted to available datasets
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) | NO |
| CollectorsName | nvarchar (255) | Name of the Collector | NO |
| CollectorsAgentURI | varchar (255) | The URI of the Agent, e.g. as stored within the module DiversityAgents | YES |
| CollectorsSequence | datetime2 | The order of collectors in a team. Automatically set by the database system | YES |
| CollectorsNumber | nvarchar (50) | Number assigned to a specimen or a batch of specimens by the collector during the collection event (= ‘field number’) | YES |
| Notes | nvarchar (MAX) | Notes on the collector, e.g. if the name is uncertain | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
Depending on:
- CollectionAgent
- CollectionSpecimenID_Available
View CollectionEvent_Core2
List for accessible content of table CollectionEvent with preformated date column
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) | NO |
| Version | int | The version of the data set. Automatically set by the system. | NO |
| SeriesID | int | The ID of the related expedition. Relates to the PK of the table CollectionExpedition (foreign key). | YES |
| CollectorsEventNumber | nvarchar (50) | Number assigned to a collection event by the collector (= ‘field number’) | YES |
| CollectionDate | datetime | The cached date of the collection event calulated from the entries in CollectionDay, -Month and -Year. | YES |
| CollectionDay | tinyint | The day of the date of the event or when the collection event started | YES |
| CollectionMonth | tinyint | The month of the date of the event or when the collection event started | YES |
| CollectionYear | smallint | The year of the date of the event or when the collection event started | YES |
| CollectionEndDay | tinyint | The day of the date of the event or when the collection event ended | YES |
| CollectionEndMonth | tinyint | The month of the date of the event or when the collection event ended | YES |
| CollectionEndYear | smallint | The year of the date of the event or when the collection event ended | YES |
| CollectionDateSupplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event comprises a period. The time of the event, if necessary. | YES |
| CollectionDateCategory | nvarchar (50) | Category of the date of the identification e.g. “system”, “estimated” (= foreign key, see in table CollEventDateCategory_Enum) | YES |
| CollectionTime | varchar (50) | The time of the event or when the collection event started | YES |
| CollectionTimeSpan | varchar (50) | The time span e.g. in seconds of the collection event | YES |
| LocalityDescription | nvarchar (MAX) | Locality description of the locality exactly as written on the original label (i.e. without corrections during data entry) | YES |
| LocalityVerbatim | nvarchar (MAX) | Locality as given in historical context, documents and labels | YES |
| HabitatDescription | nvarchar (MAX) | Geo-ecological description of the locality exactly as written on the original label (i.e. without corrections during data entry) | YES |
| ReferenceTitle | nvarchar (255) | The title of the publication where the collection event was published. Note that this is only a cached value where ReferenceURI is present | YES |
| ReferenceDetails | nvarchar (50) | The exact location within the reference, e.g. pages, plates | YES |
| ReferenceURI | varchar (255) | URI (e.g. LSID) of the source publication where the collection event is published, may e.g. refer to the module DiversityReferences | YES |
| CollectingMethod | nvarchar (MAX) | Description of the method used for collecting the samples, e.g. traps, moist chambers, drag net | YES |
| Notes | nvarchar (MAX) | Notes on the collection event | YES |
| CountryCache | nvarchar (50) | The country where the collection event took place. Cached value derived from an entry in CollectionEventLocalisation | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
| DataWithholdingReasonDate | nvarchar (50) | The reason for withholding the collection date | YES |
| CollectionDate_YMD | varchar (94) | - | YES |
Depending on:
- CollectionEvent
- CollectionEventID_AvailableNotReadOnly
View CollectionEventID_CanEdit
List for CollectionEventID (PK of table CollectionEvent) a user can edit
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionEventID | int | Refers to the ID of table CollectionEvent (= foreign key and part of primary key) | YES |
Depending on:
- CollectionProject
- CollectionSpecimen
- ProjectUser
View CollectionEventID_UserAvailable
List for CollectionEventID (PK of table CollectionEvent) a user has access to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionEventID | int | Refers to the ID of table CollectionEvent (= foreign key and part of primary key) | YES |
Depending on:
- CollectionProject
- CollectionSpecimen
- ProjectUser
View CollectionEventLocalisation_Core
Content of table CollectionEventLocalisation excluding log columns and geography
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionEventID | int | Refers to the ID of CollectionEvent (= foreign key and part of primary key) | NO |
| LocalisationSystemID | int | Refers to the ID of LocalisationSystem (= foreign key and part of primary key) | NO |
| Location1 | nvarchar (255) | Either a named location selected from a thesaurus (e.g. ‘Germany, Bavaria, Kleindingharting’) or altitude range or other values (e. g. 100-200 m) | YES |
| Location2 | nvarchar (255) | Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus | YES |
| LocationAccuracy | nvarchar (50) | The accuracy of the determination of this locality | YES |
| LocationNotes | nvarchar (MAX) | Notes on the location | YES |
| DeterminationDate | smalldatetime | Date of the determination of the geographical localisation | YES |
| DistanceToLocation | varchar (50) | Distance from the specified place to the real location of the collection site (m) | YES |
| DirectionToLocation | varchar (50) | Direction from the specified place to the real location of the collection site (Degrees rel. to north) | YES |
| ResponsibleName | nvarchar (255) | The name of the agent (person or organization) responsible for this entry. | YES |
| ResponsibleAgentURI | varchar (255) | URI of the person or organisation responsible for the data (see e.g. module DiversityAgents) | YES |
| AverageAltitudeCache | float | Calculated altitude as parsed from the location fields | YES |
| AverageLatitudeCache | float | Calculated latitude as parsed from the location fields | YES |
| AverageLongitudeCache | float | Calculated longitude as parsed from the location fields | YES |
| RecordingMethod | nvarchar (500) | The method or device used for the recording of the localisation | YES |
| Geography | nvarchar (MAX) | The geography of the localisation | YES |
Depending on:
- CollectionEventLocalisation
View CollectionSpecimen_Core2
List of accessible dataset of table CollectionSpecimen containing additional data from table CollectionEvent
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) | NO |
| Version | int | The version of the data set. Automatically set by the system. | NO |
| CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) | YES |
| AccessionNumber | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” | YES |
| AccessionDate | datetime | The date of the accession calculated from the entries in AccessionDay, -Month and -Year | YES |
| AccessionDay | tinyint | The day of the date when the specimen was acquired in the collection | YES |
| AccessionMonth | tinyint | The month of the date when the specimen was acquired in the collection | YES |
| AccessionYear | smallint | The year of the date when the specimen was acquired in the collection | YES |
| AccessionDateSupplement | nvarchar (255) | Verbal or additional accession date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’ | YES |
| AccessionDateCategory | nvarchar (50) | Category of the date of the accession e.g. “system”, “estimated” (= foreign key, see in table CollDateCategory_Enum) | YES |
| DepositorsName | nvarchar (255) | The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. ‘Herbarium P. Döbbler’) | YES |
| DepositorsAgentURI | varchar (255) | The URI of the depositor(s) (person or organization responsible for deposition) | YES |
| DepositorsAccessionNumber | nvarchar (50) | Accession number of the specimen within the previous or original collection, e.g. ‘D-23948’ | YES |
| LabelTitle | nvarchar (MAX) | The title of the label e.g. for printing labels. | YES |
| LabelType | nvarchar (50) | Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc. | YES |
| LabelTranscriptionState | nvarchar (50) | The state of the transcription of a label into the database: ‘Not started’, ‘incomplete’, ‘complete’ | YES |
| LabelTranscriptionNotes | nvarchar (255) | User defined notes on the transcription of the label into the database | YES |
| ExsiccataURI | varchar (255) | If specimen is an exsiccata: The URI of the exsiccata series, e.g. as stored within the DiversityExsiccata module | YES |
| ExsiccataAbbreviation | nvarchar (255) | If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time) | YES |
| OriginalNotes | nvarchar (MAX) | Notes found on the label of the specimen by the original collector or from a later revision | YES |
| AdditionalNotes | nvarchar (MAX) | Additional notes made by the editor of the specimen record, e.g. ‘doubtful identification/locality’ | YES |
| ReferenceTitle | nvarchar (255) | The title of the publication where the collection event was published. Note that this is only a cached value where ReferenceURI is present | YES |
| ReferenceURI | varchar (255) | URI (e.g. LSID) of the source publication where the collection event is published, may e.g. refer to the module DiversityReferences | YES |
| Problems | nvarchar (255) | Description of a problem which occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems! | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
| CollectionDate | varchar (94) | The cached date of the collection event calulated from the entries in CollectionDay, -Month and -Year. | YES |
| Locality | nvarchar (MAX) | LocalityDescription from table CollectionEvent | YES |
| Habitat | nvarchar (MAX) | HabitatDescription from table CollectionEvent | YES |
| InternalNotes | nvarchar (MAX) | Internal notes which should not be published e.g. on websites | YES |
| ExternalDatasourceID | int | An ID to identify an external data collection of the collected specimen (primary key, the ID has no meaning outside of the DiversityWorkbench system) | YES |
| ExternalIdentifier | nvarchar (100) | The identifier of the external specimen as defined in the external data source | YES |
| LogCreatedWhen | datetime | Point in time when this data set was created | YES |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data set | YES |
| LogUpdatedWhen | datetime | Point in time when this data set was updated last | YES |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set last | YES |
Depending on:
- CollectionEvent
- CollectionSpecimen
- CollectionSpecimenID_Available
- UserProxy
View CollectionSpecimenID_Available
CollectionSpecimenIDs a user has access to including CollectionSpecimenIDs not linked to a project
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
Depending on:
- CollectionProject
- CollectionSpecimen
- ProjectUser
View CollectionSpecimenID_AvailableReadOnly
CollectionSpecimenIDs a user has read only access to including CollectionSpecimenIDs from locked projects
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
Depending on:
- CollectionProject
- CollectionSpecimenID_Locked
- ProjectUser
View CollectionSpecimenID_CanEdit
CollectionSpecimenIDs a user has access to excluding those with read only access
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
Depending on:
- CollectionProject
- ProjectUser
View CollectionSpecimenID_Locked
CollectionSpecimenIDs of locked projects
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
Depending on:
- CollectionProject
- ProjectProxy
View CollectionSpecimenID_ReadOnly
CollectionSpecimenIDs a user has read only access to including CollectionSpecimenIDs from locked projects
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
Depending on:
- CollectionProject
- CollectionSpecimenID_Locked
- ProjectUser
View CollectionSpecimenID_UserAvailable
CollectionSpecimenIDs a user has access to including CollectionSpecimenIDs not linked to a project
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
Depending on:
- CollectionProject
- CollectionSpecimen
- ProjectUser
View CollectionSpecimenPart_Core2
Content of table CollectionSpecimenPart restricted to those avaliable for a user including the collection hierarchy
| Column | Data type | Description | Nullable |
|---|---|---|---|
| Collection | nvarchar (1158) | Hierarchy of the collection separated by " | " followed by StorageLocation resp. AccessionNumber and Sublabel |
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). | NO |
| DerivedFromSpecimenPartID | int | SpecimenPartID of the specimen from which the current specimen is derived from | YES |
| PreparationMethod | nvarchar (MAX) | The method used for the preparation of the part of the specimen, e.g. the inoculation method for cultures | YES |
| PreparationDate | datetime | Point in time when the part was preparated e.g when it was separated from the source object | YES |
| AccessionNumber | nvarchar (50) | Accession number of the part of the specimen within the collection, if it is different from the accession number of the specimen as stored in the table CollectionSpecimen, e.g. “M-29834752” | YES |
| PartSublabel | nvarchar (50) | The label for a part of a specimen, e.g. “cone”, or a number attached to a duplicate of a specimen | YES |
| CollectionID | int | ID of the collection as stored in table Collection (= foreign key, see table Collection) | NO |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum) | NO |
| StorageLocation | nvarchar (255) | A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. | YES |
| StorageContainer | nvarchar (500) | The container in which the part is stored | YES |
| Stock | float | Number of stock units, if the specimen is stored in separated units e.g. several boxes or vessels (max. 255) | YES |
| StockUnit | nvarchar (50) | If empty, the stock is given as a count, else it contains the unit in which stock is expressed, e.g. µl, ml, kg etc. | YES |
| Notes | nvarchar (MAX) | Notes on the storage of the sample | YES |
| PartAccessionNumber | nvarchar (50) | - | YES |
Depending on:
- CollectionHierarchyAll
- CollectionSpecimenID_UserAvailable
- CollectionSpecimenPart
View CollectionSpecimenRelationInternal
List of specimen with a relation to a specimen within the same database or where another specimen within the database has defined a relation to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) | YES |
| RelatedSpecimen_CollectionSpecimenID | int | CollectionSpecimenID of the related specimen | YES |
| RelatedSpecimenDisplayText | nvarchar (255) | The name of a related specimen as shown e.g. in a user interface | YES |
| RelationType | nvarchar (50) | Type of the relation between the specimen (= foreign key, see table CollRelationType_Enum) | YES |
| RelatedSpecimenCollectionID | int | ID of the Collection as stored in table Collection (= foreign key, see table Collection) | YES |
| RelatedSpecimenDescription | nvarchar (MAX) | Description of the related specimen | YES |
| Notes | nvarchar (MAX) | Notes on the relation to the specimen | YES |
Depending on:
- baseURL
- CollectionSpecimen
- CollectionSpecimenRelation
View CollectionSpecimenRelationInvers
Content of function CollectionSpecimenRelationInversList: List of specimen where another specimen within the database has defined a relation to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) | NO |
| RelatedSpecimenCollectionSpecimenID | int | The CollectionSpecimenID of the dataset that refers to the current dataset | NO |
| RelatedSpecimenAccessionNumber | nvarchar (50) | The AccessionNumber of the dataset that refers to the current dataset | YES |
| RelationType | nvarchar (50) | Type of the relation between the specimen (= foreign key, see table CollRelationType_Enum) | YES |
| RelatedSpecimenCollectionID | int | ID of the Collection as stored in table Collection (= foreign key, see table Collection) | YES |
| Notes | nvarchar (MAX) | Notes on the relation to the specimen | YES |
Depending on:
- CollectionSpecimenRelationInversList
View CollectionSpecimenTransactionRequest
PK and supplementary columns of Table CollectionSpecimenTransaction with TransactionType loan or request the user has access to as a CollectionManager or the user has created and access to as a CollectionRequester
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| TransactionID | int | Unique ID for the table Transaction (= foreign key and part of primary key) | NO |
| SpecimenPartID | int | Optional: If the data set is related to a part of a specimen, the ID of a related part (= foreign key, see table CollectionSpecimenPart) | NO |
| IsOnLoan | bit | True, if a specimen is on loan | YES |
| LogInsertedBy | nvarchar (50) | Name of user to first enter (typ or import) the data. | YES |
| LogInsertedWhen | smalldatetime | Point in time when the data was first entered (typed or imported) into this database. | YES |
Depending on:
- CollectionSpecimenTransaction
- TransactionRequest
View FirstLinesIdentification
Content of table Identification restricted to last identification of a IdentificationUnit
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO |
| IdentificationSequence | smallint | The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid | NO |
| IdentificationDate | datetime | The date of the identification calculated from the entries in IdentificationDay, -Month and -Year | YES |
| IdentificationDay | tinyint | The day of the identification | YES |
| IdentificationMonth | tinyint | The month of the identification | YES |
| IdentificationYear | smallint | The year of the identification. The year may be empty if only the day or month are known. | YES |
| IdentificationDateSupplement | nvarchar (255) | Verbal or additional identification date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’ | YES |
| IdentificationDateCategory | nvarchar (50) | Category of the date of the identification e.g. “system”, “estimated” (= foreign key, see in table CollDateCategory_Enum) | YES |
| VernacularTerm | nvarchar (255) | Name or term other than a taxonomic (= scientific) name, e.g. ‘pine’, ’limestone’, ‘conifer’, ‘hardwood’ | YES |
| TaxonomicName | nvarchar (255) | Valid name of the species (including the taxonomic author where available). Example: ‘Rosa canina L.’ | YES |
| NameURI | varchar (255) | The URI of the taxonomic name, e.g. as provided by the module DiversityTaxonNames. | YES |
| IdentificationCategory | nvarchar (50) | Category of the identification e.g. ‘determination’, ‘confirmation’, ‘absence’ (= foreign key, see table CollIdentificationCategory_Enum) | YES |
| IdentificationQualifier | nvarchar (50) | Qualification of the identification e.g. “cf.”," aff.", “sp. nov.” (= foreign key, see table CollIdentificationQualifier_Enum) | YES |
| TypeStatus | nvarchar (50) | If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum) | YES |
| TypeNotes | nvarchar (MAX) | Notes on the typification of this specimen | YES |
| Notes | nvarchar (MAX) | User defined notes, e.g. the reason for a re-determination / change of the name, etc. | YES |
| ResponsibleName | nvarchar (255) | Name of the person or institution responsible for the determination | YES |
| ResponsibleAgentURI | varchar (255) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. | YES |
| LogCreatedWhen | datetime | Point in time when this data set was created | YES |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data set | YES |
| LogUpdatedWhen | datetime | Point in time when this data set was updated last | YES |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set last | YES |
Depending on:
- FirstLinesIdentificationUnit
- Identification
View Identification_Core2
Content of table Identification restricted to those available for a user
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO |
| IdentificationSequence | smallint | The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid | NO |
| IdentificationDate | datetime | The date of the identification calculated from the entries in IdentificationDay, -Month and -Year | YES |
| IdentificationDay | tinyint | The day of the identification | YES |
| IdentificationMonth | tinyint | The month of the identification | YES |
| IdentificationYear | smallint | The year of the identification. The year may be empty if only the day or month are known. | YES |
| IdentificationDateSupplement | nvarchar (255) | Verbal or additional identification date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’ | YES |
| IdentificationDateCategory | nvarchar (50) | Category of the date of the identification e.g. “system”, “estimated” (= foreign key, see in table CollDateCategory_Enum) | YES |
| VernacularTerm | nvarchar (255) | Name or term other than a taxonomic (= scientific) name, e.g. ‘pine’, ’limestone’, ‘conifer’, ‘hardwood’ | YES |
| TaxonomicName | nvarchar (255) | Valid name of the species (including the taxonomic author where available). Example: ‘Rosa canina L.’ | YES |
| NameURI | varchar (255) | The URI of the taxonomic name, e.g. as provided by the module DiversityTaxonNames. | YES |
| IdentificationCategory | nvarchar (50) | Category of the identification e.g. ‘determination’, ‘confirmation’, ‘absence’ (= foreign key, see table CollIdentificationCategory_Enum) | YES |
| IdentificationQualifier | nvarchar (50) | Qualification of the identification e.g. “cf.”," aff.", “sp. nov.” (= foreign key, see table CollIdentificationQualifier_Enum) | YES |
| TypeStatus | nvarchar (50) | If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum) | YES |
| TypeNotes | nvarchar (MAX) | Notes on the typification of this specimen | YES |
| Notes | nvarchar (MAX) | User defined notes, e.g. the reason for a re-determination / change of the name, etc. | YES |
| ResponsibleName | nvarchar (255) | Name of the person or institution responsible for the determination | YES |
| ResponsibleAgentURI | varchar (255) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. | YES |
Depending on:
- CollectionSpecimenID_Available
- Identification
View IdentificationUnit_Core2
Content of table IdentificationUnit restricted to those available for a user
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| IdentificationUnitID | int | ID of the IdentificationUnit (= part of primary key). Usually one of possibly several organisms present on the collected specimen. Example: parasite with hyperparasite on plant leaf = 3 units, | NO |
| LastIdentificationCache | nvarchar (255) | The last identification as entered in table Identification | NO |
| FamilyCache | nvarchar (255) | A cached value of the family of the taxon of the last identification. Can be set by the editor, if NameURI in table Identification is NULL, otherwise set by the system. | YES |
| OrderCache | nvarchar (255) | A cached value of the order of the taxon of the last identification. Can be set by the editor, if NameURI in table Identification is NULL, otherwise set by the system. | YES |
| TaxonomicGroup | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) | NO |
| OnlyObserved | bit | True, if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. | YES |
| RelatedUnitID | int | The IdentificationUnitID of the organism or substrate on which this organism is growing (= foreign key) | YES |
| RelationType | nvarchar (50) | The relation of a unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key) | YES |
| ColonisedSubstratePart | nvarchar (255) | If a substrate association exists: part of the substrate which is affected in the interaction (e.g. ’leaves’, if a fungus is growing on the leaves of an infected plant) | YES |
| LifeStage | nvarchar (255) | Examples: ‘II, III’ for spore generations of rusts or ‘seed’, ‘seedling’ etc. for higher plants | YES |
| Gender | nvarchar (50) | The sex of the organism, e.g. ‘female’ | YES |
| NumberOfUnits | smallint | The number of units of this organism, e.g. 400 beetles in a bottle | YES |
| ExsiccataNumber | nvarchar (50) | If specimen is an exsiccata: Number of current specimen within the exsiccata series | YES |
| ExsiccataIdentification | smallint | Refers to the IdentificationSequence in Identification (= foreign key). The name under which the collectied specimen or this organism is published within an exsiccata. | YES |
| UnitIdentifier | nvarchar (50) | An identifier for the identification of the unit, e.g. a number painted on a tree within an experimental plot | YES |
| UnitDescription | nvarchar (50) | Description of the unit, especially if not an organism but parts or remnants of it were present or observed, e.g. a nest of an insect or a song of a bird | YES |
| Circumstances | nvarchar (50) | Circumstances of the occurence of the organism | YES |
| DisplayOrder | smallint | The sequence in which the units within this specimen will appear on e.g. a label where the first unit may be printed in the header and others in the text below. 0 means the unit should not appear on a label. | NO |
| Notes | nvarchar (MAX) | Further information on the organism or interaction, e.g. infection symptoms like ‘producing galls’ | YES |
| HierarchyCache | nvarchar (500) | A cached value fo the superior taxonomy of the last identification as derived from a taxonomic data provider | YES |
| RetrievalType | nvarchar (50) | The way the data about the unit were retrieved, e.g. observation, literature | YES |
| ParentUnitID | int | The IdentificationUnitID of a parent organism of which this organism is a child of (= foreign key). | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
| NumberOfUnitsModifier | nvarchar (50) | A modifier for the number of units of this organism, e.g. ca. 400 beetles in a bottle | YES |
Depending on:
- CollectionSpecimenID_Available
- IdentificationUnit
View IdentificationUnitDisplayOrder1
Content of table IdentificationUnit restricted to those available for a user and DisplayOrder = 1
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| IdentificationUnitID | int | ID of the IdentificationUnit (= part of primary key). Usually one of possibly several organisms present on the collected specimen. Example: parasite with hyperparasite on plant leaf = 3 units, | NO |
| LastIdentificationCache | nvarchar (255) | The last identification as entered in table Identification | NO |
| FamilyCache | nvarchar (255) | A cached value of the family of the taxon of the last identification. Can be set by the editor, if NameURI in table Identification is NULL, otherwise set by the system. | YES |
| OrderCache | nvarchar (255) | A cached value of the order of the taxon of the last identification. Can be set by the editor, if NameURI in table Identification is NULL, otherwise set by the system. | YES |
| TaxonomicGroup | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) | NO |
| OnlyObserved | bit | True, if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. | YES |
| RelatedUnitID | int | The IdentificationUnitID of the organism or substrate on which this organism is growing (= foreign key) | YES |
| RelationType | nvarchar (50) | The relation of a unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key) | YES |
| ColonisedSubstratePart | nvarchar (255) | If a substrate association exists: part of the substrate which is affected in the interaction (e.g. ’leaves’, if a fungus is growing on the leaves of an infected plant) | YES |
| LifeStage | nvarchar (255) | Examples: ‘II, III’ for spore generations of rusts or ‘seed’, ‘seedling’ etc. for higher plants | YES |
| Gender | nvarchar (50) | The sex of the organism, e.g. ‘female’ | YES |
| NumberOfUnits | smallint | The number of units of this organism, e.g. 400 beetles in a bottle | YES |
| ExsiccataNumber | nvarchar (50) | If specimen is an exsiccata: Number of current specimen within the exsiccata series | YES |
| ExsiccataIdentification | smallint | Refers to the IdentificationSequence in Identification (= foreign key). The name under which the collectied specimen or this organism is published within an exsiccata. | YES |
| UnitIdentifier | nvarchar (50) | An identifier for the identification of the unit, e.g. a number painted on a tree within an experimental plot | YES |
| Circumstances | nvarchar (50) | Circumstances of the occurence of the organism | YES |
| DisplayOrder | smallint | The sequence in which the units within this specimen will appear on e.g. a label where the first unit may be printed in the header and others in the text below. 0 means the unit should not appear on a label. | NO |
| Notes | nvarchar (MAX) | Further information on the organism or interaction, e.g. infection symptoms like ‘producing galls’ | YES |
Depending on:
- CollectionSpecimenID_UserAvailable
- IdentificationUnit
View ManagerSpecimenPartList
List based on content of table CollectionSpecimenPart restricted to those with an AccessionNumber and available for a CollectionManager
| Column | Data type | Description | Nullable |
|---|---|---|---|
| AccessionNumber | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” | YES |
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) | NO |
| SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). | NO |
| AccessionNumberSpecimen | nvarchar (50) | - | YES |
| AccessionNumberPart | nvarchar (50) | - | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum) | NO |
| CollectionName | nvarchar (50) | - | YES |
Depending on:
- CollectionSpecimen
- CollectionSpecimenPart
- ManagerCollectionList
View ProjectList
List of projects available for a user
| Column | Data type | Description | Nullable |
|---|---|---|---|
| Project | nvarchar (50) | The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) | YES |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO |
| ReadOnly | int | If the user has only read access to data of this project | YES |
| ProjectURI | varchar (255) | The URI of the project, e.g. as provided by the module DiversityProjects. | YES |
Depending on:
- ProjectProxy
- ProjectUser
View ProjectListNotReadOnly
List of projects a user can edit
| Column | Data type | Description | Nullable |
|---|---|---|---|
| Project | nvarchar (50) | The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) | YES |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO |
Depending on:
- ProjectProxy
- ProjectUser
View RequesterSpecimenPartList
List of possible request for a user based on table CollectionSpecimenPart
| Column | Data type | Description | Nullable |
|---|---|---|---|
| AccessionNumber | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” | YES |
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) | NO |
| SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). | NO |
| AccessionNumberSpecimen | nvarchar (50) | - | YES |
| AccessionNumberPart | nvarchar (50) | - | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum) | NO |
| CollectionName | nvarchar (50) | - | YES |
Depending on:
- CollectionSpecimen
- CollectionSpecimenPart
- RequesterCollectionList
View TransactionForeignRequest
Content of table Transaction with TransactionType = request a CollectionManager has access to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy | YES |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out | NO |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing | YES |
| AdministratingCollectionID | int | ID for the collection for which the Manager has the right to administrate the transaction. Corresponds to AdministratingCollectionID in table Transaction. | NO |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. | YES |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material | YES |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift | YES |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift | YES |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift | YES |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift | YES |
| NumberOfUnits | smallint | The number of units which were (initially) included in the transaction | YES |
| Investigator | nvarchar (50) | The investigator for whose study a transacted material was sent | YES |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner | YES |
| BeginDate | datetime | Date when the transaction started | YES |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted | YES |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner | YES |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page | YES |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction | YES |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) | YES |
Depending on:
- CollectionManager
- Transaction
View TransactionList
Content of table Transaction missing a AdministratingCollectionID resp. those available for a CollectionManager
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy | YES |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out | NO |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing | YES |
| AdministratingCollectionID | int | ID for the collection for which the Manager has the right to administrate the transaction. Corresponds to AdministratingCollectionID in table Transaction. | NO |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction | YES |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. | YES |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material | YES |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift | YES |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift | YES |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift | YES |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift | YES |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents | YES |
| NumberOfUnits | smallint | The number of units which were (initially) included in the transaction | YES |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent | YES |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner | YES |
| BeginDate | datetime | Date when the transaction started | YES |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted | YES |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner | YES |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page | YES |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction | YES |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) | YES |
| DateSupplement | nvarchar (100) | Verbal or additional date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. | YES |
Depending on:
- CollectionManager
- Transaction
View TransactionList_H7
Content of table Transaction missing a AdministratingCollectionID resp. those available for a CollectionManager including a hierarchy with up to 7 levels
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy | YES |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out | NO |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| HierarchyDisplayText | nvarchar (1621) | Hierarchy of the transaction represented by the TransactionTitle separated by " | " for up to 7 levels |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing | YES |
| AdministratingCollectionID | int | ID for the collection for which the Manager has the right to administrate the transaction. Corresponds to AdministratingCollectionID in table Transaction. | NO |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction | YES |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. | YES |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material | YES |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift | YES |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift | YES |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift | YES |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift | YES |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents | YES |
| NumberOfUnits | int | The number of units which were (initially) included in the transaction | YES |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent | YES |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner | YES |
| BeginDate | datetime | Date when the transaction started | YES |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted | YES |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner | YES |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page | YES |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction | YES |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) | YES |
| DateSupplement | nvarchar (100) | Verbal or additional date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. | YES |
Depending on:
- CollectionManager
- Transaction
View TransactionPermit
Content of table Transaction restricted to those with TransactionType = permit
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out | NO |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing | YES |
| AdministratingCollectionID | int | ID of the collection which is responsible for the administration of the transaction. | NO |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. | YES |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material | YES |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation | YES |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift | YES |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift | YES |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift | YES |
| NumberOfUnits | int | The number of units which were (initially) included in the transaction | YES |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner | YES |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent | YES |
| BeginDate | datetime | Date when the transaction started | YES |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted | YES |
| DateSupplement | nvarchar (100) | Verbal or additional date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. | YES |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page | YES |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents | YES |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction | YES |
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
Depending on:
- Transaction
View TransactionRegulation
Content of table Transaction restricted to those with TransactionType = regulation a user has access to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
Depending on:
- Transaction
- TransactionList
View TransactionRequest
Content of table Transaction with TransactionType loan or request the user has access to as a CollectionManager or the user has created and access to as a CollectionRequester
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy | YES |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out | NO |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing | YES |
| AdministratingCollectionID | int | ID for the collection for which the Manager has the right to administrate the transaction. Corresponds to AdministratingCollectionID in table Transaction. | NO |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. | YES |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material | YES |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift | YES |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift | YES |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift | YES |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift | YES |
| NumberOfUnits | smallint | The number of units which were (initially) included in the transaction | YES |
| Investigator | nvarchar (50) | The investigator for whose study a transacted material was sent | YES |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner | YES |
| BeginDate | datetime | Date when the transaction started | YES |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted | YES |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner | YES |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page | YES |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction | YES |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) | YES |
Depending on:
- TransactionForeignRequest
- TransactionUserRequest
View TransactionUserRequest
Content of table Transaction with TransactionType loan or request a CollectionRequester has created and access to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) | NO |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy | YES |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out | NO |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface | NO |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing | YES |
| AdministratingCollectionID | int | ID for the collection for which the requester has the right to request specimen. Corresponds to AdministratingCollectionID in table Transaction. | NO |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction | YES |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. | YES |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material | YES |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift | YES |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift | YES |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift | YES |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift | YES |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) | YES |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift | YES |
| NumberOfUnits | smallint | The number of units which were (initially) included in the transaction | YES |
| Investigator | nvarchar (50) | The investigator for whose study a transacted material was sent | YES |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner | YES |
| BeginDate | datetime | Date when the transaction started | YES |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted | YES |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner | YES |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page | YES |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction | YES |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) | YES |
Depending on:
- CollectionRequester
- Transaction
View UserGroups
List of the groups the users are asigned to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CombinedNameCache | nvarchar (255) | The short name of the user, e.g. P. Smith | NO |
| LoginName | nvarchar (50) | A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc. | NO |
| UserGroup | nvarchar (128) | - | NO |
Depending on:
- UserProxy
View ViewBaseURL
Content of function dbo.BaseURL()
| Column | Data type | Description | Nullable |
|---|---|---|---|
| BaseURL | varchar (255) | - | YES |
Depending on:
- BaseURL
View ViewCollectionEventImage
Content of table CollectionEventImage converting XML to nvarchar and excluding Log columns
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionEventID | int | Part of primay key, refers to unique ID for the table CollectionEvent (= foreign key) | NO |
| URI | varchar (255) | The complete URI address of the image. This is only a cached value, if ResourceID is available and referring to the module DiversityResources | NO |
| ResourceURI | varchar (255) | The URI of the resource (e.g. see module DiversityResources) | YES |
| ImageType | nvarchar (50) | Type of the image, e.g. map | YES |
| Notes | nvarchar (MAX) | Notes to this image concerning the CollectionEvent | YES |
| Description | nvarchar (MAX) | Description of the image | YES |
| Title | nvarchar (500) | Title of the resource | YES |
| IPR | nvarchar (500) | Intellectual Property Rights; the rights given to persons for their intellectual property | YES |
| CreatorAgent | nvarchar (500) | Person or organization originally creating the resource | YES |
| CreatorAgentURI | varchar (255) | Link to the module DiversityAgents | YES |
| CopyrightStatement | nvarchar (500) | Notice on rights held in and for the resource | YES |
| LicenseType | nvarchar (500) | Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses | YES |
| InternalNotes | nvarchar (500) | Internal notes which should not be published e.g. on websites | YES |
| LicenseHolder | nvarchar (500) | The person or institution holding the license | YES |
| LicenseHolderAgentURI | nvarchar (500) | The link to a module containing futher information on the person or institution holding the license | YES |
| LicenseYear | nvarchar (50) | The year of license declaration | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
Depending on:
- CollectionEventImage
View ViewCollectionEventSeriesImage
Content of table CollectionEventSeriesImage converting XML to nvarchar and excluding Log columns
| Column | Data type | Description | Nullable |
|---|---|---|---|
| SeriesID | int | Unique ID for the table CollectionEventSeries (= foreign key and part of primary key) | NO |
| URI | varchar (255) | The complete URI address of the image. This is only a cached value, if ResourceID is available and referring to the module DiversityResources | NO |
| ResourceURI | varchar (255) | The URI of the resource (e.g. see module DiversityResources) | YES |
| ImageType | nvarchar (50) | Type of the image, e.g. map | YES |
| Notes | nvarchar (MAX) | Notes to this image of the collection site | YES |
| Description | nvarchar (MAX) | Description of the image | YES |
| Title | nvarchar (500) | Title of the resource | YES |
| IPR | nvarchar (500) | Intellectual Property Rights; the rights given to persons for their intellectual property | YES |
| CreatorAgent | nvarchar (500) | Person or organization originally creating the resource | YES |
| CreatorAgentURI | varchar (255) | Link to the module DiversityAgents | YES |
| CopyrightStatement | nvarchar (500) | Notice on rights held in and for the resource | YES |
| LicenseType | nvarchar (500) | Type of an official or legal permission to do or own a specified thing, e.g. Creative Common licenses | YES |
| InternalNotes | nvarchar (500) | Internal notes which should not be published e.g. on websites | YES |
| LicenseHolder | nvarchar (500) | The person or institution holding the license | YES |
| LicenseHolderAgentURI | nvarchar (500) | The link to a module containing futher information on the person or institution holding the license | YES |
| LicenseYear | nvarchar (50) | The year of license declaration | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
Depending on:
- CollectionEventSeriesImage
View ViewCollectionImage
Content of table Collection converting XML to nvarchar and excluding Log columns
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionID | int | Refers to the ID of Collection (= foreign key and part of primary key) | NO |
| URI | varchar (255) | The complete URI address of the image. | NO |
| ImageType | nvarchar (50) | Type of the image, e.g. label | YES |
| Notes | nvarchar (MAX) | Notes on the collection image | YES |
| Description | nvarchar (MAX) | Description of the image | YES |
| Title | nvarchar (500) | Title of the resource | YES |
| IPR | nvarchar (500) | Intellectual Property Rights; the rights given to persons for their intellectual property | YES |
| CreatorAgent | nvarchar (500) | Person or organization originally creating the resource | YES |
| CreatorAgentURI | varchar (255) | Link to the module DiversityAgents | YES |
| CopyrightStatement | nvarchar (500) | Notice on rights held in and for the resource | YES |
| LicenseType | nvarchar (500) | Type of an official or legal permission to do or own a specified thing, e.g. Creative Common licenses | YES |
| InternalNotes | nvarchar (500) | Internal notes which should not be published e.g. on websites | YES |
| LicenseHolder | nvarchar (500) | The person or institution holding the license | YES |
| LicenseHolderAgentURI | nvarchar (500) | The link to a module containing futher information on the person or institution holding the license | YES |
| LicenseYear | nvarchar (50) | The year of license declaration | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
Depending on:
- CollectionImage
View ViewCollectionSpecimenImage
Content of table CollectionSpecimenImage converting XML to nvarchar and excluding Log columns
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| URI | varchar (255) | The complete URI address of the image. This is only a cached value, if ResourceID is available and referring to the module DiversityResources | NO |
| ResourceURI | varchar (255) | The URI of the image, e.g. as stored in the module DiversityResources. | YES |
| SpecimenPartID | int | Optional: If the data set is not related to a part of a specimen, the ID of a related part (= foreign key) | YES |
| IdentificationUnitID | int | If image refers to only one out of several IdentificationUnits for a specimen, refers to the ID of an IdentificationUnit for a collection specimen (= foreign key) | YES |
| ImageType | nvarchar (50) | Type of the image, e.g. photograph | YES |
| Notes | nvarchar (MAX) | Notes on the specimen image | YES |
| Description | nvarchar (MAX) | Description of the image | YES |
| Title | nvarchar (500) | Title of the resource | YES |
| IPR | nvarchar (500) | Intellectual Property Rights; the rights given to persons for their intellectual property | YES |
| CreatorAgent | nvarchar (500) | Person or organization originally creating the resource | YES |
| CreatorAgentURI | varchar (255) | Link to the module DiversityAgents | YES |
| CopyrightStatement | nvarchar (500) | Notice on rights held in and for the resource | YES |
| LicenseType | nvarchar (500) | Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses | YES |
| LicenseURI | varchar (500) | The URI of the license for the resource | YES |
| LicenseHolder | nvarchar (500) | The person or institution holding the license | YES |
| LicenseHolderAgentURI | nvarchar (500) | The link to a module containing futher information on the person or institution holding the license | YES |
| LicenseYear | nvarchar (50) | The year of license declaration | YES |
| LicenseNotes | nvarchar (500) | Notice on license for the resource | YES |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES |
| DisplayOrder | int | The order in which the images should be shown in a interface | YES |
| InternalNotes | nvarchar (500) | Internal notes which should not be published e.g. on websites | YES |
Depending on:
- CollectionSpecimenImage
View ViewDiversityWorkbenchModule
Content of function dbo.DiversityWorkbenchModule()
| Column | Data type | Description | Nullable |
|---|---|---|---|
| DiversityWorkbenchModule | nvarchar (50) | - | YES |
Depending on:
- DiversityWorkbenchModule
View ViewIdentificationUnitGeoAnalysis
Content of table IdentificationUnitGeoAnalysis converting geography and geometry to nvarchar and excluding Log columns
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO |
| AnalysisDate | datetime | The date of the analysis | NO |
| Geography | nvarchar (MAX) | The geography where the organism resp. object was located according to WGS84, e.g. a point (latitide, longitude and altitude) | YES |
| Geometry | nvarchar (MAX) | The geometry of the place the organism resp. object was observed, e.g. an area | YES |
| ResponsibleName | nvarchar (255) | Name of the person or institution responsible for the determination | YES |
| ResponsibleAgentURI | varchar (255) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. | YES |
| Notes | nvarchar (MAX) | Notes on this analysis | YES |
Depending on:
- IdentificationUnitGeoAnalysis