Diversity Collection
TABLES
The following objects are not included:
- Logging tables
- Enumeration tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
Table Analysis
Analysis types used within the database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) | NO | - |
| AnalysisParentID | int | Analysis ID of the parent analysis, if it belongs to a certain type documented in this table | YES | Refers to table Analysis |
| DisplayText | nvarchar (50) | Name of the analysis as e.g. shown in user interface | YES | - |
| Description | nvarchar (MAX) | Description of the analysis | YES | - |
| MeasurementUnit | nvarchar (50) | The measurement unit used for the analysis, e.g. mm, µmol, kg | YES | - |
| Notes | nvarchar (MAX) | Notes on this analysis | YES | - |
| AnalysisURI | varchar (255) | URI referring to an external documentation of the analysis | YES | - |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entriesDefault value: (0) | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table AnalysisResult
Value lists for analysis types with predefined values, e.g. “0, 1, 2, 3, …” for Red list category. Includes description etc. for the values in the list.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) | NO | Refers to table Analysis |
| AnalysisResult | nvarchar (255) | The categorized value of the analysis | NO | - |
| Description | nvarchar (500) | Description of enumerated object displayed in the user interface | YES | - |
| DisplayText | nvarchar (50) | Short abbreviated description of the object displayed in the user interface | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| Notes | nvarchar (500) | Internal development notes on usage, definition, etc. of an enumerated object | YES | - |
| LogInsertedBy | nvarchar (50) | Name of user to first enter (typ or import) the data.Default value: suser_sname() | YES | - |
| LogInsertedWhen | smalldatetime | Point in time when the data was first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data last.Default value: suser_sname() | YES | - |
| LogUpdatedWhen | smalldatetime | Point in time when this data was updated last.Default value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Analysis
Table AnalysisTaxonomicGroup
The types of analysis which are available for a taxonomic group
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| AnalysisID | int | Analysis ID, foreign key of table Analysis. | NO | Refers to table Analysis |
| TaxonomicGroup | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) | NO | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Analysis
Table Annotation
Annotations to datasets in the database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| AnnotationID | int | ID of the annotation (primary key) | NO | - |
| ReferencedAnnotationID | int | If an annotation refers to another annotation, the ID of the referred annotation | YES | Refers to table Annotation |
| AnnotationType | nvarchar (50) | The type of the annotation as defined in AnnotationType_Enum, e.g. ReferenceDefault value: N’Annotation' | NO | Refers to table AnnotationType_Enum |
| 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 | - |
| ReferencedID | int | The ID of the data set in the table the annotation refers to | NO | - |
| ReferencedTable | nvarchar (500) | The name of the table the annotation refers to | NO | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- AnnotationType_Enum
trgInsAnnotation
updating the logging columns
Table AnonymCollector
Anonyms for collectors of whom the names should not be published
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectorsName | nvarchar (400) | The name of the collector, PK | NO | - |
| Anonymisation | nvarchar (50) | The anonymisation phrase for the collector | YES | - |
Table CacheDatabase2
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 CacheDescription
Table for temperary storage of description of database objects derived e.g. from tables Entity, EntityRepresentation etc.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TableName | varchar (50) | The name of the table | NO | - |
| ColumnName | varchar (50) | The name of the table column | NO | - |
| LanguageCode | varchar (50) | The language code for the descriptionDefault value: ’en-US' | NO | - |
| Context | nvarchar (50) | A context e.g. as definded in table EntityContext_EnumDefault value: ‘General’ | NO | - |
| DisplayText | nvarchar (50) | The text for the table or column as shown e.g. in a user interface | YES | - |
| Abbreviation | nvarchar (20) | The abbreviation for the table or column as shown e.g. in a user interface | YES | - |
| Description | nvarchar (MAX) | The description for the table column | YES | - |
| ID | int | A unique ID | NO | - |
| Type | varchar (20) | Type of the entryDefault value: ‘COLUMN’ | YES | - |
| Schema | varchar (100) | Schema of the entryDefault value: ‘dbo’ | YES | - |
Table Collection
The collections where the specimen are stored
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) | NO | - |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection | YES | Refers to table Collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information | NO | - |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum | YES | - |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection | YES | - |
| AdministrativeContactAgentURI | varchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents | YES | - |
| Description | nvarchar (MAX) | A short description of the collection | YES | - |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection | YES | - |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection | YES | - |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry | YES | - |
| LocationGeometry | geometry (MAX) | Geometry of the collection within the floor plan | YES | - |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors | YES | - |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location | YES | Refers to table Collection |
| LocationPlanDate | datetime | The date when the plan for the collection has been created | YES | - |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. | YES | Refers to table CollCollectionType_Enum |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollCollectionType_Enum
Table CollectionAgent
The collector(s) of CollectionSpecimens
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimen |
| 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 systemDefault value: sysdatetime() | 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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimen
trgInsCollectionAgent
Setting the version of the dataset
Table CollectionEvent
The event where and when the specimen were collected
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) | NO | - |
| Version | int | The version of the data set. Automatically set by the system.Default value: (1) | NO | - |
| SeriesID | int | The ID of the related expedition. Relates to the PK of the table CollectionExpedition (foreign key). | YES | Refers to table CollectionEventSeries |
| 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 | Refers to table CollEventDateCategory_Enum |
| 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 | - |
| 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 | - |
| ReferenceDetails | nvarchar (50) | The exact location within the reference, e.g. pages, plates | 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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEventSeries
- CollEventDateCategory_Enum
trgInsCollectionEvent
Setting the date in case of valid date columns
Table CollectionEventImage
The images showing the collection site resp. place of the observations
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Part of primay key, refers to unique ID for the table CollectionEvent (= foreign key) | NO | Refers to table CollectionEvent |
| 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 | Refers to table CollEventImageType_Enum |
| Notes | nvarchar (MAX) | Notes to this image concerning the CollectionEvent | YES | - |
| Description | xml (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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEvent
- CollEventImageType_Enum
trgInsCollectionEventImage
Setting the version of the dataset
Table CollectionEventLocalisation
The geographic localisation of a CollectionEvent
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Refers to the ID of CollectionEvent (= foreign key and part of primary key) | NO | Refers to table CollectionEvent |
| LocalisationSystemID | int | Refers to the ID of LocalisationSystem (= foreign key and part of primary key) | NO | Refers to table LocalisationSystem |
| 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 | - |
| Geography | geography | The geography of the localisation | YES | - |
| RecordingMethod | nvarchar (500) | The method or device used for the recording of the localisation | 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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEvent
- LocalisationSystem
trgInsCollectionEventLocalisation
Setting missing geographical values on base of given values
Table CollectionEventMethod
The methods used during a collection event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Refers to ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionEvent |
| MethodID | int | ID of the setting, part of primary key | NO | Refers to table Method |
| MethodMarker | nvarchar (50) | A marker for the method, part of primary keyDefault value: ‘1’ | NO | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEvent
- Method
Table CollectionEventParameterValue
The values of the parameter of the methods used within a collection event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Unique ID for the table CollectionEvent (= foreign key and part of primary key) | NO | Refers to table CollectionEventMethod |
| MethodID | int | ID of the method tool. Referes to table Method (= foreign key and part of primary key) | NO | Refers to table CollectionEventMethod and table Parameter |
| MethodMarker | nvarchar (50) | A marker for the method, part of primary keyDefault value: ‘1’ | NO | Refers to table CollectionEventMethod |
| ParameterID | int | ID of the parameter tool. Referes to table Parameter (= foreign key and part of primary key) | NO | Refers to table Parameter |
| Value | nvarchar (MAX) | The value of the parameter, if different of the default value as documented in the table Parameter | YES | - |
| Notes | nvarchar (MAX) | Notes concerning the value of the parameter | YES | - |
| LogInsertedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogInsertedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | YES | - |
Depending on:
- CollectionEventMethod
- Parameter
Table CollectionEventProperty
A property of a collection site, e.g. exposition, slope, vegetation. May refer to Diversity Workbench module DiversityScientificTerms
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Refers to the ID of CollectionEvent (= foreign key and part of primary key) | NO | Refers to table CollectionEvent |
| PropertyID | int | The ID of the property of the collection site, foreign key, see table Property | NO | Refers to table Property |
| DisplayText | nvarchar (255) | The text for the property as shown e.g. in a user interface | YES | - |
| PropertyURI | varchar (255) | URI referring to an external data source e.g. DiversityTerminology | YES | - |
| PropertyHierarchyCache | nvarchar (MAX) | A cached text of the complete name of the descriptor including superior categories, if present | YES | - |
| PropertyValue | nvarchar (255) | The value of a captured feature, e.g. temperature, pH, vegetation etc. If there is a range, this is the lower or first value | 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 | - |
| Notes | nvarchar (MAX) | Notes on the property of the colletion site. | YES | - |
| AverageValueCache | float | For numeric values - a cached average value according to the | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEvent
- Property
trgInsCollectionEventProperty
Setting the version of the dataset
Table CollectionEventRegulation
Regulation applied to a collection event
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionEventID | int | Part of primay key, refers to unique ID for the table CollectionEvent (= foreign key) | NO | Refers to table CollectionEvent |
| Regulation | nvarchar (400) | Regulation as defined in the table Regulation. Used to ensure, that user checked correct entry with authorized stuff | NO | - |
| TransactionID | int | Refers to unique TransactionID for the table Transaction (= foreign key) | YES | Refers to table Transaction |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEvent
- Transaction
Table CollectionEventSeries
The series whithin which collection events take place
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| SeriesID | int | Primary key. The ID for this series of collection events (= primary key) | NO | - |
| SeriesParentID | int | The ID of the superior series of collection events | YES | Refers to table CollectionEventSeries |
| Description | nvarchar (MAX) | The description of the series of collection events as it will be printed on e.g. the label | NO | - |
| SeriesCode | nvarchar (50) | The user defined code for a series of collection events | YES | - |
| Notes | nvarchar (MAX) | Notes on this series of collection events | YES | - |
| Geography | geography | The geography of the series of collection events | YES | - |
| DateStart | datetime | Point in time when the series of collection events started | YES | - |
| DateEnd | datetime | Point in time when the series of collection events ended | YES | - |
| DateCache | datetime | The first date of the depending events, used for sorting the expeditions [controlled by the database] | YES | - |
| DateSupplement | 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 series comprises a period. The time of the event, if necessary. | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table CollectionEventSeriesDescriptor
The Descriptors for the CollectionEventSeries
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| SeriesID | int | Unique ID for the CollectionEventSeries (foreign key + part of primary key) | NO | Refers to table CollectionEventSeries |
| DescriptorID | int | Unique ID for the descriptor, Part of PK | NO | - |
| Descriptor | nvarchar (200) | The DescriptorDefault value: '’ | NO | - |
| URL | varchar (500) | URL of the Descriptor. In case of a module related Descriptor, the link to the module entry resp. the related webserviceDefault value: '’ | YES | - |
| DescriptorType | nvarchar (50) | Type of the Descriptor as described in table CollectionEventSeriesDescriptorType_EnumDefault value: N’Descriptor’ | YES | Refers to table CollEventSeriesDescriptorType_Enum |
| LogInsertedBy | nvarchar (50) | Name of user who first entered (typed or imported) the data.Default value: suser_sname() | 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) | Name of user who last updated the data.Default value: suser_sname() | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEventSeries
- CollEventSeriesDescriptorType_Enum
Table CollectionEventSeriesImage
The images showing the sites of a series of collection events, e.g. an expedition
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| SeriesID | int | Unique ID for the table CollectionEventSeries (= foreign key and part of primary key) | NO | Refers to table CollectionEventSeries |
| 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 | Refers to table CollEventSeriesImageType_Enum |
| Notes | nvarchar (MAX) | Notes to this image of the collection site | YES | - |
| Description | xml (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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionEventSeries
- CollEventSeriesImageType_Enum
Table CollectionExternalDatasource
CollectionExternalDatasource document the sources of the names.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| 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) | NO | - |
| ExternalDatasourceName | nvarchar (255) | The name of the data collection which has been integrated or can be linked to for further analysis | YES | - |
| ExternalDatasourceVersion | nvarchar (255) | The version of this data collection (either official version number or dates when the collection was integrated) | YES | - |
| Rights | nvarchar (500) | A description of copyright agreements or permission to use data from the external database | YES | - |
| ExternalDatasourceAuthors | nvarchar (200) | The persons or institutions responsible for the external database | YES | - |
| ExternalDatasourceURI | nvarchar (300) | The URI of the database provider or the external database | YES | - |
| ExternalDatasourceInstitution | nvarchar (300) | The institution responsible for the external database | YES | - |
| InternalNotes | nvarchar (1500) | Additional notes on this data collection | YES | - |
| ExternalAttribute_NameID | nvarchar (255) | The table and field name in the external data collection to which CollectionExternalID refers | YES | - |
| PreferredSequence | tinyint | For selection in e.g. picklists: of several equal names only the name from the source with the lowest preferred sequence will be provided. | YES | - |
| Disabled | bit | If this source should be disabled for selection of names e.g. in picklists | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table CollectionImage
The images showing the collection
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionID | int | Refers to the ID of Collection (= foreign key and part of primary key) | NO | Refers to table Collection |
| URI | varchar (255) | The complete URI address of the image. | NO | - |
| ImageType | nvarchar (50) | Type of the image, e.g. label | YES | Refers to table CollCollectionImageType_Enum |
| Notes | nvarchar (MAX) | Notes on the collection image | YES | - |
| Description | xml (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 | - |
| LocationGeometry | geometry (MAX) | Geometry of the collection e.g. within a floor plan | YES | - |
| RecordingDate | datetime | The recording date of the resource | YES | - |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES | - |
| LogInsertedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogInsertedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollCollectionImageType_Enum
- Collection
Table CollectionManager
Collection managers within DiversityCollection responsible for specimen transactions
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LoginName | nvarchar (50) | A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc.. | NO | - |
| AdministratingCollectionID | int | ID for the collection for which the Manager has the right to administrate the transaction. Corresponds to AdministratingCollectionID in table Transaction. | NO | Refers to table Collection |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Collection
Table CollectionProject
The projects within which the collection specimen were placed
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimen |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO | Refers to table ProjectProxy |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimen
- ProjectProxy
trgInsCollectionProject
Setting LastChanges in table ProjectProxy
Table CollectionSpecimen
The data directly attributed to the collected specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) | NO | - |
| Version | int | The version of the data setDefault value: (1) | NO | - |
| CollectionEventID | int | Refers to the ID of table CollectionEvent (= foreign key and part of primary key) | YES | Refers to table CollectionEvent |
| 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 | Refers to table CollDateCategory_Enum |
| 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 | Refers to table CollLabelType_Enum |
| LabelTranscriptionState | nvarchar (50) | The state of the transcription of a label into the database: ‘Not started’, ‘incomplete’, ‘complete’ | YES | Refers to table CollLabelTranscriptionState_Enum |
| LabelTranscriptionNotes | nvarchar (MAX) | 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 | - |
| 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 nullDefault value: N’Withhold by default’ | 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 | Refers to table CollectionExternalDatasource |
| 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 createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollDateCategory_Enum
- CollectionEvent
- CollectionExternalDatasource
- CollLabelTranscriptionState_Enum
- CollLabelType_Enum
Table CollectionSpecimenImage
The images of a collection specimen or of an organism (stored in table IdentificationUnit) within this specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimen and table IdentificationUnit |
| 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 | Refers to table CollectionSpecimenPart |
| 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 | Refers to table IdentificationUnit |
| ImageType | nvarchar (50) | Type of the image, e.g. photograph | YES | Refers to table CollSpecimenImageType_Enum |
| Notes | nvarchar (MAX) | Notes on the specimen image | YES | - |
| Description | xml (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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimen
- CollectionSpecimenPart
- CollSpecimenImageType_Enum
- IdentificationUnit
CollectionSpecimenImage_URI
Deprecated
trgInsCollectionSpecimenImage
Setting the version of the dataset
Table CollectionSpecimenImageProperty
The properties of images of a collection specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimenImage |
| 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 | Refers to table CollectionSpecimenImage |
| Property | varchar (255) | The property of the image | NO | - |
| Description | nvarchar (MAX) | If description of the property of the image | YES | - |
| ImageArea | geometry (MAX) | The area in the image the property refers to | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
| PropertyURI | varchar (500) | The URI of the property of the image, e.g. a link to module DiversityScientificTerms | YES | - |
Depending on:
- CollectionSpecimenImage
Table CollectionSpecimenPart
Parts of a collected specimen. Includes a possible hierarchy of the parts
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimen and table CollectionSpecimenPart |
| 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 | Refers to table CollectionSpecimenPart |
| 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 | Refers to table Collection |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum)Default value: N’specimen’ | NO | Refers to table CollMaterialCategory_Enum |
| 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 | - |
| 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 | - |
| StorageContainer | nvarchar (500) | The container in which the part is stored | YES | - |
| ResponsibleName | nvarchar (255) | Name of the person or institution responsible for the preparation | YES | - |
| ResponsibleAgentURI | varchar (255) | URI of the person or institution responsible for the preparation (= foreign key) as stored in the module DiversityAgents | YES | - |
| Notes | nvarchar (MAX) | Notes on the storage of the sample | YES | - |
| DataWithholdingReason | nvarchar (255) | If the specimen part is withhold, the reason for withholding the data, otherwise null. | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Collection
- CollectionSpecimen
- CollMaterialCategory_Enum
trgInsCollectionSpecimenPart
Setting the version of the dataset
Table CollectionSpecimenPartDescription
Description of the specimen part with a standardized vocabulary as defined in the module DiversityScientificTerms
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimenPart |
| SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). | NO | Refers to table CollectionSpecimenPart |
| PartDescriptionID | int | ID of the description (Part of primary key) | NO | - |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | YES | - |
| Description | nvarchar (MAX) | The descrition of the part. Cached value if DescriptionTermURI is used | YES | - |
| DescriptionTermURI | varchar (500) | Link to a external datasource like a webservice or the module DiversityScientificTerms where the description is documented | YES | - |
| Notes | nvarchar (MAX) | Notes about this description | YES | - |
| DescriptionHierarchyCache | nvarchar (MAX) | Hierarchy of the description. For values linked to a module, a cached value provided by the module | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimenPart
Table CollectionSpecimenProcessing
The processing which was applied to a collected specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimen and table CollectionSpecimenPart |
| SpecimenProcessingID | int | Unique ID of the processing of a specimen or part of a specimen, part of primary key | NO | - |
| ProcessingDate | datetime | Point in time of the start of the processing | YES | - |
| ProcessingID | int | ID of the processing method. Refers to ProcessingID in table Processing (foreign key)Default value: (1) | NO | Refers to table Processing |
| Protocoll | nvarchar (100) | The label of the processing protocol | YES | - |
| 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) | YES | Refers to table CollectionSpecimenPart |
| ProcessingDuration | varchar (50) | The duration of the processing including the unit (e.g. 5 min) or the end of the processing starting at the processing date (e.g. 23.05.2008) | 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 the processing | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
| ToolUsage | xml (MAX) | The tools used for the processing and their usage or settings. | YES | - |
Depending on:
- CollectionSpecimen
- CollectionSpecimenPart
- Processing
trgInsCollectionSpecimenProcessing
Setting the version of the dataset
Table CollectionSpecimenProcessingMethod
The methods used for a processing of a specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= Foreign key and part of primary key) | NO | Refers to table CollectionSpecimenProcessing |
| SpecimenProcessingID | int | Refers to the ID of the specimen processing (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimenProcessing |
| MethodID | int | ID of the method, part of primary key | NO | Refers to table MethodForProcessing |
| MethodMarker | nvarchar (50) | A marker for the method, part of primary keyDefault value: ‘1’ | NO | - |
| ProcessingID | int | ID of the processing. Refers to ProcessingID in table Processing (foreign key)Default value: (1) | NO | Refers to table MethodForProcessing |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimenProcessing
- MethodForProcessing
Table CollectionSpecimenProcessingMethodParameter
The parameter values of a method used for the processing of a specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= Foreign key and part of primary key) | NO | Refers to table CollectionSpecimenProcessingMethod |
| SpecimenProcessingID | int | Refers to ID of CollectionSpecimenProcessing (= Foreign key and part of primary key) | NO | Refers to table CollectionSpecimenProcessingMethod |
| ProcessingID | int | ID of the processing. Refers to ProcessingID in table Processing (= Foreign key and part of primary key)Default value: (1) | NO | Refers to table CollectionSpecimenProcessingMethod |
| MethodID | int | ID of the method (= Foreign key and part of primary key) | NO | Refers to table CollectionSpecimenProcessingMethod and table Parameter |
| MethodMarker | nvarchar (50) | A marker for the method, part of primary keyDefault value: ‘1’ | NO | Refers to table CollectionSpecimenProcessingMethod |
| ParameterID | int | ID of the parameter. Referes to table Parameter (= Foreign key and part of primary key) | NO | Refers to table Parameter |
| Value | nvarchar (MAX) | The value of the parameter if different of the default value as documented in the table Parameter | NO | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimenProcessingMethod
- Parameter
Table CollectionSpecimenReference
A reference related to the collection specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to unique ID of collection specimen record (part of primary key) | NO | Refers to table CollectionSpecimen and table IdentificationUnit |
| ReferenceID | int | Unique reference ID for the reference record (part of primary key) | NO | - |
| ReferenceTitle | nvarchar (400) | The title of the publication related to the specimen or parts of it. Note that this is only a cached value where ReferenceURI is present | NO | - |
| ReferenceURI | varchar (500) | URI of the reference, e.g. a connection to the module DiversityReferences | YES | - |
| IdentificationUnitID | int | If relation refers to a certain organism within a specimen, the ID of an IdentificationUnit (= foreign key) | YES | Refers to table IdentificationUnit |
| IdentificationSequence | smallint | Referes to table Identification: The sequence of the identifications. | YES | - |
| SpecimenPartID | int | If the relation refers to a part of a specimen, the ID of a related part (= foreign key) | YES | Refers to table CollectionSpecimenPart |
| ReferenceDetails | nvarchar (500) | The exact location within the reference, e.g. pages, plates | YES | - |
| Notes | nvarchar (MAX) | Notes about the reference | 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 | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimen
- CollectionSpecimenPart
- IdentificationUnit
Table CollectionSpecimenRelation
The relations of a collection specimen to other collection specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Unique reference ID for the collection specimen record (primary key) | NO | Refers to table CollectionSpecimen and table IdentificationUnit |
| RelatedSpecimenURI | varchar (255) | URI of the related specimen | NO | - |
| RelatedSpecimenDisplayText | varchar (255) | The name of a related specimen as shown e.g. in a user interface | NO | - |
| RelationType | nvarchar (50) | Type of the relation between the specimen (= foreign key, see table CollRelationType_Enum) | YES | Refers to table CollSpecimenRelationType_Enum |
| RelatedSpecimenCollectionID | int | ID of the Collection as stored in table Collection (= foreign key, see table Collection) | YES | Refers to table Collection |
| RelatedSpecimenDescription | nvarchar (MAX) | Description of the related specimen | YES | - |
| IdentificationUnitID | int | If relation refers to a certain organism within a specimen, the ID of an IdentificationUnit (= foreign key) | YES | Refers to table IdentificationUnit |
| SpecimenPartID | int | If the relation refers to a part of a specimen, the ID of a related part (= foreign key) | YES | Refers to table CollectionSpecimenPart |
| Notes | nvarchar (MAX) | Notes on the relation to the specimen | YES | - |
| IsInternalRelationCache | bit | If the relation represents a connection between specimen in this databaseDefault value: (1) | NO | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Collection
- CollectionSpecimen
- CollectionSpecimenPart
- CollSpecimenRelationType_Enum
- IdentificationUnit
trgInsCollectionSpecimenRelation
Setting the version of the dataset
Table CollectionSpecimenTransaction
The transactions in which a specimen was involved
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimenPart |
| TransactionID | int | Unique ID for the table Transaction (= foreign key and part of primary key) | NO | Refers to table Transaction |
| 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 | Refers to table CollectionSpecimenPart |
| AccessionNumber | nvarchar (255) | Accession number which has been assigen to the part of the specimen, e.g. in connection with a former inventory. | YES | - |
| TransactionReturnID | int | Unique ID for the table Transaction (= foreign key) for the return of a part that has been on loan | YES | - |
| TransactionTitle | nvarchar (200) | Title as in related table Transaction. Used for validation of correct entry of transaction with type regulation (see insert trigger) | YES | - |
| IsOnLoan | bit | True, if a specimen is on loan | YES | - |
| LogInsertedBy | nvarchar (50) | Name of user to first enter (typ or import) the data.Default value: suser_sname() | YES | - |
| LogInsertedWhen | smalldatetime | Point in time when the data was first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set last.Default value: suser_sname() | YES | - |
| LogUpdatedWhen | smalldatetime | Point in time when this data set was updated last.Default value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimenPart
- Transaction
trgInsCollectionSpecimenTransaction
Rollback of regulations if corresponding datasets are missing in table CollectionEventRegulation
Table CollectionTask
A task for a collection. Details are defined in table Task
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionTaskID | int | PK of the table | NO | - |
| CollectionTaskParentID | int | Relation to PK for hierarchy within the data | YES | Refers to table CollectionTask |
| CollectionID | int | Relation to table Collection. Every ColletionTask needs a relation to a collection | NO | Refers to table Collection |
| TaskID | int | Relation to table Task where details for the collection task are defined | NO | Refers to table Task |
| DisplayOrder | int | Display order e.g. in a report. Data with value 0 will not be includedDefault value: (1) | YES | - |
| DisplayText | nvarchar (400) | The display text of the module related data as shown e.g. in a user interface | YES | - |
| CollectionSpecimenID | int | Refers to the CollectionSpecimenID of CollectionSpecimenPart (= foreign key and part of primary key) | YES | Refers to table CollectionSpecimenPart |
| SpecimenPartID | int | Unique ID of the part of the collection specimen the task is related to. | YES | Refers to table CollectionSpecimenPart |
| TransactionID | int | ID of a transaction. Related to PK of table Transaction | YES | Refers to table Transaction |
| ModuleUri | varchar (500) | The URL of module related data | YES | - |
| TaskStart | datetime | The start date and or time of the collection tasks. The type is defined in table Task | YES | - |
| TaskEnd | datetime | The end date and or time of the collection tasks. The type is defined in table Task | YES | - |
| Result | nvarchar (400) | A text result either taken from a list or entered. The type is defined in table Task | YES | - |
| URI | varchar (500) | The URI of the collection tasks. The type is defined in table Task | YES | - |
| NumberValue | real | The numeric value of the collection tasks. The type is defined in table Task | YES | - |
| BoolValue | bit | The boolean of the collection tasks. The type is defined in table Task | YES | - |
| MetricDescription | nvarchar (500) | Description of the metric e.g. imported from a time series database like Prometheus | YES | - |
| MetricSource | varchar (4000) | The source of the metric e.g. the PromQL statement for the import from a timeseries database Prometheus | YES | - |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature | YES | - |
| ResponsibleAgent | nvarchar (500) | The name of the responsible person or institution | YES | - |
| ResponsibleAgentURI | varchar (500) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. | YES | - |
| Description | nvarchar (MAX) | The description of the collection tasks. The type is defined in table Task | YES | - |
| Notes | nvarchar (MAX) | The notes of the collection tasks. The type is defined in table Task | YES | - |
| LogInsertedBy | nvarchar (50) | Name of the creator of this data setDefault value: dbo.UserID() | YES | - |
| LogInsertedWhen | smalldatetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: dbo.UserID() | YES | - |
| LogUpdatedWhen | smalldatetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Collection
- CollectionSpecimenPart
- Task
- Transaction
trgInsCollectionTask
Inserting CollectionID of parent entry if missing
Table CollectionTaskImage
The images showing the CollectionTask
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionTaskID | int | Refers to the ID of CollectionTask (= foreign key and part of primary key) | NO | Refers to table CollectionTask |
| 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 CollectionTask image | YES | - |
| Description | xml (MAX) | Description of the image | YES | - |
| Title | nvarchar (500) | Title of the resource | YES | - |
| ObjectGeometry | geometry (MAX) | The geometry of an object placed within the image | 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 | - |
| DisplayOrder | smallint | The display order of the image | YES | - |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES | - |
| LogInsertedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogInsertedBy | nvarchar (50) | Name of the creator of this data setDefault value: dbo.UserID() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: dbo.UserID() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionTask
Table CollectionTaskMetric
The metric related to a collection task
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionTaskID | int | Refers to the ID of CollectionTask (= foreign key and part of primary key) | NO | Refers to table CollectionTask |
| MetricDate | datetime | Date and time of the metric, part of PK | NO | - |
| Aggregation | nvarchar (50) | The Aggregation applied for retrieval of the value, e.g. max, avg etc.Default value: N’none’ | NO | Refers to table CollTaskMetricAggregation_Enum |
| MetricValue | real | The value of the metric | YES | - |
| LogInsertedBy | nvarchar (50) | Name of the creator of this data setDefault value: dbo.UserID() | YES | - |
| LogInsertedWhen | smalldatetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: dbo.UserID() | YES | - |
| LogUpdatedWhen | smalldatetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionTask
- CollTaskMetricAggregation_Enum
Table Entity
The entities in an application e.g. the tables and columns in a database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Entity | varchar (500) | The name of the entity, e.g. Table.Column.Content within the database or a unique string for e.g. a message within the DiversityWorkbench e.g. “DiversityWorkbench.Message.Connection.NoAccess”, PK | NO | - |
| DisplayGroup | nvarchar (50) | If DiversityWorkbench entities should be displayed in a group, the name of the group | YES | - |
| Notes | nvarchar (MAX) | Notes on the entity | YES | - |
| Obsolete | bit | True if an entity is obsolete. Obsolete entities may be kept to ensure compatibility with older modules | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table EntityRepresentation
The description of the entity in a certain context in different languages
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Entity | varchar (500) | The name of the entity. Foreign key, relates to table Entity | NO | Refers to table Entity |
| LanguageCode | nvarchar (50) | ISO 639: 2-letter codes for the language of the content | NO | Refers to table EntityLanguageCode_Enum |
| EntityContext | nvarchar (50) | The context for the representation, e.g. “Exchange with ABCD”, “collection management” or “observation” as defined in table EntityContext_Enum | NO | Refers to table EntityContext_Enum |
| DisplayText | nvarchar (50) | The text for the entity as shown e.g. in a user interface | YES | - |
| Abbreviation | nvarchar (20) | The abbreviation for the entity as shown e.g. in a user interface | YES | - |
| Description | nvarchar (MAX) | The description of the entity | YES | - |
| Notes | nvarchar (MAX) | Notes on the representation of the entity | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Entity
- EntityContext_Enum
- EntityLanguageCode_Enum
Table EntityUsage
The usage of an entity in a certain context, e.g. hidden, readonly
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Entity | varchar (500) | The name of the entity. Foreign key, relates to table Entity | NO | Refers to table Entity |
| EntityContext | nvarchar (50) | The context for the representation, e.g. “Exchange with ABCD”, “collection management” or “observation” as defined in table EntityContext_Enum | NO | Refers to table EntityContext_Enum |
| Accessibility | nvarchar (50) | If the access of entity is resticted to e.g. read only or it can be edited without restrictions | YES | Refers to table EntityAccessibility_Enum |
| Determination | nvarchar (50) | If a value is determined e.g. by the system or the user | YES | Refers to table EntityDetermination_Enum |
| Visibility | nvarchar (50) | If the entity is visible or hidden from e.g. a user interface | YES | Refers to table EntityVisibility_Enum |
| PresetValue | nvarchar (500) | If a value is preset, the value or SQL statement for the value, e.g. ‘determination’ for identifications when using a mobile device during an expedition | YES | - |
| Notes | nvarchar (MAX) | Notes on the usage of the entity | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Entity
- EntityAccessibility_Enum
- EntityContext_Enum
- EntityDetermination_Enum
- EntityVisibility_Enum
Table ExternalIdentifier
An external identier related to a dataset, e.g. a DOI
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ID | int | ID of the identifier (Primary key) | NO | - |
| ReferencedTable | nvarchar (128) | The name of the table the external identifier refers to | NO | - |
| ReferencedID | int | The ID of the data set in the table the external identifier refers to | NO | - |
| Type | nvarchar (50) | The type of the identifier as defined in table ExternalIdentifierType | YES | Refers to table ExternalIdentifierType |
| Identifier | nvarchar (500) | The identifier | YES | - |
| URL | varchar (500) | A URL with further informations about the identifier | YES | - |
| Notes | nvarchar (MAX) | Notes about the identifier | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- ExternalIdentifierType
Table ExternalIdentifierType
The type of an external identier, e.g. DOI
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Type | nvarchar (50) | The type of external identifiers (primary key) | NO | - |
| ParentType | nvarchar (50) | The superior type of this type | YES | - |
| URL | varchar (500) | A URL providing further informations about this type | YES | - |
| Description | nvarchar (MAX) | The description of this type | YES | - |
| InternalNotes | nvarchar (MAX) | Internal notes about the type | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newid() | NO | - |
Table Identification
The identifications of the organisms within a specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table Identification and table IdentificationUnit |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO | Refers to table Identification and table IdentificationUnit |
| IdentificationSequence | smallint | The sequence of the identifications. The last identification (having the highest sequence) is regarded as validDefault value: (1) | NO | - |
| DependsOnIdentificationSequence | smallint | if the identification depends on another identification, e.g. for rocks where several terms from a terminology should be included | YES | Refers to table Identification |
| 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 | Refers to table CollIdentificationDateCategory_Enum |
| VernacularTerm | nvarchar (255) | Name or term other than a taxonomic (= scientific) name, e.g. ‘pine’, ’limestone’, ‘conifer’, ‘hardwood’ | YES | - |
| TermURI | varchar (500) | The URI of the term, e.g. as provided by the module DiversityScientificTerms. | 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 | Refers to table CollIdentificationCategory_Enum |
| IdentificationQualifier | nvarchar (50) | Qualification of the identification e.g. “cf.”," aff.", “sp. nov.” (= foreign key, see table CollIdentificationQualifier_Enum) | YES | Refers to table CollIdentificationQualifier_Enum |
| TypeStatus | nvarchar (50) | If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum) | YES | Refers to table CollTypeStatus_Enum |
| 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 createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollIdentificationCategory_Enum
- CollIdentificationDateCategory_Enum
- CollIdentificationQualifier_Enum
- CollTypeStatus_Enum
- IdentificationUnit
trgIdentificationInsert
Updating the LastIdentificationCache in IdentificationUnit
trgInsIdentification
Updating empty date columns depending on a given date
Table IdentificationUnit
Organism which is present in or on a collectied specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimen and table IdentificationUnit |
| 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 | - |
| HierarchyCache | nvarchar (500) | A cached value fo the superior taxonomy of the last identification as derived from a taxonomic data provider | YES | - |
| TaxonomicGroup | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) | NO | Refers to table CollTaxonomicGroup_Enum |
| 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.Default value: (0) | YES | - |
| RelatedUnitID | int | The IdentificationUnitID of the organism or substrate on which this organism is growing (= foreign key) | YES | Refers to table IdentificationUnit |
| RelationType | nvarchar (50) | The relation of a unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key) | YES | Refers to table CollUnitRelationType_Enum |
| ParentUnitID | int | The IdentificationUnitID of a parent organism of which this organism is a child of (= foreign key). | YES | Refers to table IdentificationUnit |
| 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 | - |
| NumberOfUnitsModifier | nvarchar (100) | A modifier for the number of units of this organism, e.g. ca. 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 | Refers to table CollCircumstances_Enum |
| RetrievalType | nvarchar (50) | The way the data about the unit were retrieved, e.g. observation, literature | YES | Refers to table CollRetrievalType_Enum |
| 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.Default value: (1) | NO | - |
| DataWithholdingReason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null | YES | - |
| Notes | nvarchar (MAX) | Further information on the organism or interaction, e.g. infection symptoms like ‘producing galls’ | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollCircumstances_Enum
- CollectionSpecimen
- CollRetrievalType_Enum
- CollTaxonomicGroup_Enum
- CollUnitRelationType_Enum
trgInsIdentificationUnit
setting the display oder for the new unit to the next number if none or an already present on was given
Table IdentificationUnitAnalysis
The analysis values taken from an organism resp. object
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table IdentificationUnit and table IdentificationUnitInPart |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO | Refers to table IdentificationUnit and table IdentificationUnitInPart |
| AnalysisID | int | Analysis ID, foreign key of table Analysis. | NO | Refers to table Analysis |
| AnalysisNumber | nvarchar (50) | Number of the analysis | NO | - |
| AnalysisResult | nvarchar (MAX) | The result of the analysis | YES | - |
| ExternalAnalysisURI | varchar (255) | An URI for an analysis as defined in an external datasoure | 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 | - |
| AnalysisDate | nvarchar (50) | The date of the analysis | YES | - |
| SpecimenPartID | int | ID of the part of a specimen (optional, foreign key) if the analysis was done with a part of the specimen (see table CollectionSpecimenPart). | YES | Refers to table IdentificationUnitInPart |
| Notes | nvarchar (MAX) | Notes on this analysis | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
| ToolUsage | xml (MAX) | The tools used for the analysis and their usage or settings. | YES | - |
Depending on:
- Analysis
- IdentificationUnit
- IdentificationUnitInPart
trgInsIdentificationUnitAnalysis
Setting the version of the dataset
Table IdentificationUnitAnalysisMethod
The methods used for an analysis
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= Foreign key and part of primary key) | NO | Refers to table IdentificationUnitAnalysis |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO | Refers to table IdentificationUnitAnalysis |
| MethodID | int | ID of the method, part of primary key | NO | Refers to table MethodForAnalysis |
| MethodMarker | nvarchar (50) | A marker for the method, part of primary keyDefault value: ‘1’ | NO | - |
| AnalysisID | int | ID of the processing. Refers to AnalysisID in table Processing (foreign key)Default value: (1) | NO | Refers to table IdentificationUnitAnalysis and table MethodForAnalysis |
| AnalysisNumber | nvarchar (50) | Number of the analysis | NO | Refers to table IdentificationUnitAnalysis |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- IdentificationUnitAnalysis
- MethodForAnalysis
Table IdentificationUnitAnalysisMethodParameter
The parameter values of a method used for an analysis
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionSpecimen (= Foreign key and part of primary key) | NO | Refers to table IdentificationUnitAnalysisMethod |
| IdentificationUnitID | int | ID of the identification unit (= Foreign key and part of primary key) | NO | Refers to table IdentificationUnitAnalysisMethod |
| AnalysisID | int | ID of the analysis. Refers to AnalysisID in table Analysis (= Foreign key and part of primary key)Default value: (1) | NO | Refers to table IdentificationUnitAnalysisMethod |
| AnalysisNumber | nvarchar (50) | Number of the analysis (= Foreign key and part of primary key) | NO | Refers to table IdentificationUnitAnalysisMethod |
| MethodID | int | ID of the method (= Foreign key and part of primary key) | NO | Refers to table IdentificationUnitAnalysisMethod and table Parameter |
| MethodMarker | nvarchar (50) | A marker for the method, part of primary keyDefault value: ‘1’ | NO | Refers to table IdentificationUnitAnalysisMethod |
| ParameterID | int | ID of the parameter tool. Referes to table Parameter (= Foreign key and part of primary key) | NO | Refers to table Parameter |
| Value | nvarchar (MAX) | The value of the parameter if different of the default value as documented in the table Parameter | YES | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- IdentificationUnitAnalysisMethod
- Parameter
Table IdentificationUnitGeoAnalysis
The geographical position or region of an organism at a certain time
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table IdentificationUnit |
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) | NO | Refers to table IdentificationUnit |
| AnalysisDate | datetime | The date of the analysis | NO | - |
| Geography | geography | The geography where the organism resp. object was located according to WGS84, e.g. a point (latitide, longitude and altitude) | YES | - |
| Geometry | geometry (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 | - |
| 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 | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- IdentificationUnit
Table IdentificationUnitInPart
The list of the organisms which are found in a part of the specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) | NO | Refers to table CollectionSpecimenPart and table IdentificationUnit |
| IdentificationUnitID | int | ID of the identification unit in table IdentificationUnit (= part of primary key). Usually one of possibly several organisms present on the collection specimen. Example: parasite with hyperparasite on plant leaf = 3 units, | NO | Refers to table IdentificationUnit |
| SpecimenPartID | int | ID of the part of a specimen (optional, foreign key), if the identification unit is located on a part of the specimen (see table CollectionSpecimenPart). | NO | Refers to table CollectionSpecimenPart |
| DisplayOrder | smallint | The sequence in which the units within this part 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.Default value: (1) | NO | - |
| LogInsertedBy | nvarchar (50) | Name of the user to first enter (typ or import) the data.Default value: suser_sname() | YES | - |
| LogInsertedWhen | smalldatetime | Point in time when the data was first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data last.Default value: suser_sname() | YES | - |
| LogUpdatedWhen | smalldatetime | Point in time when this data was updated last.Default value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollectionSpecimenPart
- IdentificationUnit
trgInsIdentificationUnitInPart
Setting the version of the dataset
Table LocalisationSystem
The geographic localisation systems, e.g. coordinates
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LocalisationSystemID | int | Unique ID for the localisation system (= Primary key) | NO | - |
| LocalisationSystemParentID | int | LocalisationSystemID of the superior LocalisationSystem | YES | Refers to table LocalisationSystem |
| LocalisationSystemName | nvarchar (100) | Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS | NO | - |
| DefaultAccuracyOfLocalisation | nvarchar (50) | The default for the accuracy of values which can be reached with this method | YES | - |
| DefaultMeasurementUnit | nvarchar (50) | The default measurement unit for the localisation system, e.g. m, geograpic coordinates | YES | - |
| ParsingMethodName | nvarchar (50) | Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionLocalisation | YES | - |
| DisplayText | nvarchar (50) | Short abbreviated description of the localisation system as displayed in the user interface | YES | - |
| DisplayEnable | bit | Specifies, if this item is enabled to be used within the database. Localisation systems can be disabled to avoid seeing them, but keep the definition for the future. | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| Description | nvarchar (255) | Description of the localisation method | YES | - |
| DisplayTextLocation1 | nvarchar (50) | Short abbreviated description of the attribute Location1 in the table CollectionGeography as displayed in the user interface | YES | - |
| DescriptionLocation1 | nvarchar (255) | Description of the attribute Location1 in the table CollectionGeography as displayed in the user interface | YES | - |
| DisplayTextLocation2 | nvarchar (50) | Short abbreviated description of the attribute Location2 in the table CollectionGeography as displayed in the user interface | YES | - |
| DescriptionLocation2 | nvarchar (255) | Description of the attribute Location2 in the table CollectionGeography as displayed in the user interface | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table Method
Methods used within the database
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| MethodID | int | ID of the Method (Primary key) | NO | - |
| MethodParentID | int | MethodID of the parent Method, if it belongs to a certain type documented in this table | YES | Refers to table Method |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entriesDefault value: (0) | YES | - |
| DisplayText | nvarchar (50) | Name of the Method as e.g. shown in user interface | YES | - |
| Description | nvarchar (MAX) | Description of the Method | YES | - |
| MethodURI | varchar (255) | URI referring to an external documentation of the Method | YES | - |
| ForCollectionEvent | bit | If a method may be used during a collection event | YES | - |
| Notes | nvarchar (MAX) | Notes on this method | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table MethodForAnalysis
Methods available for a Analysis
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| AnalysisID | int | ID of the table Analysis (foreign key and part of primary key) | NO | Refers to table Analysis |
| MethodID | int | ID of the table Method (foreign key and part of primary key) | NO | Refers to table Method |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Analysis
- Method
Table MethodForProcessing
Methods available for a processing
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProcessingID | int | ID of the table Processing (foreign key and part of primary key) | NO | Refers to table Processing |
| MethodID | int | ID of the table Method (foreign key and part of primary key) | NO | Refers to table Method |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Method
- Processing
Table Parameter
The variable parameters within a method
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| MethodID | int | ID of the Method (foreign key and part of primary key) | NO | Refers to table Method |
| ParameterID | int | ID of the Parameter (part of primary key) | NO | - |
| DisplayText | nvarchar (50) | Name of the parameter as e.g. shown in user interface | YES | - |
| Description | nvarchar (MAX) | Description of the parameter | YES | - |
| ParameterURI | varchar (255) | URI referring to an external documentation of the Parameter | YES | - |
| DefaultValue | nvarchar (MAX) | The default value of the parameter | YES | - |
| Notes | nvarchar (MAX) | Notes on this parameter | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Method
Table Processing
The processings of the specimen
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProcessingID | int | ID of the processing (primary key) | NO | - |
| ProcessingParentID | int | The ID of the superior type of the processing | YES | Refers to table Processing |
| DisplayText | nvarchar (50) | The display text of the processing as shown e.g. in a user interface | YES | - |
| Description | nvarchar (MAX) | Description of the processing | YES | - |
| Notes | nvarchar (MAX) | Notes on the processing | YES | - |
| ProcessingURI | varchar (255) | A URI for a processing as defined in an external data source | YES | - |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entriesDefault value: (0) | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table ProcessingMaterialCategory
The processings which are possible for a certain material category
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProcessingID | int | ID of the processing. Refers to ProcessingID in table Processing (foreign key)Default value: (1) | NO | Refers to table Processing |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc.Default value: N’specimen’ | NO | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Processing
Table ProjectAnalysis
The types of the analysis which are available for a project
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) | NO | Refers to table Analysis |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO | Refers to table ProjectProxy |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Analysis
- ProjectProxy
Table ProjectMaterialCategory
The material categorys which are possible for a certain Project
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProjectID | int | ID of the Project. Refers to ProjectID in table ProjectProxy (foreign key)Default value: (1) | NO | Refers to table ProjectProxy |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc.Default value: N’specimen’ | NO | Refers to table CollMaterialCategory_Enum |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollMaterialCategory_Enum
- ProjectProxy
Table ProjectProcessing
The types of processing available within a project
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProcessingID | int | ID of the table Processing (foreign key and part of primary key) | NO | Refers to table Processing |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO | Refers to table ProjectProxy |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Processing
- ProjectProxy
Table ProjectTaxonomicGroup
The taxonomic group which are possible for a certain Project
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProjectID | int | ID of the Project. Refers to ProjectID in table ProjectProxy (foreign key)Default value: (1) | NO | Refers to table ProjectProxy |
| TaxonomicGroup | nvarchar (50) | Taxonomic group of specimen. Examples: ‘plant’, ‘animal’, etc.Default value: N’plant’ | NO | Refers to table CollTaxonomicGroup_Enum |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- CollTaxonomicGroup_Enum
- ProjectProxy
Table ProjectUser
The projects which a user can access
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LoginName | nvarchar (50) | A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc.. | NO | Refers to table UserProxy |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO | Refers to table ProjectProxy |
| ReadOnly | bit | If the user has only read access to data of this projectDefault value: (0) | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- ProjectProxy
- UserProxy
trgInsProjectUser
Setting ReadOnly in dependence of locked projects
Table Property
The list of the properties that can be specified for the collection site
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PropertyID | int | Unique ID for the property (= primary key) | NO | - |
| PropertyParentID | int | PropertyID of the superior property | YES | Refers to table Property |
| PropertyName | nvarchar (100) | Name of the system used for the description of the collection site, e.g. Chronostratigraphy | NO | - |
| DefaultAccuracyOfProperty | nvarchar (50) | The default for the accuracy of values which can be reached with this method | YES | - |
| DefaultMeasurementUnit | nvarchar (50) | The default measurement unit for the characterisation system, e.g. pH | YES | - |
| ParsingMethodName | nvarchar (50) | Internal value, specifying a programming method used for parsing text in table CollectionEventProperty | NO | - |
| DisplayText | nvarchar (50) | Short abbreviated description of the site property as displayed in the user interface | YES | - |
| DisplayEnabled | bit | Specifies, if this item is enabled to be used within the database. Properties can be disabled to avoid seeing them, but keep the definition for the future.Default value: (1) | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| Description | nvarchar (MAX) | Description of the property | YES | - |
| PropertyURI | varchar (1000) | The URI of the property, e.g. as provided by the module DiversityScientificTerms. | YES | - |
| PropertyType | nvarchar (50) | Type of the collection site property, e.g. Chronostratigraphy | YES | Refers to table PropertyType_Enum |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- PropertyType_Enum
Table ReplicationPublisher
Databases providing data via replication
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| DatabaseName | varchar (255) | The name of the publishing database | NO | - |
| Server | varchar (255) | The name or address of the server where the publishing database is located | NO | - |
| Port | smallint | The port used by the server | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
Table Task
The Tasks of the collection
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TaskID | int | ID of the Task (primary key) | NO | - |
| TaskParentID | int | The ID of the superior type of the Task | YES | Refers to table Task |
| DisplayText | nvarchar (50) | The display text of the Task as shown e.g. in a user interface | YES | - |
| Description | nvarchar (MAX) | Description of the Task | YES | - |
| Notes | nvarchar (MAX) | Notes on the Task | YES | - |
| TaskURI | varchar (500) | A URI for a Task as defined in an external data source | YES | - |
| Type | nvarchar (50) | The type of the task as defined in table TaskType_EnumDefault value: ‘Task’ | YES | Refers to table TaskType_Enum |
| ModuleTitle | nvarchar (50) | The title for module related data for collection tasks. Not available in depending collection task if empty | YES | - |
| ModuleType | nvarchar (50) | The DiversityWorkbench module to which a task is related. Not available in depending collection task if empty | YES | Refers to table TaskModuleType_Enum |
| SpecimenPartType | nvarchar (50) | The description of the collection specimen part to which a task is related. Not available in depending collection task if empty | YES | - |
| TransactionType | nvarchar (50) | The description of the transaction to which a task is related. Not available in depending collection task if empty | YES | - |
| ResultType | nvarchar (50) | The display text for the results as shown in a user interface. Not available in depending collection task if empty | YES | - |
| DateType | nvarchar (50) | The date and time details defined for a task. Not available in depending collection task if empty | YES | Refers to table TaskDateType_Enum |
| DateBeginType | nvarchar (50) | The definition of the begin for date and time details defined for a task. Not available in depending collection task if empty | YES | - |
| DateEndType | nvarchar (50) | The definition of the end for date and time details defined for a task. Not available in depending collection task if empty | YES | - |
| NumberType | nvarchar (50) | The definition for the numeric value as shown in a user interface. Not available in depending collection task if empty | YES | - |
| BoolType | nvarchar (50) | The definition for the boolean value as shown in a user interface. Not available in depending collection task if empty | YES | - |
| MetricType | nvarchar (50) | The definition for the metric as shown in a user interface. Not available in depending collection task if empty | YES | - |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature | YES | - |
| DescriptionType | nvarchar (50) | The definition for the description as shown in a user interface. Not available in depending collection task if empty | YES | - |
| NotesType | nvarchar (50) | The definition for the notes as shown in a user interface. Not available in depending collection task if empty | YES | - |
| UriType | nvarchar (50) | The definition for the URI as shown in a user interface. Not available in depending collection task if empty | YES | - |
| ResponsibleType | nvarchar (50) | The definition for the responsible agent as shown in a user interface. Not available in depending collection task if empty | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: dbo.UserID() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: dbo.UserID() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- TaskDateType_Enum
- TaskModuleType_Enum
- TaskType_Enum
Table TaskModule
Data from DiversityWorkbench modules used used within a task
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TaskID | int | ID of the task, part of PK, relates to PK of table Task | NO | Refers to table Task |
| DisplayText | nvarchar (400) | Display text as provided by the module, part of PK | NO | - |
| URI | varchar (500) | URI linking the dataset of the module | YES | - |
| Description | nvarchar (MAX) | Optional description of the linked data, e.g. the common name for taxa | YES | - |
| Notes | nvarchar (MAX) | Notes related to the dataset | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | ID of the creator of this data setDefault value: dbo.UserID() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: dbo.UserID() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Task
Table TaskResult
List or results provided for the CollectionTask of the corresponding type
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TaskID | int | ID of the task, part of PK, relates to PK of table Task | NO | Refers to table Task |
| Result | nvarchar (400) | The result, part of PK | NO | - |
| URI | varchar (500) | A URI of the result providing further information | YES | - |
| Description | nvarchar (MAX) | The description of the entry | YES | - |
| Notes | nvarchar (MAX) | Notes about the entry | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | ID of the creator of this data setDefault value: dbo.UserID() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: dbo.UserID() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Task
Table Transaction
Transactions like loan, borrow, gift, exchange etc. of specimen, if they are e.g. permanently or temporary transfered from one collection to another
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| 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 | Refers to table Transaction |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or outDefault value: N’exchange’ | NO | Refers to table CollTransactionType_Enum |
| 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 | Refers to table Collection |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transactionDefault value: '’ | YES | - |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc.Default value: N’specimen’ | 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 | - |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift | YES | Refers to table Collection |
| 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 | Refers to table Collection |
| 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 | 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 | - |
| 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 | - |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Collection
- CollTransactionType_Enum
Table TransactionAgent
Agents involved in the transaction
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TransactionID | int | Unique ID for the transaction, refers to table Transaction (= part of primary key and foreign key) | NO | Refers to table Transaction |
| TransactionAgentID | int | Unique ID for the Agent within the transaction (= part of primary key) | NO | - |
| AgentName | nvarchar (500) | Name of the person or institution | YES | - |
| AgentURI | varchar (500) | Link to the source for further informations about the agent, e.g in the module DiversityAgents | YES | - |
| AgentRole | nvarchar (500) | Role of the agent within the transaction | YES | - |
| Notes | nvarchar (MAX) | Notes about the agent | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Transaction
Table TransactionComment
The standard text phrases for transactions
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Comment | nvarchar (400) | Text as transferred into the comment of a transaction | NO | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table TransactionDocument
The history of transactions or the documents connected to the transactions
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TransactionID | int | Unique ID for the transaction, refers to table Transaction (= part of primary key and foreign key) | NO | Refers to table Transaction |
| Date | datetime | The date of the event of a transaction | NO | - |
| TransactionText | nvarchar (MAX) | The text of a transaction document | YES | - |
| TransactionDocument | image | A scanned document connected to this transaction | YES | - |
| DisplayText | nvarchar (255) | A display text as shown e.g. in a user interface to characterize the document | YES | - |
| DocumentURI | varchar (1000) | A link to a web resource of the document the document | YES | - |
| DocumentType | nvarchar (255) | The type of the document | YES | - |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Transaction
Table TransactionPayment
The payments within a transaction
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| TransactionID | int | Unique ID for the transaction, refers to table Transaction (= part of primary key and foreign key) | NO | Refers to table Transaction |
| PaymentID | int | Unique ID for the payment (= part of primary key) | NO | - |
| Identifier | nvarchar (500) | An identifer for the payment like a booking number or invoice number | YES | - |
| Amount | float | Amount of the payment in the default currency as defined in TransactionCurrency | YES | - |
| ForeignAmount | float | If the payment was not in the default curreny as defined in TransactionCurrency, the amount of the payment in foreign curreny | YES | - |
| ForeignCurrency | nvarchar (50) | If the payment was not in the default curreny as defined in TransactionCurrency, the foreign currency of the payment | YES | - |
| PayerName | nvarchar (500) | Name of the person or institution paying the amount | YES | - |
| PayerAgentURI | varchar (500) | Link to the source for further infomations about the payer, e.g in the module DiversityAgents | YES | - |
| RecipientName | nvarchar (500) | Agent receiving the payment | YES | - |
| RecipientAgentURI | varchar (500) | Link to the source for further infomations about the recipient of the payment, e.g in the module DiversityAgents | YES | - |
| PaymentDate | datetime | Date of the payment | YES | - |
| PaymentDateSupplement | nvarchar (50) | Supplement to the date of the payment, e.g. if the original date is not a real date like ‘summer 1920’ or ‘1910 - 1912’ | YES | - |
| PaymentURI | varchar (500) | A link to an external administration system for the payment | YES | - |
| Notes | nvarchar (MAX) | Notes about the payment | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: suser_sname() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: suser_sname() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- Transaction