DiversityScientificTermsModel v1.0

From Workbench
Jump to: navigation, search

DiversityScientificTerms Information Model (version 1.0, 20. April 2010)

Authors M. Weiss, G. Hagedorn & D. Triebel 2010
License 80x15.png
Suggested citation M. Weiss, G. Hagedorn & D. Triebel (2010). DiversityScientificTerms information model (version 2.1). http://www.diversityworkbench.net/Portal/DiversityScientificTerms_v1.0.
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.


ER Diagrams

Overview over all entities and relations used in the database model

ER

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:

  • “Terminology” Represents the termiologies containing the scientific terms.
  • “Term” Represents the scientific term with its relation to other terms.
  • “TermRepresentation” keeps the representations of the terms in different languages.

Entities ending with “_Enum” (“LanguageCode_Enum” etc.) contain enumerated values or strings.


Table: Term

The scientific term within a scientific terminology, e.g. the name of a soil in soil sciences or the name of a mineral in mineralogy

Column Data type Description Ind.
TerminologyID int The ID of an external data collection as defined in ExternalDatabase (foreign key)   -  
TermID int ID of the term, Primary key   -  
BroaderTermID int ID of the superior dataset within DiversityScientificTerms to which this record belongs to   -  
RankingTermID int ID of the ranking term of the dataset, e.g. Epoch for Paleocene   -  
IsRankingTerm bit If the term is a ranking term, e.g. epoch in palaeontolgoy
DefaultValue: (0)
  -  
Notes nvarchar (-1) Notes about the term   -  
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()
  -  

Table: Terminology

A terminology used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy

Column Data type Description Ind.
TerminologyID int An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)   -  
DisplayText nvarchar (50) The text for the terminology as shown e.g. in a user interface   -  
Description nvarchar (-1) The description of the terminology   -  
ExternalDatabase nvarchar (200) The name of the data collection that has been integrated or can be linked to for further analysis   -  
ExternalDatabaseVersion nvarchar (255) The version of this data collection (either official version number, or dates when the collection was integrated)   -  
ExternalDatabaseAuthors nvarchar (200) The persons or institutions responsible for the external database   -  
ExternalDatabaseURI varchar (255) The URI of the database provider resp. the external database   -  
ExternalDatabaseInstitution nvarchar (300) The institution responsible for the external database   -  
ExternalAttribute_NameID nvarchar (255) The table and field name in the external data collection to which TaxonNameExternalID refers   -  
Rights nvarchar (100) Information about rights (copyright, intellectual property) held in and over the database. Enter esp. name of person or institution holding the copyright. Leave empty if unknown.   -  
DefaultLanguageCode nvarchar (50) The code of the default language of the terminology
DefaultValue: N'en'
  -  
InternalNotes nvarchar (-1) Additional notes concerning this data collection   -  
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()
  -  

Table: TerminologyProperty

Properties of scientific terms in a certain terminology, e.g. the specific weight of a mineral in mineralogy

Column Data type Description Ind.
TerminologyID int An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)   -  
Property nvarchar (50) The name of the property   -  
DisplayText nvarchar (50) The text shown e.g. in a user interface   -  
Description nvarchar (-1) Description of the property   -  
Datatype varchar (50) The datatype of the property   -  
DisplayOrder smallint The sequence with which the properties are shown in a user interface if different from alphabetic order   -  
DisplayEnable bit True if the property should be shown, otherwise false   -  
InternalNotes nvarchar (500) Internal notes about this property   -  
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()
  -  

Table: TerminologyReference

A TerminologyReference used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy

Column Data type Description Ind.
TerminologyID int An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)   -  
Reference nvarchar (255) A link to e.g. a website containing a description of the terminology or the title of the publication where the collection event was published. Note this is only a cached value where ReferenceURI is present   -  
ReferenceURI varchar (255) URI of reference where specimen is published, e.g. referring to the module DiversityReferences   -  
ReferenceDetails varchar (255) The exact location within the reference, e.g. pages, plates   -  
InternalNotes nvarchar (-1) Additional notes concerning the reference   -  
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()
  -  

Table: TermProperty

The property of a scientific term, e.g. the specific weight of a certain mineral in mineralogy

Column Data type Description Ind.
TerminologyID int An ID to identify an external data collection of terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)   -  
TermID int ID of the term, referes to table Term, Foreign key   -  
Property nvarchar (50) The property as defined in table TermiologyProperty, Foreign key   -  
TextValue nvarchar (500) The text if the value is a text value   -  
NumericValue float The numeric value if the value is numeric   -  
DateValue datetime The date if the value is a date   -  
Notes nvarchar (-1) Notes about the property entry   -  
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()
  -  

Table: TermReference

A TermReference used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy

Column Data type Description Ind.
RepresentationID int ID of the term, referes to table TermRepresentation, Foreign key   -  
Reference nvarchar (255) A link to e.g. a website containing a description of the terminology or the title of the publication where the collection event was published. Note this is only a cached value where ReferenceURI is present   -  
ReferenceURI varchar (255) URI of reference where specimen is published, e.g. referring to the module DiversityReferences   -  
ReferenceDetails varchar (255) The exact location within the reference, e.g. pages, plates   -  
InternalNotes nvarchar (-1) Internal notes about the reference   -  
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()
  -  

Table: TermRepresentation

The representation of a scientific term within a scientific terminology in a certain language, e.g. the name of a soil in soil sciences in english, german or russian

Column Data type Description Ind.
RepresentationID int ID of the representation of a term within DiversityScientificTerms (primary key)   -  
TerminologyID int The ID of the terminology of to which the term belongs (foreign key)   -  
TermID int ID of the term to which the represenation belongs (foreign key)   -  
DisplayText nvarchar (400) The label or name of the term as shown e.g. in a user interface   -  
Description nvarchar (-1) Description of the term   -  
HierarchyCache nvarchar (-1) A system generated list defining the hierarchy of the term above the current entry, necessary to distinguish synonymous entries   -  
ExternalID nvarchar (50) If the entry was retrieved from an external database, a code or number for the entry as defined in the external database   -  
Notes nvarchar (-1) Notes about the term   -  
LanguageCode nvarchar (50) The code of the language of the property (foreign key)
DefaultValue: N'en'
  -  
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()
  -  


Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined I: The field is indexed to enable faster searching. U: unique index