CollectionModel v2.02

From Diversity Workbench
Jump to: navigation, search

DiversityCollection Information Model (version 2.02, 25. January 2006)

By M. Weiss, G. Hagedorn, & D. Triebel 2006

Note: The models currently reside in MS SQL Server, so knowledge of some SQL Server ER-diagram conventions will be helpful.

Besides the screen shots below, a Microsoft T-SQL-Script for the generation of the tables is provided.

Proposed citation:
M. Weiss, G. Hagedorn, & D. Triebel (2006). DiversityCollection information model (version 2.02).

ER Diagrams

Overview over all entities and relations used in the database model

ER Collection 060120.png

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 CollectorsAgentID refers to the module DiversityAgent within the Diversity Workbench.
  • “CollectionSpecimenImage” keeps the images for a specimen. The ResourceID 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.
  • “CollectionExpedition”. An expedition can contain other expeditions and collection events. The expedition should be used to organise your collection events.
  • “CollectionGeography”. 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.
  • “CollectionHabitat”. 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 fields NameID and NameThesaurusSignature are used.
  • “CollectionStorage”. This tables keeps the informations about 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.
  • “CollectionExchange” 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.

Entity relationship diagram 2: Details

CollectionSpecimen and related tables

ER Collection Speci 060120.png

CollectionEvent and related tables.

ER Collection Event 060120.png

Identification and related tables.

ER Collection Ident 060120.png

CollectionStorage and related tables.

ER Collection Stor 060120.png

The tables shown are identical to those in diagram 1, but the arrangement had to be changed to be able to show data type and description of each column.

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.

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

Template for enumeration tables

ER Enum.png

Known problems

LSID, GUID for CollectionSpecimen. LSID for connection to TaxonNames. The analysis tables (IdentificationUnitAnalysis, Analysis and AnalysisExternalDatabase) are used to store measurement data collected on an identification unit within the specimen. These tables will be replaced by an upcoming module of the Diversity Workbench.