CollectionModel v2.05.52

From Diversity Workbench
Jump to: navigation, search

Contents

DiversityCollection Information Model (version 2.05.52, 13 October 2014)

Authors M. Weiss, G. Hagedorn & D. Triebel 2014
License 80x15.png
Suggested citation M. Weiss, G. Hagedorn & D. Triebel (2014). DiversityCollection information model (version 2.05.52). http://www.diversityworkbench.net/Portal/CollectionModel_v2.05.52
Notes The models currently reside in MS SQL Server, so knowledge of some SQL Server ER-diagram conventions will be helpful.

ER Diagrams

Overview over all entities and relations used in the database model

(enumeration tables are not included)

ER

The colors in the diagram indicate the logical group of the tables as described in the legend. Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview or the detail diagrams.

A short introduction:

  • “CollectionSpecimen” is the central entity, containing the information directly related to the collected specimen. The field ExsiccataID relates to the module DiversityExsiccate within the Diversity Workbench.
  • “CollectionProject” keeps the relation to the projects. Each specimen may be included in several projects.
  • “CollectionAgent”. The people or groups responsible for the collection of the specimen are stored in the table CollectionAgent. There may be several collectors for one specimen. The sequence of the collectors (e.g. for print on a label) is stored with the CollectorsSequence. The CollectorsAgentURI refers to the module DiversityAgent within the Diversity Workbench.
  • “CollectionSpecimenImage” keeps the images for a specimen. The ResourceURI refers to the module DiversityResources within the Diversity Workbench.
  • “CollectionEvent” is the central entity, containing the information for the collection event. Specimen stored in a collection are gathered during a collection event. The tables associated with collection event keep informations about the geographic locality, the habitat, the date etc. During an collection event, several specimen may have been collected.
  • “CollectionEventSeries”. An event series can contain other series and collection events. The event series should be used to organise your collection events.
  • “CollectionLocalisation”. The geographic localisation of each collection event can be documented with several localisation systems. One option are entries linked to the module DiversityGazetteer within the Diversity Workbench, providing information on geographical names.
  • “LocalisationSystem” lists the localisation systems used to document a geographical locality.
  • “CollectionEventProperty”. Properties of the collection site, e.g. the habitat found during a collectoin event can be documented with several habitats derived from standard habitat list like e.g. EUNIS.
  • “CollectionEventImage” keeps the images related to a collection event, e.g. a map or a photograph of the habitat.
  • “IdentificationUnit”. The items in one collection specimen are regarded as identification units. One specimen can contain several identification units, e.g. an insect (1) feeding on a fungus (2) growing as a parasit on a plant (3).
  • “Identification” keeps the identifications of the identification units in a collection specimen. Each identification unit may have been identified several times. For relation to the module DiversityTaxonNames the field NameURI is used.
  • “CollectionSpecimenPart”. This tables keeps the informations about the parts of a specimen and the actual location of a specimen within e.g. a herbarium. A specimen may be located in several herbaria with several duplicates.
  • “Collection” lists the collections where the specimen can be found.
  • “CollectionSpecimenRelation” documents the relations between specimen.
  • “CollectionSpecimenTransaction” documents the exchange with other herbaria.

Entities ending with “_Enum” (“CollRelationType_Enum”, “CollTypeStatus_Enum” etc.) contain enumerated values or strings; all have the same fields shown in the last ER diagram further below.


Description of the tables

Table: Analysis

Analysis types used within the database

Column Data type Description Requ. Ind.
AnalysisID int ID of the analysis (primary key) R U
AnalysisParentID int Analysis ID of the parent analysis, if it belongs to a certain type documented in this table   -     -  
DisplayText nvarchar (50) Name of the analysis as e.g. shown in user interface   -     -  
Description nvarchar (MAX) Description of the analysis   -     -  
MeasurementUnit nvarchar (50) The measurement unit used for the analysis, e.g. mm, µmol, kg   -     -  
Notes nvarchar (MAX) Notes on this analysis   -     -  
AnalysisURI varchar (255) URI referring to an external documentation of the analysis   -     -  
OnlyHierarchy bit If the entry is only used for the hierarchical arrangement of the entries
DefaultValue: (0)
  -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


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 Requ. Ind.
AnalysisID int ID of the analysis (primary key) R U
AnalysisResult nvarchar (255) The categorized value of the analysis R U
Description nvarchar (500) Description of enumerated object displayed in the user interface   -     -  
DisplayText nvarchar (50) Short abbreviated description of the object displayed in the user interface   -     -  
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.   -     -  
Notes nvarchar (500) Internal development notes on usage, definition, etc. of an enumerated object   -     -  
LogInsertedBy nvarchar (50) Name of user to first enter (typ or import) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Point in time when the data was first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data last.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Point in time when this data was updated last.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: AnalysisTaxonomicGroup

The types of analysis which are available for a taxonomic group

Column Data type Description Requ. Ind.
AnalysisID int Analysis ID, foreign key of table Analysis. R U
TaxonomicGroup nvarchar (50) Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) R U
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Annotation

Annotations to datasets in the database

Column Data type Description Requ. Ind.
AnnotationID int ID of the annotation (primary key) R U
ReferencedAnnotationID int If an annotation refers to another annotation, the ID of the referred annotation   -     -  
AnnotationType nvarchar (50) The type of the annotation as defined in AnnotationType_Enum, e.g. Reference
DefaultValue: N'Annotation'
R   -  
Title nvarchar (50) Title of the annotation   -     -  
Annotation nvarchar (MAX) The annotation entered by the user R   -  
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   -     -  
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   -     -  
ReferenceURI varchar (255) If the entry is linked to an external module like DiversityReferences, the link to the referenced data set   -     -  
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   -     -  
SourceURI varchar (255) If the entry is linked to an external module like DiversityAgents, the link to the referenced data set   -     -  
IsInternal bit If an annotation is restricted to authorized users of the database   -     -  
ReferencedID int The ID of the data set in the table the annotation refers to R   -  
ReferencedTable nvarchar (500) The name of the table the annotation refers to R   -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Collection

The collections where the specimen are stored

Column Data type Description Requ. Ind.
CollectionID int Unique reference ID for the collection (= primary key) R U
CollectionParentID int For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent 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 R   -  
CollectionAcronym nvarchar (10) A unique code for the collection, e.g. the herbarium code from Index Herbariorum   -     -  
AdministrativeContactName nvarchar (500) The name of the person or organisation responsible for this collection   -     -  
AdministrativeContactAgentURI varchar (255) The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents   -     -  
Description nvarchar (MAX) A short description of the collection   -     -  
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   -     -  
CollectionOwner nvarchar (255) The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null   -     -  
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionAgent

The collector(s) of CollectionSpecimens

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to ID of CollectionEvent (= foreign key and part of primary key) R U
CollectorsName nvarchar (255) Name of the Collector R U
CollectorsAgentURI varchar (255) The URI of the Agent, e.g. as stored within the module DiversityAgents   -     -  
CollectorsSequence datetime2 The order of collectors in a team. Automatically set by the database system
DefaultValue: sysdatetime()
  -   U
CollectorsNumber nvarchar (50) Number assigned to a specimen or a batch of specimens by the collector during the collection event (= 'field number')   -     -  
Notes nvarchar (MAX) Notes on the collector, e.g. if the name is uncertain   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionEvent

The event where and when the specimen were collected

Column Data type Description Requ. Ind.
CollectionEventID int Unique ID for the table CollectionEvent (= primary key) R U
Version int The version of the data set. Automatically set by the system.
DefaultValue: (1)
R   -  
SeriesID int The ID of the related expedition. Relates to the PK of the table CollectionExpedition (foreign key).   -     -  
CollectorsEventNumber nvarchar (50) Number assigned to a collection event by the collector (= 'field number')   -     -  
CollectionDate datetime The date of the event calulated from the entries in CollectionDay, -Month and -Year.   -     -  
CollectionDay tinyint The day of the date of the event or when the collection event started   -     -  
CollectionMonth tinyint The month of the date of the event or when the collection event started   -     -  
CollectionYear smallint The year of the date of the event or when the collection event started   -     -  
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.   -     -  
CollectionDateCategory nvarchar (50) Category of the date of the identification e.g. "system", "estimated" (= foreign key, see in table CollEventDateCategory_Enum)   -     -  
CollectionTime varchar (50) The time of the event or when the collection event started   -     -  
CollectionTimeSpan varchar (50) The time span e.g. in seconds of the collection event   -     -  
LocalityDescription nvarchar (MAX) Locality description of the locality exactly as written on the original label (i.e. without corrections during data entry)   -     -  
HabitatDescription nvarchar (MAX) Geo-ecological description of the locality exactly as written on the original label (i.e. without corrections during data entry)   -     -  
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   -     -  
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   -     -  
ReferenceDetails nvarchar (50) The exact location within the reference, e.g. pages, plates   -     -  
CollectingMethod nvarchar (MAX) Description of the method used for collecting the samples, e.g. traps, moist chambers, drag net   -     -  
Notes nvarchar (MAX) Notes on the collection event   -     -  
CountryCache nvarchar (50) The country where the collection event took place. Cached value derived from an entry in CollectionEventLocalisation   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionEventImage

The images showing the collection site resp. place of the observations

Column Data type Description Requ. Ind.
CollectionEventID int Part of primay key, refers to unique ID for the table CollectionEvent (= foreign key) R U
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 R U
ResourceURI varchar (255) The URI of the resource (e.g. see module DiversityResources)   -     -  
ImageType nvarchar (50) Type of the image, e.g. map   -     -  
Notes nvarchar (MAX) Notes to this image concerning the CollectionEvent   -     -  
Description xml (MAX) Description of the image   -     -  
Title nvarchar (500) Title of the resource   -     -  
IPR nvarchar (500) Intellectual Property Rights; the rights given to persons for their intellectual property   -     -  
CreatorAgent nvarchar (500) Person or organization originally creating the resource   -     -  
CreatorAgentURI varchar (255) Link to the module DiversityAgents   -     -  
CopyrightStatement nvarchar (500) Notice on rights held in and for the resource   -     -  
LicenseType nvarchar (500) Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses   -     -  
InternalNotes nvarchar (500) Internal notes which should not be published e.g. on websites   -     -  
LicenseHolder nvarchar (500) The person or institution holding the license   -     -  
LicenseHolderAgentURI nvarchar (500) The link to a module containing futher information on the person or institution holding the license   -     -  
LicenseYear nvarchar (50) The year of license declaration   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionEventLocalisation

The geographic localisation of a CollectionEvent

Column Data type Description Requ. Ind.
CollectionEventID int Refers to the ID of CollectionEvent (= foreign key and part of primary key) R U
LocalisationSystemID int Refers to the ID of LocalisationSystem (= foreign key and part of primary key) R U
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)   -     -  
Location2 nvarchar (255) Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus   -     -  
LocationAccuracy nvarchar (50) The accuracy of the determination of this locality   -     -  
LocationNotes nvarchar (MAX) Notes on the location   -     -  
DeterminationDate smalldatetime Date of the determination of the geographical localisation   -     -  
DistanceToLocation varchar (50) Distance from the specified place to the real location of the collection site (m)   -     -  
DirectionToLocation varchar (50) Direction from the specified place to the real location of the collection site (Degrees rel. to north)   -     -  
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.   -     -  
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)   -     -  
Geography geography The geography of the localisation   -     -  
RecordingMethod nvarchar (500) The method or device used for the recording of the localisation   -     -  
AverageAltitudeCache float Calculated altitude as parsed from the location fields   -     -  
AverageLatitudeCache float Calculated latitude as parsed from the location fields   -     -  
AverageLongitudeCache float Calculated longitude as parsed from the location fields   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionEventMethod

The methods used during a collection event

Column Data type Description Requ. Ind.
CollectionEventID int Refers to ID of CollectionSpecimen (= foreign key and part of primary key) R U
MethodID int ID of the setting, part of primary key R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  


Table: CollectionEventParameterValue

The values of the parameter of the methods used within a collection event

Column Data type Description Requ. Ind.
CollectionEventID int Unique ID for the table CollectionEvent (= foreign key and part of primary key) R U
MethodID int ID of the method tool. Referes to table Method (= foreign key and part of primary key) R U
ParameterID int ID of the parameter tool. Referes to table Parameter (= foreign key and part of primary key) R U
Value nvarchar (MAX) The value of the parameter, if different of the default value as documented in the table Parameter   -     -  
LogInsertedWhen datetime Point in time when this data set was created   -     -  
LogInsertedBy nvarchar (50) Name of the creator of this data set   -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last   -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last   -     -  
RowGUID uniqueidentifier     -     -  


Table: CollectionEventProperty

A property of a collection site, e.g. exposition, slope, vegetation. May refer to Diversity Workbench module DiversityScientificTerms

Column Data type Description Requ. Ind.
CollectionEventID int Refers to the ID of CollectionEvent (= foreign key and part of primary key) R U
PropertyID int The ID of the property of the collection site, foreign key, see table Property R U
DisplayText nvarchar (255) The text for the property as shown e.g. in a user interface   -     -  
PropertyURI varchar (255) URI referring to an external data source e.g. DiversityTerminology   -     -  
PropertyHierarchyCache nvarchar (MAX) A cached text of the complete name of the descriptor including superior categories, if present   -     -  
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   -     -  
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.   -     -  
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)   -     -  
Notes nvarchar (MAX) Notes on the property of the colletion site.   -     -  
AverageValueCache float For numeric values - a cached average value according to the   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionEventSeries

The series whithin which collection events take place

Column Data type Description Requ. Ind.
SeriesID int Primary key. The ID for this series of collection events (= primary key) R U
SeriesParentID int The ID of the superior series of collection events   -     -  
Description nvarchar (MAX) The description of the series of collection events as it will be printed on e.g. the label R   -  
SeriesCode nvarchar (50) The user defined code for a series of collection events   -     -  
Notes nvarchar (MAX) Notes on this series of collection events   -     -  
Geography geography The geography of the series of collection events   -     -  
DateStart datetime Point in time when the series of collection events started   -     -  
DateEnd datetime Point in time when the series of collection events ended   -     -  
DateCache datetime The first date of the depending events, used for sorting the expeditions [controlled by the database]   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionEventSeriesImage

The images showing the sites of a series of collection events, e.g. an expedition

Column Data type Description Requ. Ind.
SeriesID int Unique ID for the table CollectionEventSeries (= foreign key and part of primary key) R U
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 R U
ResourceURI varchar (255) The URI of the resource (e.g. see module DiversityResources)   -     -  
ImageType nvarchar (50) Type of the image, e.g. map   -     -  
Notes nvarchar (MAX) Notes to this image of the collection site   -     -  
Description xml (MAX) Description of the image   -     -  
Title nvarchar (500) Title of the resource   -     -  
IPR nvarchar (500) Intellectual Property Rights; the rights given to persons for their intellectual property   -     -  
CreatorAgent nvarchar (500) Person or organization originally creating the resource   -     -  
CreatorAgentURI varchar (255) Link to the module DiversityAgents   -     -  
CopyrightStatement nvarchar (500) Notice on rights held in and for the resource   -     -  
LicenseType nvarchar (500) Type of an official or legal permission to do or own a specified thing, e.g. Creative Common licenses   -     -  
InternalNotes nvarchar (500) Internal notes which should not be published e.g. on websites   -     -  
LicenseHolder nvarchar (500) The person or institution holding the license   -     -  
LicenseHolderAgentURI nvarchar (500) The link to a module containing futher information on the person or institution holding the license   -     -  
LicenseYear nvarchar (50) The year of license declaration   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionExternalDatasource

CollectionExternalDatasource document the sources of the names.

Column Data type Description Requ. Ind.
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) R U
ExternalDatasourceName nvarchar (255) The name of the data collection which has been integrated or can be linked to for further analysis   -     -  
ExternalDatasourceVersion nvarchar (255) The version of this data collection (either official version number or dates when the collection was integrated)   -     -  
Rights nvarchar (500) A description of copyright agreements or permission to use data from the external database   -     -  
ExternalDatasourceAuthors nvarchar (200) The persons or institutions responsible for the external database   -     -  
ExternalDatasourceURI nvarchar (300) The URI of the database provider or the external database   -     -  
ExternalDatasourceInstitution nvarchar (300) The institution responsible for the external database   -     -  
InternalNotes nvarchar (1500) Additional notes on this data collection   -     -  
ExternalAttribute_NameID nvarchar (255) The table and field name in the external data collection to which CollectionExternalID refers   -     -  
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.   -     -  
Disabled bit If this source should be disabled for selection of names e.g. in picklists   -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  


Table: CollectionImage

The images showing the collection

Column Data type Description Requ. Ind.
CollectionID int Refers to the ID of Collection (= foreign key and part of primary key) R U
URI varchar (255) The complete URI address of the image. R U
ImageType nvarchar (50) Type of the image, e.g. label   -     -  
Notes nvarchar (MAX) Notes on the collection image   -     -  
Description xml (MAX) Description of the image   -     -  
Title nvarchar (500) Title of the resource   -     -  
IPR nvarchar (500) Intellectual Property Rights; the rights given to persons for their intellectual property   -     -  
CreatorAgent nvarchar (500) Person or organization originally creating the resource   -     -  
CreatorAgentURI varchar (255) Link to the module DiversityAgents   -     -  
CopyrightStatement nvarchar (500) Notice on rights held in and for the resource   -     -  
LicenseType nvarchar (500) Type of an official or legal permission to do or own a specified thing, e.g. Creative Common licenses   -     -  
InternalNotes nvarchar (500) Internal notes which should not be published e.g. on websites   -     -  
LicenseHolder nvarchar (500) The person or institution holding the license   -     -  
LicenseHolderAgentURI nvarchar (500) The link to a module containing futher information on the person or institution holding the license   -     -  
LicenseYear nvarchar (50) The year of license declaration   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
LogInsertedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogInsertedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionManager

Collection managers within DiversityCollection responsible for specimen transactions

Column Data type Description Requ. Ind.
LoginName nvarchar (50) A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc.. R U
AdministratingCollectionID int ID for the collection for which the Manager has the right to administrate the transaction. Corresponds to AdministratingCollectionID in table Transaction. R U
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionProject

The projects within which the collection specimen were placed

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionRequester

Requesters within DiversityCollection responsible for specimen transactions

Column Data type Description Requ. Ind.
LoginName nvarchar (50) A login name which the user uses to access to the DivesityWorkbench, Microsoft domains, etc.. R U
AdministratingCollectionID int ID for the collection for which the requester has the right to request specimen. Corresponds to AdministratingCollectionID in table Transaction. R U
IncludeSubcollections bit If the subcollections of the administrating collection are accessible for a request   -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionSpecimen

The data directly attributed to the collected specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Unique ID for the table CollectionSpecimen (primary key) R U
Version int The version of the data set
DefaultValue: (1)
R   -  
CollectionEventID int Refers to the ID of table CollectionEvent (= foreign key and part of primary key)   -     -  
CollectionID int ID of the collection as stored in table Collection (= foreign key, see table Collection)   -     -  
AccessionNumber nvarchar (50) Accession number of the specimen within the collection, e.g. "M-29834752"   -    
AccessionDate datetime The date of the accession calculated from the entries in AccessionDay, -Month and -Year   -     -  
AccessionDay tinyint The day of the date when the specimen was acquired in the collection   -     -  
AccessionMonth tinyint The month of the date when the specimen was acquired in the collection   -     -  
AccessionYear smallint The year of the date when the specimen was acquired in the collection   -     -  
AccessionDateSupplement nvarchar (255) Verbal or additional accession date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'   -     -  
AccessionDateCategory nvarchar (50) Category of the date of the accession e.g. "system", "estimated" (= foreign key, see in 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')   -     -  
DepositorsAgentURI varchar (255) The URI of the depositor(s) (person or organization responsible for deposition)   -     -  
DepositorsAccessionNumber nvarchar (50) Accession number of the specimen within the previous or original collection, e.g. 'D-23948'   -     -  
LabelTitle nvarchar (MAX) The title of the label e.g. for printing labels.   -     -  
LabelType nvarchar (50) Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc.   -     -  
LabelTranscriptionState nvarchar (50) The state of the transcription of a label into the database: 'Not started', 'incomplete', 'complete'   -     -  
LabelTranscriptionNotes nvarchar (255) User defined notes on the transcription of the label into the database   -     -  
ExsiccataURI varchar (255) If specimen is an exsiccata: The URI of the exsiccata series, e.g. as stored within the DiversityExsiccata module   -     -  
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)   -     -  
OriginalNotes nvarchar (MAX) Notes found on the label of the specimen by the original collector or from a later revision   -     -  
AdditionalNotes nvarchar (MAX) Additional notes made by the editor of the specimen record, e.g. 'doubtful identification/locality'   -     -  
ReferenceTitle nvarchar (255) The title of the publication where the specimen was published. Note that this is only a cached value where ReferenceURI is present   -     -  
ReferenceURI varchar (255) URI (e.g. LSID) of reference where specimen is published, e.g. referring to the module DiversityReferences   -     -  
ReferenceDetails nvarchar (50) The exact location within the reference, e.g. pages, plates   -     -  
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!   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
InternalNotes nvarchar (MAX) Internal notes which should not be published e.g. on websites   -     -  
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)   -     -  
ExternalIdentifier nvarchar (100) The identifier of the external specimen as defined in the external data source   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionSpecimenImage

The images of a collection specimen or of an organism (stored in table IdentificationUnit) within this specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
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 R U
ResourceURI varchar (255) The URI of the image, e.g. as stored in the module DiversityResources.   -     -  
SpecimenPartID int Optional: If the data set is not related to a part of a specimen, the ID of a related part (= foreign key)   -     -  
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)   -     -  
ImageType nvarchar (50) Type of the image, e.g. photograph   -     -  
Notes nvarchar (MAX) Notes on the specimen image   -     -  
Description xml (MAX) Description of the image   -     -  
Title nvarchar (500) Title of the resource   -     -  
IPR nvarchar (500) Intellectual Property Rights; the rights given to persons for their intellectual property   -     -  
CreatorAgent nvarchar (500) Person or organization originally creating the resource   -     -  
CreatorAgentURI varchar (255) Link to the module DiversityAgents   -     -  
CopyrightStatement nvarchar (500) Notice on rights held in and for the resource   -     -  
LicenseType nvarchar (500) Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses   -     -  
InternalNotes nvarchar (500) Internal notes which should not be published e.g. on websites   -     -  
LicenseHolder nvarchar (500) The person or institution holding the license   -     -  
LicenseHolderAgentURI nvarchar (500) The link to a module containing futher information on the person or institution holding the license   -     -  
LicenseYear nvarchar (50) The year of license declaration   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
DisplayOrder int The order in which the images should be shown in a interface   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionSpecimenImageProperty

The properties of images of a collection specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
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 R U
Property varchar (255) The property of the image R U
Description nvarchar (MAX) If description of the property of the image   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  
ImageArea geometry (MAX) The area in the image the property refers to   -     -  


Table: CollectionSpecimenPart

Parts of a collected specimen. Includes a possible hierarchy of the parts

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
SpecimenPartID int Unique ID of the part of the collection specimen (= part of primary key). R U
DerivedFromSpecimenPartID int SpecimenPartID of the specimen from which the current specimen is derived from   -     -  
PreparationMethod nvarchar (MAX) The method used for the preparation of the part of the specimen, e.g. the inoculation method for cultures   -     -  
PreparationDate datetime Point in time when the part was preparated e.g when it was separated from the source object   -     -  
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"   -    
PartSublabel nvarchar (50) The label for a part of a specimen, e.g. "cone", or a number attached to a duplicate of a specimen   -     -  
CollectionID int ID of the collection as stored in table Collection (= foreign key, see table Collection) R   -  
MaterialCategory nvarchar (50) Material category of specimen. Examples: 'herbarium sheets', 'drawings', 'microscopic slides' etc. (= foreign key, see table CollMaterialCategory_Enum)
DefaultValue: N'specimen'
R   -  
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.   -     -  
Stock float Number of stock units, if the specimen is stored in separated units e.g. several boxes or vessels (max. 255)   -     -  
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.   -     -  
StorageContainer nvarchar (500) The container in which the part is stored   -     -  
ResponsibleName nvarchar (255) Name of the person or institution responsible for the preparation   -     -  
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the preparation (= foreign key) as stored in the module DiversityAgents   -     -  
Notes nvarchar (MAX) Notes on the storage of the sample   -     -  
DataWithholdingReason nvarchar (255) If the specimen part is withhold, the reason for withholding the data, otherwise null.   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionSpecimenProcessing

The processing which was applied to a collected specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to ID of CollectionSpecimen (= foreign key and part of primary key) R U
ProcessingDate datetime Point in time of the start of the processing
DefaultValue: getdate()
R U
ProcessingID int ID of the processing method. Refers to ProcessingID in table Processing (foreign key)
DefaultValue: (1)
R   -  
Protocoll nvarchar (100) The label of the processing protocol   -     -  
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)   -     -  
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)   -     -  
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination   -     -  
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.   -     -  
Notes nvarchar (MAX) Notes on the processing   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  
ToolUsage xml (MAX) The tools used for the processing and their usage or settings.   -     -  


Table: CollectionSpecimenRelation

The relations of a collection specimen to other collection specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Unique reference ID for the collection specimen record (primary key) R U
RelatedSpecimenURI varchar (255) URI of the related specimen R U
RelatedSpecimenDisplayText varchar (255) The name of a related specimen as shown e.g. in a user interface R   -  
RelationType nvarchar (50) Type of the relation between the specimen (= foreign key, see table CollRelationType_Enum)   -     -  
RelatedSpecimenCollectionID int ID of the Collection as stored in table Collection (= foreign key, see table Collection)   -     -  
RelatedSpecimenDescription nvarchar (MAX) Description of the related specimen   -     -  
IdentificationUnitID int If relation refers to a certain organism within a specimen, the ID of an IdentificationUnit (= foreign key)   -     -  
SpecimenPartID int If the relation refers to a part of a specimen, the ID of a related part (= foreign key)   -     -  
Notes nvarchar (MAX) Notes on the relation to the specimen   -     -  
IsInternalRelationCache bit If the relation represents a connection between specimen in this database
DefaultValue: (1)
R   -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionSpecimenTransaction

The transactions in which a specimen was involved

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to ID of CollectionSpecimen (= foreign key and part of primary key) R U
TransactionID int Unique ID for the table Transaction (= foreign key and part of primary key) R U
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) R U
AccessionNumber nvarchar (255) Accession number which has been assigen to the part of the specimen, e.g. in connection with a former inventory.   -     -  
TransactionReturnID int Unique ID for the table Transaction (= foreign key) for the return of a part that has been on loan   -     -  
IsOnLoan bit True, if a specimen is on loan   -     -  
LogInsertedBy nvarchar (50) Name of user to first enter (typ or import) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Point in time when the data was first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Point in time when this data set was updated last.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: CollectionUser

Users of collections within DiversityCollection

Column Data type Description Requ. Ind.
LoginName nvarchar (50) A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc.. R U
CollectionID int ID for the collection for the user has access to administrate the transaction. R U
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Identification

The identifications of the organisms within a specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key) R U
IdentificationSequence smallint The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid
DefaultValue: (1)
R U
IdentificationDate datetime The date of the identification calculated from the entries in IdentificationDay, -Month and -Year   -     -  
IdentificationDay tinyint The day of the identification   -     -  
IdentificationMonth tinyint The month of the identification   -     -  
IdentificationYear smallint The year of the identification. The year may be empty if only the day or month are known.   -     -  
IdentificationDateSupplement nvarchar (255) Verbal or additional identification date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'   -     -  
IdentificationDateCategory nvarchar (50) Category of the date of the identification e.g. "system", "estimated" (= foreign key, see in table CollDateCategory_Enum)   -     -  
VernacularTerm nvarchar (255) Name or term other than a taxonomic (= scientific) name, e.g. 'pine', 'limestone', 'conifer', 'hardwood'   -     -  
TaxonomicName nvarchar (255) Valid name of the species (including the taxonomic author where available). Example: 'Rosa canina L.'   -     -  
NameURI varchar (255) The URI of the taxonomic name, e.g. as provided by the module DiversityTaxonNames.   -     -  
IdentificationCategory nvarchar (50) Category of the identification e.g. 'determination', 'confirmation', 'absence' (= foreign key, see table CollIdentificationCategory_Enum)   -     -  
IdentificationQualifier nvarchar (50) Qualification of the identification e.g. "cf."," aff.", "sp. nov." (= foreign key, see table CollIdentificationQualifier_Enum)   -     -  
TypeStatus nvarchar (50) If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum)   -     -  
TypeNotes nvarchar (MAX) Notes on the typification of this specimen   -     -  
ReferenceTitle nvarchar (255) Publications or authoritative opinions of scientist used during the identification process. Example: enter 'Schmeil-Fitschen 1995', if this field flora was used.   -     -  
ReferenceURI varchar (255) The URI of the reference e.g. as provided by the module DiversityReferences   -     -  
ReferenceDetails nvarchar (50) The exact location within the reference, e.g. pages, plates   -     -  
Notes nvarchar (MAX) User defined notes, e.g. the reason for a re-determination / change of the name, etc.   -     -  
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination   -     -  
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: IdentificationUnit

Organism which is present in or on a collectied specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
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, R U
LastIdentificationCache nvarchar (255) The last identification as entered in table Identification R  
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.   -     -  
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.   -     -  
HierarchyCache nvarchar (500)     -     -  
TaxonomicGroup nvarchar (50) Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) R   -  
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.
DefaultValue: (0)
  -     -  
RelatedUnitID int The IdentificationUnitID of the organism or substrate on which this organism is growing (= foreign key)   -     -  
RelationType nvarchar (50) The relation of a unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key)   -     -  
ParentUnitID int The IdentificationUnitID of a parent organism of which this organism is a child of (= foreign key).   -     -  
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)   -     -  
LifeStage nvarchar (255) Examples: 'II, III' for spore generations of rusts or 'seed', 'seedling' etc. for higher plants   -     -  
Gender nvarchar (50) The sex of the organism, e.g. 'female'   -     -  
NumberOfUnits smallint The number of units of this organism, e.g. 400 beetles in a bottle   -     -  
ExsiccataNumber nvarchar (50) If specimen is an exsiccata: Number of current specimen within the exsiccata series   -     -  
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.   -     -  
UnitIdentifier nvarchar (50) An identifier for the identification of the unit, e.g. a number painted on a tree within an experimental plot   -     -  
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   -     -  
Circumstances nvarchar (50) Circumstances of the occurence of the organism   -     -  
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.
DefaultValue: (1)
R   -  
Notes nvarchar (MAX) Further information on the organism or interaction, e.g. infection symptoms like 'producing galls'   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: IdentificationUnitAnalysis

The analysis values taken from an organism resp. object

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key) R U
AnalysisID int Analysis ID, foreign key of table Analysis. R U
AnalysisNumber nvarchar (50) Number of the analysis R U
AnalysisResult nvarchar (MAX) The result of the analysis   -     -  
ExternalAnalysisURI varchar (255) An URI for an analysis as defined in an external datasoure   -     -  
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination   -     -  
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.   -     -  
AnalysisDate nvarchar (50) The date of the analysis   -     -  
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).   -     -  
Notes nvarchar (MAX) Notes on this analysis   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  
ToolUsage xml (MAX) The tools used for the analysis and their usage or settings.   -     -  


Table: IdentificationUnitAnalysisMethod

The methods used for an analysis

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to ID of CollectionSpecimen (= Foreign key and part of primary key) R U
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key) R U
MethodID int ID of the method, part of primary key R U
AnalysisID int ID of the processing. Refers to AnalysisID in table Processing (foreign key)
DefaultValue: (1)
R U
AnalysisNumber nvarchar (50) Number of the analysis R U
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Who created this dataset
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime The last time when this dataset was updated
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: IdentificationUnitAnalysisMethodParameter

The parameter values of a method used for an analysis

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to ID of CollectionSpecimen (= Foreign key and part of primary key) R U
IdentificationUnitID int ID of the identification unit (= Foreign key and part of primary key) R U
AnalysisID int ID of the analysis. Refers to AnalysisID in table Analysis (= Foreign key and part of primary key)
DefaultValue: (1)
R U
AnalysisNumber nvarchar (50) Number of the analysis (= Foreign key and part of primary key) R U
MethodID int ID of the method (= Foreign key and part of primary key) R U
ParameterID int ID of the parameter tool. Referes to table Parameter (= Foreign key and part of primary key) R U
Value nvarchar (MAX) The value of the parameter if different of the default value as documented in the table Parameter   -     -  
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Who created this dataset
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime The last time when this dataset was updated
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: IdentificationUnitGeoAnalysis

The geographical position or region of an organism at a certain time

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
IdentificationUnitID int Refers to the ID of IdentficationUnit (= foreign key and part of primary key) R U
AnalysisDate datetime The date of the analysis R U
Geography geography The geography where the organism resp. object was located according to WGS84, e.g. a point (latitide, longitude and altitude)   -     -  
Geometry geometry (MAX) The geometry of the place the organism resp. object was observed, e.g. an area   -     -  
ResponsibleName nvarchar (255) Name of the person or institution responsible for the determination   -     -  
ResponsibleAgentURI varchar (255) URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents.   -     -  
Notes nvarchar (MAX) Notes on this analysis   -     -  
LogCreatedWhen datetime Point in time when this data set was created   -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set   -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last   -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last   -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: IdentificationUnitInPart

The list of the organisms which are found in a part of the specimen

Column Data type Description Requ. Ind.
CollectionSpecimenID int Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) R U
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, R U
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). R U
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.
DefaultValue: (1)
R   -  
Description nvarchar (500) A description of the unit, especially if not an entire unit but e.g. parts of it are stored in the collection, e.g. a nest of a bird   -     -  
LogInsertedBy nvarchar (50) Name of the user to first enter (typ or import) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Point in time when the data was first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data last.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Point in time when this data was updated last.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: LocalisationSystem

The geographic localisation systems, e.g. coordinates

Column Data type Description Requ. Ind.
LocalisationSystemID int Unique ID for the localisation system (= Primary key) R U
LocalisationSystemParentID int LocalisationSystemID of the superior 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 R   -  
DefaultAccuracyOfLocalisation nvarchar (50) The default for the accuracy of values which can be reached with this method   -     -  
DefaultMeasurementUnit nvarchar (50) The default measurement unit for the localisation system, e.g. m, geograpic coordinates   -     -  
ParsingMethodName nvarchar (50) Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionLocalisation   -     -  
DisplayText nvarchar (50) Short abbreviated description of the localisation system as displayed in the user interface   -     -  
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.   -     -  
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.   -     -  
Description nvarchar (255) Description of the localisation method   -     -  
DisplayTextLocation1 nvarchar (50) Short abbreviated description of the attribute Location1 in the table CollectionGeography as displayed in the user interface   -     -  
DescriptionLocation1 nvarchar (255) Description of the attribute Location1 in the table CollectionGeography as displayed in the user interface   -     -  
DisplayTextLocation2 nvarchar (50) Short abbreviated description of the attribute Location2 in the table CollectionGeography as displayed in the user interface   -     -  
DescriptionLocation2 nvarchar (255) Description of the attribute Location2 in the table CollectionGeography as displayed in the user interface   -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Method

Methods used within the database

Column Data type Description Requ. Ind.
MethodID int ID of the Method (Primary key) R U
MethodParentID int MethodID of the parent Method, if it belongs to a certain type documented in this table   -     -  
OnlyHierarchy bit If the entry is only used for the hierarchical arrangement of the entries
DefaultValue: (0)
  -     -  
DisplayText nvarchar (50) Name of the Method as e.g. shown in user interface   -     -  
Description nvarchar (MAX) Description of the Method   -     -  
MethodURI varchar (255) URI referring to an external documentation of the Method   -     -  
ForCollectionEvent bit If a method may be used during a collection event   -     -  
Notes nvarchar (MAX) Notes on this method   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: MethodForAnalysis

Methods available for a Analysis

Column Data type Description Requ. Ind.
AnalysisID int ID of the table Analysis (foreign key and part of primary key) R U
MethodID int ID of the table Method (foreign key and part of primary key) R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: MethodForProcessing

Methods available for a processing

Column Data type Description Requ. Ind.
ProcessingID int ID of the table Processing (foreign key and part of primary key) R U
MethodID int ID of the table Method (foreign key and part of primary key) R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Parameter

The variable parameters within a method

Column Data type Description Requ. Ind.
MethodID int ID of the Method (foreign key and part of primary key) R U
ParameterID int ID of the Parameter (part of primary key) R U
DisplayText nvarchar (50) Name of the parameter as e.g. shown in user interface   -     -  
Description nvarchar (MAX) Description of the parameter   -     -  
ParameterURI varchar (255) URI referring to an external documentation of the Parameter   -     -  
DefaultValue nvarchar (MAX) The default value of the parameter   -     -  
Notes nvarchar (MAX) Notes on this parameter   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Processing

The processings of the specimen

Column Data type Description Requ. Ind.
ProcessingID int ID of the processing (primary key) R U
ProcessingParentID int The ID of the superior type of the processing   -     -  
DisplayText nvarchar (50) The display text of the processing as shown e.g. in a user interface   -     -  
Description nvarchar (MAX) Description of the processing   -     -  
Notes nvarchar (MAX) Notes on the processing   -     -  
ProcessingURI varchar (255) A URI for a processing as defined in an external data source   -     -  
OnlyHierarchy bit If the entry is only used for the hierarchical arrangement of the entries
DefaultValue: (0)
  -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: ProcessingMaterialCategory

The processings which are possible for a certain material category

Column Data type Description Requ. Ind.
ProcessingID int ID of the processing. Refers to ProcessingID in table Processing (foreign key)
DefaultValue: (1)
R U
MaterialCategory nvarchar (50) Material category of specimen. Examples: 'herbarium sheets', 'drawings', 'microscopic slides' etc.
DefaultValue: N'specimen'
R U
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: ProjectAnalysis

The types of the analysis which are available for a project

Column Data type Description Requ. Ind.
AnalysisID int ID of the analysis (primary key) R U
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: ProjectProcessing

The types of processing available within a project

Column Data type Description Requ. Ind.
ProcessingID int ID of the table Processing (foreign key and part of primary key) R U
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: Property

The list of the properties that can be specified for the collection site

Column Data type Description Requ. Ind.
PropertyID int Unique ID for the localisation system (= primary key) R U
PropertyParentID int LocalisationSystemID of the superior LocalisationSystem   -     -  
PropertyName nvarchar (100) Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS R   -  
DefaultAccuracyOfProperty nvarchar (50) The default for the accuracy of values which can be reached with this method   -     -  
DefaultMeasurementUnit nvarchar (50) The default measurement unit for the characterisation system, e.g. pH   -     -  
ParsingMethodName nvarchar (50) Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionLocalisation R   -  
DisplayText nvarchar (50) Short abbreviated description of the localisation system as displayed in the user interface   -     -  
DisplayEnabled 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.   -     -  
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.   -     -  
Description nvarchar (255) Description of the localisation method   -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: ReplicationPublisher

Column Data type Description Requ. Ind.
DatabaseName varchar (255) The name of the publishing database R U
Server varchar (255) The name or address of the server where the publishing database is located R U
Port smallint The port used by the server   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  


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 Requ. Ind.
TransactionID int Unique ID for the transaction (= primary key) R U
ParentTransactionID int The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy   -     -  
TransactionType nvarchar (50) Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out
DefaultValue: N'exchange'
R   -  
TransactionTitle nvarchar (200) The title of the transaction as e.g. shown in an user interface R   -  
ReportingCategory nvarchar (50) A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing   -     -  
AdministratingCollectionID int ID of the collection which is responsible for the administration of the transaction. R   -  
MaterialDescription nvarchar (MAX) ID of the project to which the transaction belongs (Projects are defined in DiversityProjects)   -     -  
MaterialCategory nvarchar (50) Material category of specimen. Examples: 'herbarium sheets', 'drawings', 'microscopic slides' etc.
DefaultValue: N'specimen'
  -     -  
MaterialCollectors nvarchar (MAX) The collectors of the material   -     -  
FromCollectionID int The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift   -     -  
FromTransactionPartnerName nvarchar (255) Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift   -     -  
FromTransactionPartnerAgentURI varchar (255) The URI of the transaction partner (see e.g. module DiversityAgents)   -     -  
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   -     -  
ToCollectionID int The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift   -     -  
ToTransactionPartnerName nvarchar (255) Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift   -     -  
ToTransactionPartnerAgentURI varchar (255) The URI of the transaction partner (see e.g. module DiversityAgents)   -     -  
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   -     -  
NumberOfUnits smallint The number of units which were (initially) included in the transaction   -     -  
Investigator nvarchar (200) The investigator for whose study a transacted material was sent   -     -  
TransactionComment nvarchar (MAX) Comments on the exchanged material addressed to the transaction partner   -     -  
BeginDate datetime Date when the transaction started   -     -  
AgreedEndDate datetime End of the transaction period, e.g. if the time for borrowing the specimen is restricted   -     -  
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   -     -  
InternalNotes nvarchar (MAX) Internal notes on this transaction not to be published e.g. on a web page   -     -  
ResponsibleName nvarchar (255) The person responsible for this transaction   -     -  
ResponsibleAgentURI varchar (255) The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents)   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  
ToRecipient nvarchar (255) The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents   -     -  


Table: TransactionComment

The standard text phrases for transactions

Column Data type Description Requ. Ind.
Comment nvarchar (400) Text as transferred into the comment of a transaction R U
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  


Table: TransactionDocument

The history of transactions or the documents connected to the transactions

Column Data type Description Requ. Ind.
TransactionID int Unique ID for the transaction, refers to table Transaction (= part of primary key and foreign key) R U
Date datetime The date of the event of a transaction R U
TransactionText nvarchar (MAX) The text of a transaction document   -     -  
TransactionDocument image (2147483647) A scanned document connected to this transaction   -     -  
DisplayText nvarchar (255) A display text as shown e.g. in a user interface to characterize the document   -     -  
InternalNotes nvarchar (MAX) Internal notes on this transaction   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate()
  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
R   -  



Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined R: It is required to enter data in this field. U: unique index


Template for enumeration tables

ER Enum 071101.gif

Unfortunately, some labels in the diagram are in German. “Spaltenname” translates to “column name”, “Datentyp-Kurzform” to “data type”, and “Beschreibung” to “column description”.


Known problems

URI for connection to DiversityTaxonNames, DiversityAgents, DiversityReferences and DiversityGazetteer.