DiversityTaxonNamesModel 2.5.29

From Diversity Workbench
Jump to: navigation, search

DiversityTaxonNames Information Model (version 2.5.29, 03 September 2019)

Authors G. Hagedorn, M. Weiss & D. Triebel 2019
License 80x15.png
Suggested citation G. Hagedorn, M. Weiss & D. Triebel (2019). DiversityTaxonNames information model (version 2.5.29). https://diversityworkbench.net/Portal/DiversityTaxonNamesModel_2.5.29
Notes 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.


dwbD Database Scheme

This information model is available as dwbD database scheme with each single data table and data column referenced as term or concept by its own stable and persistent URL.

ER Diagrams

Overview over the main entities and relations used in the database model

ER TaxonNames 020529.png

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

Organisation of the data

The data are organised in several groups as indicated with the colors in the image above, data connected with the name, data based on the opinion of an editor and the corresponding projects, the source of the data and the taxonomic lists.

TaxonName, TaxonGeography, TaxonNameTypification and TaxonCommonName are containing the nomenclatural information. Most items are expected to be undisputed nomenclatural facts. Some data items include editorial opinion (nomenclatural status, but also the accepted spelling which may involve orthographical or grammatical corrections as required by ICBN). However, it is expected that within a project agreement can be achieved on the information contained in TaxonName, i.e. collaborators can “correct” information without consultation.

The tables TaxonHierarchy, TaxonAcceptedName and TaxonSynonymy - represent a higher level of taxonomic opinion, where different editors and different digitized data sources need to be kept separate. This is achieved by making a “Project” attribute part of the primary key. Editors that wish to collaborate and form agreement may work in a single project, editors that desire to define separate opinions work in separate projects. Furthermore, if the opinion is based on published information, the reference and page number may be recorded (in ...RefText, ...RefID, ...RefDetail). The duality of representing editorial opinion and remaining true to a digitized sources causes some problems. For example, a species accepted in a referenced source may not longer be accepted by the project editors. To avoid a further complication of the model, the “IgnoreButKeepForReference” attribute (default “false”) may be set to “true”, indicating that the statement is true from the perspective of the referenced source, but should be ignored when representing editorial opinion.

TaxonHierarchy contains information about the hierarchical position of the taxa. For each project, a name may be referred to a single parent taxon. The attribute “NameParentID” may, however, be Null, in which case “HierarchyPositionIsUncertain” should be true. TaxonAcceptedName contains the accepted names (“valid or preferred taxon names”). For each project, a name in “TaxonName” may either be accepted (a corresponding record in “TaxonAcceptedName” exists) or not. Examples for not accepted names are synonyms of accepted names and nomenclaturally rejected names. TaxonSynonymy contains names, which are synonym to other names. Here a single name may be a synonym of multiple accepted names (“pro parte synonym”). The “pro parte” or corresponding sensu information is captured in the “ConceptSuffix” attribute, which is part of the primary key.

The tables TaxonNameList, TaxonNameListAnalysis, TaxonNameListAnalysisCategory, TaxonNameListArea, TaxonNameListDistribution, TaxonNameListReference contain informations about taxon list like checklists

TaxonNameExternalID and TaxonNameExternalDatabase document the sources of the names.

TaxonNameProject document the projects of the names.

Description of the tables and columns:

Table: TaxonAcceptedName

TaxonAcceptedName contains the accepted names ("valid or preferred taxon names"). For each project, a name in "TaxonName" may either be accepted (a corresponding record in "TaxonAcceptedName" exists) or not. Examples for not accepted names are synonyms of accepted names and nomenclaturally rejected names.

Column Data type Description Requ. Ind.
ProjectID int Each project can have a different opinion regarding synonmy. Refers to the common project definition in the DiversityProjects module.
DefaultValue: 0
R U
NameID int ID of the accepted name. Refers to the NameID of TaxonName (= foreign key). R U
IgnoreButKeepForReference tinyint If true, the record is ignored for all purposes of evaluation (because contradicted). It is kept only to maintain the cited reference. If no reference is given, it may be deleted instead.
DefaultValue: 0
R U
ConceptSuffix nvarchar (200) Empty for default and primary concept; else 's. lat.', 's. str.', 'sec.', 'sensu Muell.', 'emend.' , '(Auct.)' etc.   -     -  
ConceptNotes nvarchar (500) Notes on the concept of the name, e.g. incl. forms with black apothecia   -     -  
RefURI varchar (255) ReferenceURI: Source publication where synonymization is published (not publication of name!)   -     -  
RefText nvarchar (255) Free text, esp. where a RefURI is missing. Source publication where synonymization is published (not publication of name!)   -     -  
RefDetail nvarchar (255) Esp. page number on which the synonymization is published   -     -  
TypistsNotes nvarchar (255) An internal note of the responsible person concerning this synonymization. This information is NOT included in any report.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonCommonName

TaxonCommonName contains the common names that are assigned to a scientific name.

Column Data type Description Requ. Ind.
NameID int ID taxonomic name. Refers to the column NameID of the table TaxonName (= foreign key). R U
CommonName nvarchar (220) A common name of the taxonomic name R U
LanguageCode varchar (2) The 2-letter code of the language of the common name according to ISO R U
CountryCode varchar (2) The 2-letter code of the country where the common name is used according to ISO ISO 3166-1 R U
ReferenceTitle nvarchar (220) The title of the reference where the common name was published R U
ReferenceURI varchar (255) The URI of the reference e.g. as provided by the module DiversityReferences   -     -  
ReferenceDetails nvarchar (500) Esp. page number on which the common name is published   -     -  
SubjectContext nvarchar (500) The context in which the common name is used, e.g. pharmacy, food   -     -  
Transliteration nvarchar (255)     -     -  
Notes nvarchar (MAX) Notes about the common name   -     -  
LogInsertedBy nvarchar (50) Who inserted this dataset
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime The time when this dataset was inserted
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Who was the last to update this dataset
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime The last time when this dataset was updated
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonGeography

TaxonGeography is containing the information about the geographic distribution according to the protologue.

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
PlaceURI varchar (255) URI (e.g. LSID) within e.g. the DiversityGazetteer for a place (which may have several names) as found in the protologue R U
PlaceNameCache nvarchar (100) The name of the place R   -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonHierarchy

TaxonHierarchy contains information about the hierarchical position of the taxa. For each project, a name may be referred to a single parent taxon. The attribute "NameParentID" may, however, be Null, in which case "HierarchyPositionIsUncertain" should be true.

Column Data type Description Requ. Ind.
ProjectID int Each project can implement a different taxonomic hierarchy. Refers to the common project definition in the DiversityProjects module.
DefaultValue: 0
R U
NameID int Unique NameID code of the higher taxon. Refers to the NameID code of LichenName (= foreign key). R U
IgnoreButKeepForReference tinyint If true, the record is ignored for all purposes of evaluation (because contradicted). It is kept only to maintain the cited reference. If no reference is given, it may be deleted instead.
DefaultValue: 0
R U
NameParentID int Next higher taxon (e.g. the family or subfamily if this taxon is a genus)   -     -  
HierarchyRefURI varchar (255) Reference URI: Source publication where the hierarchy is published (not publication of name!) as stored e.g. in DiversityReferences   -     -  
HierarchyRefText nvarchar (255) Free citation, esp. where a HierarchyRefURI is missing. Source publication where the hierarchy is published (not publication of name!)   -     -  
HierarchyRefDetail nvarchar (255) Esp. page number on which the hierarchy is published   -     -  
HierarchyPositionIsUncertain bit If the position of this taxon within this taxonomic group is uncertain.
DefaultValue: 0
  -     -  
HierarchyTypistsNotes nvarchar (255) An internal note of the responsible person concerning this hierarchy. This information is NOT included in any report.   -     -  
HierarchyListCache nvarchar (1000) CALCULATED FIELD: List of higher taxa for faster access: "div.; class; ord.; fam."   -    
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonName

TaxonName is the nomenclatural information. Most items are expected to be undisputed nomenclatural facts. Some data items include editorial opinion (nomenclatural status, but also the accepted spelling which may involve orthographical or grammatical corrections as required by ICBN). However, it is expected that within a project agreement can be achieved on the information contained in TaxonName, i.e. collaborators can "correct" information without consultation.

Column Data type Description Requ. Ind.
NameID int The ID of a name in DiversityTaxonNames_Fungi (primary key) R U
TaxonNameCache nvarchar (255) A full unique version of the name. Generated by the database, not entered by the user (candidate key)   -    
Version smallint The version of a name record (revision number, internally filled by system)
DefaultValue: (1)
  -     -  
BasedOnNameID int The Basionym of this name, resp. the NameID of the Basionym. A name is a Basionym, if NameID = BasionymID.   -     -  
CreationType nvarchar (50) E.g.: taxon based on new type, combination based on a previously publ. name ('comb. nov.'), new name ('nom.nov.') introduced to replace a homonym (may occur for genera!), validation of previously invalidly publ. taxon name ('ex'), or unknown.   -     -  
TaxonomicRank nvarchar (50) Taxonomic rank of the taxon (var., subsp., species, genus, family, order, etc.). The rank must be selected from the associated list of ranks (= TaxonomyRank)
DefaultValue: N'sp.'
R   -  
GenusOrSupragenericName nvarchar (200) If rank is above species: Name of taxon above species level (currently accepted spelling). Includes infrageneric taxon names, genera, families, etc. R   -  
SpeciesGenusNameID int If rank is species or below: NameID of the Genus name. Refers to same table with TaxonomicRank = genus.   -     -  
InfragenericEpithet nvarchar (200) If rank below genus and above species: Name of infrageneric taxon above species level (currently accepted spelling). Includes subgenus, series, etc.   -     -  
SpeciesEpithet nvarchar (100) The species name part of the species name, for example 'alba' in 'Abies alba'.   -     -  
InfraspecificEpithet nvarchar (100) The epithet of the infraspecific entity   -     -  
BasionymAuthors nvarchar (100) The authors of a newly created name. For all taxonomic ranks, only for 'comb. nov.' or 'nom. nov.': Author(s) of the basionym (will be displayed in '()', do not enter the parentheses), abbreviated according to authors standard   -     -  
CombiningAuthors nvarchar (255) The names of the combining authors if the name is base on another older name (e.g. combined into a different genus)   -     -  
PublishingAuthors nvarchar (255) If the authors of the taxon differ from the authors of the publication: the latter (...'in ' Publishing authors ... )   -     -  
SanctioningAuthor nvarchar (100) Sanctioning is a special instrument available for fungi to allow the starting date of taxonomy to be identical with higher plants, but preserve names used by Fries (Fr.) and Person (Pers.)   -     -  
NonNomenclaturalNameSuffix nvarchar (200) A suffix for concept names not included in any nomenclatural code. If present fields for authors and typification must be empty and NomenclaturalCode set to 'not under code'.   -     -  
IsRecombination bit True if the name is a recombination
DefaultValue: (0)
R   -  
IsHybrid bit If checked (or 'True'), the new taxon is a hybrid with or without a hybrid epithet.
DefaultValue: (0)
  -     -  
HybridNameID1 int If IsHybrid is checked (or 'True'): Name of hybrid species 1. Refers to NomID code of publ. TaxonomyName (= foreign key).   -     -  
HybridNameID2 int If IsHybrid is checked (or 'True'): Name of hybrid species 2. Refers to NomID code of publ. TaxonomyName (= foreign key).   -     -  
HybridNameID3 int If IsHybrid is checked (or 'True'): Name of hybrid species 3. Refers to NomID code of publ. TaxonomyName (= foreign key).   -     -  
HybridNameID4 int If IsHybrid is checked (or 'True'): Name of hybrid species 4. Refers to NomID code of publ. TaxonomyName (= foreign key).   -     -  
ReferenceTitle nvarchar (600) The title of the publication where the name was published. Note this is only a cached value where ReferenceURI is present   -     -  
ReferenceURI varchar (255) URI (e.g. LSID) of Reference, referes to table ReferenceTitle in Database DiversityReferences: Source publication where name is published   -     -  
Volume nvarchar (20) The volume of the journal   -     -  
Issue nvarchar (255) The issue of the literature   -     -  
Pages nvarchar (50) The pages within the literature   -     -  
DetailLocation nvarchar (200) Additional information like plates etc.   -     -  
DayOfPubl tinyint The day when the name was published   -     -  
MonthOfPubl tinyint The month when the name was published   -     -  
YearOfPubl smallint The year when the name was published   -     -  
DateOfPublSupplement nvarchar (200) Verbal or additional date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'   -     -  
YearOnPubl smallint The year cited on the original paper as year of publication   -     -  
DateOnPublSupplement nvarchar (200) Verbal or additional date information, e.g. 'end of summer 1985', 'first quarter', '1888-1892'   -     -  
Protologue nvarchar (MAX) Full text of the protologue. If protologue is provided in Latin and in another language

languages, both only the Latin or several languages may be stored together.

  -     -  
ProtologueURI varchar (255) URI of externally available Protologue information (scanned image, full text from external provider, etc.)   -     -  
ProtologueResourceURI varchar (255) The ResourceID of an image of the protologue as stored in the module DiversityResources. Primary key of table Resource in the database DiversityResources.(= Foreign key)   -     -  
NameUsageReferences nvarchar (255) Indexing volumes like Index of Fungi or any Name usage that caused this name to be added to the database   -     -  
OriginalOrthography nvarchar (255) The original spelling of the name   -     -  
NomenclaturalCode nvarchar (50) Code of Nomenclature under which this taxon was created: 'Bacteriology', 'Botany' (incl. Mycology), 'Zoology', 'Biocode' (for future use), 'Non nomenclatural name'. Default value: 'Botany'   -     -  
NomenclaturalStatus nvarchar (50) Categories for effective/valid/legitimate... esp. 'nom. illeg.', 'nom. inval.', 'nom. nudum', etc.   -     -  
NomenclaturalComment nvarchar (MAX) Comments on the nomenclature. e.g. 'according to ICBN Art. 39.1'   -     -  
Typification nvarchar (50) The status of the type specimen(s) as written in the protologue, e.g. holotype   -     -  
TypificationDetails nvarchar (255) Details concerning the typification as written in the protologue, e.g. if just parts of a specimen were accepted as the type   -     -  
TypificationReferenceTitle nvarchar (255) The title of the publication where the typification was published. Note this is only a cached value where Typification-LiteratureLink is present   -     -  
TypificationReferenceURI varchar (255) URI (e.g. LSID) of Reference, refers to e.g DiversityReferences: Source publication where the typification is published   -     -  
TypificationNotes nvarchar (200) Notes concerning the typification   -     -  
TypeSubstrate nvarchar (255) The substrate the type was growing on as written in the protologue   -     -  
TypeLocality nvarchar (255) The locality where the type was found as written in the protologue   -     -  
TypeSpecimenNotes nvarchar (200) Notes about the type specimen, e.g. the herbarium where a type specimen is located, Collector, collection date/number, etc.   -     -  
AnamorphTeleomorph nvarchar (255) If the name is related to a anamorph or a teleomorph as written in the protologue   -     -  
TypistNotes nvarchar (200) Additional notes and problems   -     -  
RevisionLevel nvarchar (50) The level of the revision of the taxonomic name, e.g. 'unchecked', 'fully checked'   -     -  
IgnoreButKeepForReference bit If true, the record is ignored for all purposes of evaluation (because contradicted). It is kept only to maintain the cited reference. If no reference is given, it may be deleted instead.
DefaultValue: (0)
  -     -  
DataWithholdingReason nvarchar (255) If the dataset is withhold, the reason for withholding the data, otherwise null   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  
BasionymAuthorsYear int The year when the name was published by the authors of the Basionym   -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameExternalDatabase

TaxonNameExternalDatabase document the sources of the names.

Column Data type Description Requ. Ind.
ExternalDatabaseID int An ID to identify an external data collection of plant names (primary key, the ID has no meaning outside of the DiversityWorkbench system) R U
ExternalDatabaseName nvarchar (800) The name of the data collection that has been integrated or can be linked to for further analysis R   -  
ExternalDatabaseVersion 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   -     -  
ExternalDatabaseAuthors nvarchar (200) The persons or institutions responsible for the external database   -     -  
ExternalDatabaseURI nvarchar (300) The URI of the database provider resp. the external database   -     -  
ExternalDatabaseInstitution nvarchar (300) The institution responsible for the external database   -     -  
InternalNotes nvarchar (1500) Additional notes concerning this data collection   -     -  
ExternalAttribute_NameID nvarchar (255) The table and field name in the external data collection to which TaxonNameExternalID 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   -     -  
LogCreatedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogCreatedWhen datetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameExternalID

TaxonNameExternalID document the source of a name together with the ID of the source.

Column Data type Description Requ. Ind.
NameID int The ID of a name in DiversityTaxonNames (foreign key + part of primary key: the name string associated with NameID may occur in multiple external databases) R U
ExternalDatabaseID int The ID of an external taxonomic data collection as defined in TaxontNameExternalDatabase (foreign key + part of primary key) R U
ExternalNameURI varchar (255) The URI (e.g. a LSID) of the external name as defined in the external database   -     -  
LogCreatedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogCreatedWhen datetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen datetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameList

The table is containing the names listed in e.g. a checklist corresponding to the project.

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int Each project can may contain one taxon list. Refers to the common project definition in the DiversityProjects module. R U
Notes nvarchar (MAX) An note concerning this entry.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListAnalysis

Analysis values for list entries in the database, "Red list category: R", "Time of observation: Sept. - Nov."

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int Each project can may contain one taxon list. Refers to the common project definition in the DiversityProjects module. R U
TaxonNameListRefID int Unique ID of the reference within the list, refers to table TaxonNameListReference, part of primary key
DefaultValue: (-1)
R U
AnalysisID int ID of the analysis (= foreign key) R U
AnalysisValue nvarchar (MAX) The result of the analysis   -     -  
Notes nvarchar (MAX) Notes concerning this analysis   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListAnalysisCategory

Analysis types used within the database, e.g. "Red list category", "Frequency"

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   -     -  
AnalysisURI varchar (255) URI referring to an external documentation of the analysis   -     -  
Notes nvarchar (MAX) Notes concerning this analysis   -     -  
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()
  -     -  
ReferenceTitle nvarchar (800) The title of the publication where details about the analysis are published. Note this is only a cached value where ReferenceURI is present   -     -  
ReferenceURI varchar (400) URI (e.g. LSID) of Reference, referes to table ReferenceTitle in module DiversityReferences: Source publication where details about the analysis are published   -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListAnalysisCategoryValue

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
AnalysisValue 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 about usage, definition, etc. of an enumerated object   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListArea

The area defined for the whole taxon list

Column Data type Description Requ. Ind.
ProjectID int Each project can have a different opinion regarding the parameters defined for a list. Refers to the common project definition in the DiversityProjects module. R U
PlaceURI varchar (255) URI or identifier for a place (which may have several names) derived from e.g. TDWG R U
PlaceNameCache nvarchar (255) The name of the place   -     -  
Notes nvarchar (MAX) An note concerning this entry.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListCollectionSpecimen

The collection specimens on which the taxon list is based

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int ProjectID of the taxon list. Foreign key and part of primary key. R U
DisplayText varchar (255) The name of the collection specimen as shown e.g. in a user interface. Part of the primary key R U
CollectionSpecimenURI varchar (255) URI of the collection specimen. May refer to the module CollectionSpecimen   -     -  
Role nvarchar (50) The role of the specimen in relation to the taxon list (= foreign key, see table TaxonNameListSpecimenRole_Enum)   -     -  
Description nvarchar (MAX) Description of the collection specimen   -     -  
PlaceURI varchar (255) URI or identifier for a place (which may have several names) derived from e.g. TDWG   -     -  
PlaceNameCache nvarchar (255) The name of the place   -     -  
Notes nvarchar (MAX) Notes about the collection specimen   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListDistribution

The geographical distrbution for the organisms within the taxon list.

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int Each project can may contain one taxon list. Refers to the common project definition in the DiversityProjects module. R U
TaxonNameListRefID int Unique ID of the reference within the list, refers to table TaxonNameListReference, part of primary key
DefaultValue: (-1)
R U
PlaceURI varchar (255) URI or identifier for a place (which may have several names) derived from e.g. TDWG R   -  
PlaceNameCache nvarchar (255) The name of the place R U
Notes nvarchar (MAX) Notes concerning this analysis   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListImage

Media, e.g. images of the organisms within the taxon list

Column Data type Description Requ. Ind.
NameID int ID of the accepted name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int Each project can may contain one taxon list. Refers to the common project definition in the DiversityProjects module. R U
URI varchar (255) The complete URI address of the image. R U
Description xml (MAX) Description of the image   -     -  
Notes nvarchar (MAX) Notes concerning this analysis   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListProjectProxy

The taxon lists defined within the database. Links to the module DiversityProject

Column Data type Description Requ. Ind.
ProjectID int ID of the Project, Primary key. May be related to module DiversityProjects R U
Project nvarchar (50) The name of the project. May be related to module DiversityProjets R   -  
ProjectURI varchar (255) The URI of the project, e.g. as provided by the module DiversityProjects.   -     -  
TaxonomicGroup nvarchar (50) The taxonomic group as used in DiversityCollection resp. DiversityMobile   -     -  
DisplayText nvarchar (50) The text as shown in a user interface. Corresponds to ProjectTitle in DiversityProjects.Project   -     -  
DefaultProjectID int The ID of the default project where informations about e.g. the synonymy are taken from   -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListReference

The reference where the taxon list resp. the name is published

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int Each project can may contain one taxon list. Refers to the common project definition in the DiversityProjects module. R U
TaxonNameListRefID int Unique ID of the reference within the list, part of primary key R U
TaxonNameListRefText nvarchar (800) Free text, esp. where a TaxonNameListRefURI is missing. Source publication where distribution is published (not publication of name!) R   -  
TaxonNameListRefURI varchar (255) Reference URI: Source publication where the distribution is published (not publication of name!) as stored e.g. in DiversityReferences   -     -  
TaxonNameListRefDetail nvarchar (255) Esp. page number on which the distribution is published   -     -  
ReferenceType nvarchar (50) The type of the reference, e.g. a webpage   -     -  
TaxonUsageContext nvarchar (50) The context of the usage of the taxonomic name within the reference, e.g. ecology   -     -  
Notes nvarchar (MAX) An note concerning this entry.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameListUser

The table is containing the lists accessible for a user.

Column Data type Description Requ. Ind.
LoginName nvarchar (50) Login of a user. R U
ProjectID int Project accessible for a user. Refers to ProjectID in table TaxonNameList (= foreign key). R U


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameProject

TaxonNameProject documents the projects of the names and provides access to the data.

Column Data type Description Requ. Ind.
NameID int The ID of a name in DiversityTaxonNames (foreign key + part of primary key: the name may occur in multiple projects) R U
ProjectID int ID of the project. Refers to the common project definition in the DiversityProjects module.
DefaultValue: 0
R U
LogInsertedWhen datetime The time when this dataset was inserted
DefaultValue: getdate()
  -     -  
LogInsertedBy nvarchar (50) Who inserted 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()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameReference

References related to a taxonomic name

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
TaxonNameRefID int Unique ID of the reference, part of primary key R U
TaxonNameRefText nvarchar (800) The citation text of the reference R   -  
TaxonNameRefURI varchar (500) Reference URI: Link to the source where the publication as stored e.g. DiversityReferences   -     -  
TaxonNameRefDetail nvarchar (255) Esp. page number on which the distribution is published   -     -  
ReferenceType nvarchar (50) The type of the reference, e.g. a webpage   -     -  
Notes nvarchar (MAX) An note concerning this entry.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameResource

Resouces related to the taxonomic name

Column Data type Description Requ. Ind.
NameID int ID of the name. Refers to the NameID of TaxonName (= foreign key). R U
ProjectID int ID of the project. Refers to the common project definition in the DiversityProjects module. R U
URI varchar (255) The complete URI address of the resource. R U
Description xml (MAX) Description of the resource   -     -  
Notes nvarchar (MAX) A note concerning this entry.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonNameTypification

The typification of a taxonomic name, e.g. where the type information of the name was published

Column Data type Description Requ. Ind.
NameID int The ID of a name in DiversityTaxonNames_Fungi (primary key) R U
TypificationReferenceTitle nvarchar (255) The title of the publication where the typification was published. Note this is only a cached value where Typification-LiteratureLink is present R U
TypificationReferenceURI varchar (255) URI (e.g. LSID) of Reference, refers to e.g DiversityReferences: Source publication where the typification is published   -     -  
Typification nvarchar (500) The status of the type specimen(s) as written in the protologue, e.g. holotype   -     -  
TypificationDetails nvarchar (255) Details concerning the typification as written in the protologue, e.g. if just parts of a specimen were accepted as the type   -     -  
TypificationNotes nvarchar (200) Notes concerning the typification   -     -  
TypeSubstrate nvarchar (255) The substrate the type was growing on as written in the protologue   -     -  
TypeLocality nvarchar (255) The locality where the type was found as written in the protologue   -     -  
TypeSpecimenNotes nvarchar (500) Notes about the type specimen, e.g. the herbarium where a type specimen is located, Collector, collection date/number, etc.   -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION

Table: TaxonSynonymy

TaxonSynonymy contains names, which are synonym to other names. Here a single name may be a synonym of multiple accepted names ("pro parte synonym"). The "pro parte" or corresponding sensu information is captured in the "ConceptSuffix" attribute, which is part of the primary key.

Column Data type Description Requ. Ind.
ProjectID int Each project can have a different opinion regarding synonmy. Refers to the common project definition in the DiversityProjects module.
DefaultValue: (0)
R U
NameID int ID of the synonymized name. Refers to the NameID of TaxonName (= foreign key). R U
SynNameID int The ID of the synonym to which this name has been assigned to. Refers to the NameID of TaxonName (= foreign key). R U
IgnoreButKeepForReference tinyint If true, the record is ignored for all purposes of evaluation (because contradicted). It is kept only to maintain the cited reference. If no reference is given, it may be deleted instead.
DefaultValue: (0)
R U
ConceptSuffix nvarchar (200) Empty for default and primary concept; else 's. lat.', 's. str.', 'sec.', 'sensu Muell.', 'emend.' , '(Auct.)' etc.   -     -  
ConceptNotes nvarchar (500) Notes on the concept of the name, e.g. incl. forms with black apothecia   -     -  
SynRefURI varchar (255) Reference URI: Source publication where synonymization is published (not publication of name!) as stored e.g. in DiversityReferences   -     -  
SynRefText nvarchar (255) Free text, esp. where a SynRefURI is missing. Source publication where synonymization is published (not publication of name!)   -     -  
SynRefDetail nvarchar (255) Esp. page number on which the synonymization is published   -     -  
SynTypistsNotes nvarchar (255) An internal note of the responsible person concerning this synonymization. This information is NOT included in any report.   -     -  
SynType nvarchar (50) Type of the synonymization, e.g. 'heterotypic', 'homotypic'
DefaultValue: 'unknown'
R   -  
SynIsUncertain bit If the synoymization is uncertain. Corresponding to =? for heterotypic synonyms and ? if the type of the synonymisation is unknown. Homotypic synonyms can not be uncertain.
DefaultValue: (0)
  -     -  
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()
  -     -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: user_name()
  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate()
  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
  -     -  


Relations to other tables

Columns Related table Columns in related table Update rule Delete rule
ProjectID ProjectProxy ProjectID NO ACTION NO ACTION
NameID, ProjectID TaxonNameProject NameID, ProjectID NO ACTION NO ACTION


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