MobileModel v1.0

DiversityMobile Information Model (version 1.0, 10 August 2010)

---

ER Diagram for context MONITORING

Overview over all entities and relations used in the database model

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview, the table descriptions 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.

---

Description and usage of the tables for the context MONITORING

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed and enumeration tables are not shown. Fields that are inapplicabel in the context MONITORING are not shown as well.

Color code

no restrictions	The value of this entity has no restriction
read only	The access to this entity is restricted to read only
hidden	The entity is hidden from a user interface
preset	The value of this entity is preset

Table: Analysis

Analysis types used within the database

Column	Data type	Description
AnalysisID	int	ID of the analysis (Primary key)
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 concerning this analysis
OnlyHierarchy	bit	If the entry is only used for the hierarchical arrangement of the entries DefaultValue: (0)

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
AnalysisID	int	ID of the analysis (Primary key)
AnalysisResult	nvarchar (255)	The categorized value of the analysis
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 about usage, definition, etc. of an enumerated object

Table: AnalysisTaxonomicGroup

The types of analysis that are available for a taxonomic group

Column	Data type	Description
AnalysisID	int	Analysis ID, foreign key of table Analysis.
TaxonomicGroup	nvarchar (50)	Taxonomic group the organism identified by this unit belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key)

Table: CollectionAgent

The collector(s) of collection specimens

Column	Data type	Description
CollectionSpecimenID	int	Refers to ID of CollectionEvent (= Foreign key and part of primary key)
CollectorsName	nvarchar (255)	Name of the Collector Preset value: SELECT CombinedNameCache FROM UserProxy WHERE LoginName = USER_NAME()
CollectorsAgentURI	varchar (255)	The URI of the Agent, e.g. as stored within the module DiversityAgents Preset value: SELECT UserURI FROM UserProxy WHERE LoginName = USER_NAME()
CollectorsSequence	datetime	The order of collectors in a team. Automatically set by the database system DefaultValue: getdate() Preset value: getdate()
CollectorsNumber	nvarchar (50)	Number assigned to a specimen or a batch of specimens by the collector during the collection event (= 'field number')

Table: CollectionEvent

The collection event where the specimen was collected

Column	Data type	Description
CollectionEventID	int	Unique ID for the collection event (= Primary key)
Version	int	The version of the dataset. Automatically set by the system. DefaultValue: (1)
SeriesID	int	The ID of the related expedition. Relates to the PK of the table CollectionExpedition (Foreign key).
CollectionDate	datetime	The date of the event calulated from the entries in CollectionDay, -Month and -Year. Preset value: getdate()
CollectionDay	tinyint	The day of the date of the event or when the collection event started Preset value: DAY(getdate())
CollectionMonth	tinyint	The month of the date of the event or when the collection event started Preset value: MONTH(getdate())
CollectionYear	smallint	The year of the date of the event or when the collection event started Preset value: YEAR(getdate())
CollectionDateCategory	nvarchar (50)	Category of the date of the identification e.g. "system", "estimated" (= foreign key, see in table CollEventDateCategory_Enum) Preset value: 'actual'
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)

Table: CollectionEventImage

The images showing the site of the collection event or other media like a voice recording

Column	Data type	Description
CollectionEventID	int	Unique ID for the collection event (= Primary key)
URI	varchar (255)	The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources
ImageType	nvarchar (50)	Type of the image, e.g. map Preset value: 'photography'

Table: CollectionEventLocalisation

The geographic localisation of a collection event

Column	Data type	Description
CollectionEventID	int	Refers to the ID of CollectionEvent (= Foreign key and part of primary key)
LocalisationSystemID	int	Refers to the ID of LocalisationSystem (= Foreign key and part of primary key)
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
DeterminationDate	smalldatetime	Date of the determination of the geographical localisation Preset value: getdate()
ResponsibleName	nvarchar (255)	The name of the agent (person or organization) responsible for this entry. DefaultValue: [dbo].[CurrentUserName]() Preset value: dbo.CurrentUserName()
ResponsibleAgentURI	varchar (255)	URI of the person or organisation responsible for the data (see e.g. module DiversityAgents) Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()
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
Geography	geography	The geography of the localisation

Table: CollectionEventProperty

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

Column	Data type	Description
CollectionEventID	int	Refers to the ID of CollectionEvent (= Foreign key and part of primary key)
PropertyID	int	The ID of the descriptor of the collection event, foreign key, see table Descriptor
DisplayText	nvarchar (255)	The text for the property as shown e.g. in a user interface
PropertyURI	varchar (255)	URI referring to an external datasource e.g. DiversityTerminology
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. Preset value: dbo.CurrentUserName()
ResponsibleAgentURI	varchar (255)	URI of the person or organisation responsible for the data (see e.g. module DiversityAgents) Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()
AverageValueCache	float	For numeric values - a cached average value according to the

Table: CollectionEventSeries

The series whithin which collection events take place

Column	Data type	Description
SeriesID	int	Primary key. The ID for this expedition (= Primary key)
Description	nvarchar (MAX)	The description of the expedition as it will be printed on e.g. the label
SeriesCode	nvarchar (50)	The user defined code for an expedition
Geography	geography	The geography of the collection event series
DateStart	datetime	The date and time when the collection event series started Preset value: getdate()
DateEnd	datetime	The date and time when the collection event series ended

Table: CollectionEventSeriesImage

The images showing the sites of a collection event series or other media like a voice recording

Column	Data type	Description
SeriesID	int	Unique ID for the collection event series (= Foreign key and part of primary key)
URI	varchar (255)	The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources
ImageType	nvarchar (50)	Type of the image, e.g. map Preset value: 'photography'

Table: CollectionProject

The projects within which the collection specimen were placed

Column	Data type	Description
CollectionSpecimenID	int	Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
ProjectID	int	ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)

Table: CollectionSpecimen

The data directly attributed to the collection specimen

Column	Data type	Description
CollectionSpecimenID	int	Unique reference ID for the collection specimen record (primary key)
Version	int	The version of the dataset DefaultValue: (1)
CollectionEventID	int	Refers to the ID of CollectionEvent (= Foreign key and part of primary key)
AccessionNumber	nvarchar (50)	Accession number of the specimen within the collection, e.g. "M-29834752"

Table: CollectionSpecimenImage

The images, voice recording or other medium of a collection specimen, an identification unit or part within this specimen

Column	Data type	Description
CollectionSpecimenID	int	Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
URI	varchar (255)	The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources
IdentificationUnitID	int	If image refers to only on out of several identification units for a specimen, refers to the ID of an IdentificationUnit for a CollectionSpecimen (= foreign key)
ImageType	nvarchar (50)	Type of the image, e.g. label Preset value: 'photography'

Table: Identification

The identifications of the organisms within a specimen

Column	Data type	Description
CollectionSpecimenID	int	Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID	int	Refers to the ID of IdentficationUnit (= foreign key and part of primary key)
IdentificationSequence	smallint	The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid DefaultValue: (1) Preset value: 1
IdentificationDate	datetime	The date of the identification calculated from the entries in IdentificationDay, -Month and -Year
IdentificationDay	tinyint	The day of the identification event Preset value: DAY(getdate())
IdentificationMonth	tinyint	The month of the identification event Preset value: MONTH(getdate())
IdentificationYear	smallint	The year of the identification event. The year may be empty if only the day or month are known. Preset value: YEAR(getdate())
IdentificationDateCategory	nvarchar (50)	Category of the date of the identification e.g. "system", "estimated" (= foreign key, see in table CollDateCategory_Enum) Preset value: 'actual'
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) Preset value: 'determination'
IdentificationQualifier	nvarchar (50)	Qualification of the identification e.g. "cf."," aff.", "sp. nov." (= foreign key, see table CollIdentificationQualifier_Enum)
ResponsibleName	nvarchar (255)	Name of the person or institution responsible for the determination Preset value: dbo.CurrentUserName()
ResponsibleAgentURI	varchar (255)	URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()

Table: IdentificationUnit

Organism that is present in or on a collection specimen

Column	Data type	Description
CollectionSpecimenID	int	Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID	int	ID of the identification unit (= part of Primary key). Usually one of possibly several organisms present on the collection specimen. Example: parasite with hyperparasite on plant leaf = 3 units,
LastIdentificationCache	nvarchar (255)	The last identification as entered in table Identification
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.
TaxonomicGroup	nvarchar (50)	Taxonomic group the organism identified by this unit belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key)
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) Preset value: False
RelatedUnitID	int	The IdentificationUnitID of the organism or substrate, on which this organism is growing (= foreign key)
RelationType	nvarchar (50)	The relation of an unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key)
ColonisedSubstratePart	nvarchar (255)	If a substrate association exists: part of the substrate that 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 gender of the identification unit, e.g. 'male'
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, esp. if not the 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

Table: IdentificationUnitAnalysis

The analysis values taken from an identification unit

Column	Data type	Description
CollectionSpecimenID	int	Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID	int	Refers to the ID of IdentficationUnit (= foreign key and part of primary key)
AnalysisID	int	Analysis ID, foreign key of table Analysis.
AnalysisNumber	nvarchar (50)	Number of the analysis Preset value: 1
AnalysisResult	nvarchar (MAX)	The result of the analysis
ResponsibleName	nvarchar (255)	Name of the person or institution responsible for the determination DefaultValue: [dbo].[CurrentUserName]() Preset value: dbo.CurrentUserName()
ResponsibleAgentURI	varchar (255)	URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. Preset value: SELECT AgentURI FROM UserProxy WHERE LoginName = USER_NAME()
AnalysisDate	nvarchar (50)	The date of the analysis Preset value: getdate()
Notes	nvarchar (MAX)	Notes concerning this analysis

Table: IdentificationUnitGeoAnalysis

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

Column	Data type	Description
CollectionSpecimenID	int	Refers to the ID of CollectionSpecimen (= Foreign key and part of primary key)
IdentificationUnitID	int	Refers to the ID of IdentficationUnit (= foreign key and part of primary key)
AnalysisDate	datetime	The date of the analysis
Geography	geography	The geography of the identification unit according to WGS84, e.g. a point (latitide, longitude and altitude)
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.

Table: Property

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

Column	Data type	Description
PropertyID	int	Unique ID for the property (= Primary key)
PropertyParentID	int	PropertyID of the superior Property
PropertyName	nvarchar (100)	Name of the system used for the characterisation of the collection site, e. g. Lithostratigraphy
DefaultAccuracyOfProperty	nvarchar (50)	The default for the accuracy of values that 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 the contents in table CollectionEventProperty
DisplayText	nvarchar (50)	Short abbreviated description of the characterisation system as displayed in the user interface
DisplayEnabled	bit	Specifies if this item is enabled to be used within the database. Properties can be disabled to avoid seeing them, but to 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 characterisation method

Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined

ER Diagram for the ENTITY tables

Overview over the entities and relations of the entity tables

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview, the table descriptions or the detail diagrams.

Description and usage of the entity tables

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed and enumeration tables are not shown.

Table: Entity

The entities in an application e.g. the tables and columns in a database

Column	Data type	Description
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
DisplayGroup	varchar (500)	If DiversityWorkbench entities should be displayed in a group, the name of the group
Notes	nvarchar (MAX)	Notes about the entity
Obsolete	bit	True if an entity is obsolete. Obsolete entities may be kept to ensure compatibility with older modules

Table: EntityRepresentation

The description of the entity in a certain context in different languages

Column	Data type	Description
Entity	varchar (500)	The name of the entity. Foreign key, relates to table Entity
LanguageCode	nvarchar (50)	ISO 639: 2-letter codes for the language of the content
EntityContext	nvarchar (50)	The context for the representation, e.g. "Exchange with ABCD", "collection management" or "observation" as defined in table EntityContext_Enum
DisplayText	nvarchar (50)	The text for the entity as shown e.g. in a user interface
Abbreviation	nvarchar (20)	The abbreviation for the entity as shown e.g. in a user interface
Description	nvarchar (MAX)	The description of the entity
Notes	nvarchar (MAX)	Notes about the representation of the entity

Table: EntityUsage

The usage of an entity in a certain context, e.g. hidden, readonly

Column	Data type	Description
Entity	varchar (500)	The name of the entity. Foreign key, relates 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
EntityUsage	nvarchar (50)	How the entity should be used within a certain context, e.g. "hidden" as defined in table EntityUsage_Enum
PresetValue	nvarchar (500)	If a value is preset the value resp. SQL statement for the value, e.g. 'determination' for identifications when using a mobile device during an expedition
Notes	nvarchar (MAX)	Notes about the usage of the entity

Description for the enumeration tables

All enumeration tables are read only and have an identical layout as shown below

Column	Data type	Description
Code	nvarchar (50)	A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it.
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.
DisplayEnable	bit	Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box)
InternalNotes	nvarchar (500)	Internal development notes about usage, definition, etc. of an enumerated object
ParentCode	nvarchar (50)	The code of the superior entry, if a hierarchy within the entries is necessary

Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined