Difference between revisions of "DiversityScientificTermsModel v1.0.15"

From Diversity Workbench
Jump to: navigation, search
Line 41: Line 41:
  
 
----
 
----
 +
 +
 +
===Table: Section===
 +
A section of terms within a scientific terminology, e.g. the terms of a certain geographic area extracted from a terminology
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|The ID of the terminology, refers to PK in table Terminology (foreign key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>SectionID</u>
 +
|int
 +
|ID of the section, Primary key
 +
|R
 +
|U
 +
|-
 +
|DisplayText
 +
|varchar (500)
 +
|The label or name of the section
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|Notes
 +
|nvarchar (MAX)
 +
|Notes about the section
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedBy
 +
|nvarchar (50)
 +
|ID of user who first entered (typed or imported) the recorde into this system. This is the operator (or typist) name, which may be different from the person responsible.<br />''DefaultValue: [dbo].[UserID]()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedBy
 +
|nvarchar (50)
 +
|ID of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.<br />''DefaultValue: [dbo].[UserID]()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: SectionTerm===
 +
A term within a section of terms within a scientific terminology, e.g. the terms of a certain geographic area extracted from a terminology
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|The ID of the terminology, refers to PK in table Terminology (foreign key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>SectionID</u>
 +
|int
 +
|ID of the section, Part of primary key
 +
|R
 +
|U
 +
|-
 +
|<u>TermID</u>
 +
|int
 +
|ID of the term, Part of primary key
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|LogInsertedBy
 +
|nvarchar (50)
 +
|ID of user who first entered (typed or imported) the recorde into this system. This is the operator (or typist) name, which may be different from the person responsible.<br />''DefaultValue: [dbo].[UserID]()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedBy
 +
|nvarchar (50)
 +
|ID of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.<br />''DefaultValue: [dbo].[UserID]()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===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
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|The ID of the terminology, refers to PK in table Terminology (foreign key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>TermID</u>
 +
|int
 +
|ID of the term, Primary key
 +
|R
 +
|U
 +
|-
 +
|BroaderTermID
 +
|int
 +
|ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|RankingTermID
 +
|int
 +
|ID of the ranking term of the term, e.g. Epoch is rank for Paleocene, family is rank of Rosaceae
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|IsRankingTerm
 +
|bit
 +
|If the term is a ranking term, e.g. epoch in paleontolgoy<br />''DefaultValue: (0)''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|PreferredRepresentationID
 +
|int
 +
|The ID of the preferred representation of this term
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|TermIdentifier
 +
|nvarchar (500)
 +
|An identifier for a term, valid for all representations of this term
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|Notes
 +
|nvarchar (MAX)
 +
|Notes about the term
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TermDependency===
 +
Dependency within terms
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|The ID of the terminology, refers to PK in table Terminology (foreign key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>TermID</u>
 +
|int
 +
|ID of the term, Part of primary key
 +
|R
 +
|U
 +
|-
 +
|<u>DependsOnTermID</u>
 +
|int
 +
|ID of the term the current term depens on, Part of primary key
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|LogInsertedBy
 +
|nvarchar (50)
 +
|<br />''DefaultValue: [dbo].[UserID]()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedBy
 +
|nvarchar (50)
 +
|<br />''DefaultValue: [dbo].[UserID]()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: Terminology===
 +
A terminology used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|The ID of the terminology (primary key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|DisplayText
 +
|nvarchar (50)
 +
|The text for the terminology as shown e.g. in a user interface
 +
|R
 +
|&nbsp; - &nbsp;
 +
|-
 +
|Description
 +
|nvarchar (MAX)
 +
|The description of the terminology
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ExternalDatabase
 +
|nvarchar (200)
 +
|The name of the source that has been imported or can be linked to for further analysis
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|ExternalDatabaseVersion
 +
|nvarchar (255)
 +
|The version of this data collection (either official version number, or dates when the collection was integrated)
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ExternalDatabaseAuthors
 +
|nvarchar (200)
 +
|The persons or institutions responsible for the external database
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|ExternalDatabaseURI
 +
|varchar (255)
 +
|The URI of the database provider resp. the external database
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ExternalDatabaseInstitution
 +
|nvarchar (300)
 +
|The institution responsible for the external database
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|ExternalAttribute_NameID
 +
|nvarchar (255)
 +
|The table and field name in the external data collection to which TaxonNameExternalID refers
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|DefaultLanguageCode
 +
|nvarchar (50)
 +
|The code of the default language of the terminology<br />''DefaultValue: N'en'''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ProjectURI
 +
|varchar (500)
 +
|Link to the module DiversityProjects
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|InternalNotes
 +
|nvarchar (MAX)
 +
|Additional notes concerning this data collection
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ArchiveProtocol
 +
|nvarchar (MAX)
 +
|The protocol created during the last archive
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TerminologyProperty===
 +
Properties of scientific terms in a certain terminology, e.g. the specific weight of a mineral in mineralogy
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>Property</u>
 +
|nvarchar (50)
 +
|The name of the property
 +
|R
 +
|U
 +
|-
 +
|DisplayText
 +
|nvarchar (50)
 +
|The text shown e.g. in a user interface
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|Description
 +
|nvarchar (MAX)
 +
|Description of the property
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|Datatype
 +
|varchar (50)
 +
|The datatype of the property
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|DisplayOrder
 +
|smallint
 +
|The sequence with which the properties are shown in a user interface if different from alphabetic order
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|DisplayEnable
 +
|bit
 +
|True if the property should be shown, otherwise false
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|InternalNotes
 +
|nvarchar (500)
 +
|Internal notes about this property
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TerminologyPropertyValue===
 +
Dedicated property values of a certain property of a scientific term
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>Property</u>
 +
|nvarchar (50)
 +
|The name of the property
 +
|R
 +
|U
 +
|-
 +
|<u>DisplayText</u>
 +
|nvarchar (50)
 +
|The text shown e.g. in a user interface
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|Description
 +
|nvarchar (MAX)
 +
|Description of the property
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|InternalNotes
 +
|nvarchar (500)
 +
|Internal notes about this property
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TerminologyReference===
 +
A TerminologyReference used in a certain scientifc area, e.g. mineralogy, soil sciences, paleontology, stratigraphy
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|An ID to identify an external data collection of scientific terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>Reference</u>
 +
|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
 +
|R
 +
|U
 +
|-
 +
|ReferenceURI
 +
|varchar (255)
 +
|URI of reference where specimen is published, e.g. referring to the module DiversityReferences
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ReferenceDetails
 +
|varchar (255)
 +
|The exact location within the reference, e.g. pages, plates
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|InternalNotes
 +
|nvarchar (MAX)
 +
|Additional notes concerning the reference
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TermProperty===
 +
The property of a scientific term, e.g. the specific weight of a certain mineral in mineralogy
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>TerminologyID</u>
 +
|int
 +
|An ID to identify an external data collection of terms (primary key, the ID has no meaning outside of the DiversityScientificTerms)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>TermID</u>
 +
|int
 +
|ID of the term, refers to table Term (foreign key)
 +
|R
 +
|U
 +
|-
 +
|<u>Property</u>
 +
|nvarchar (50)
 +
|The property as defined in table TermiologyProperty, Foreign key
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|TextValue
 +
|nvarchar (500)
 +
|The text if the value is a text value
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|NumericValue
 +
|float
 +
|The numeric value if the value is numeric
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|DateValue
 +
|datetime
 +
|The date if the value is a date
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|Notes
 +
|nvarchar (MAX)
 +
|Notes about the property entry
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TermReference===
 +
A reference for a term used in a certain scientific area, e.g. mineralogy, soil sciences, paleontology, stratigraphy
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>RepresentationID</u>
 +
|int
 +
|ID of the term, refers to table TermRepresentation (foreign key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>Reference</u>
 +
|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
 +
|R
 +
|U
 +
|-
 +
|ReferenceURI
 +
|varchar (255)
 +
|URI of reference where specimen is published, e.g. referring to the module DiversityReferences
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|ReferenceDetails
 +
|varchar (255)
 +
|The exact location within the reference, e.g. pages, plates
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|InternalNotes
 +
|nvarchar (MAX)
 +
|Internal notes about the reference
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===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 or German
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>RepresentationID</u>
 +
|int
 +
|ID of the representation of a term within DiversityScientificTerms (primary key)
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|TerminologyID
 +
|int
 +
|The ID of the terminology in which the term belongs (foreign key)
 +
|R
 +
|&nbsp; - &nbsp;
 +
|-
 +
|TermID
 +
|int
 +
|ID of the term to which the representation belongs (foreign key)
 +
|R
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|DisplayText
 +
|nvarchar (400)
 +
|The label or name of the term as shown e.g. in a user interface
 +
|R
 +
|&nbsp; - &nbsp;
 +
|-
 +
|Description
 +
|nvarchar (MAX)
 +
|Description of the term
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|HierarchyCache
 +
|nvarchar (MAX)
 +
|A system generated list defining the hierarchy of the term above the current entry, necessary to distinguish synonymous entries
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|HierarchyCacheDown
 +
|nvarchar (MAX)
 +
|A system generated list defining the hierarchy from the top to the current entry, necessary to distinguish synonymous entries
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|Notes
 +
|nvarchar (MAX)
 +
|Notes about the term
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LanguageCode
 +
|nvarchar (50)
 +
|The code of the language of the property  (foreign key)<br />''DefaultValue: N'en'''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|DisplayOrder
 +
|int
 +
|The order as shown in die user interface
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|DisplayARGB
 +
|int
 +
|The 8-Bit-ARGB-values (Alpha, red, green und blue) as shown in die user interface
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|DisplayInheritARGB
 +
|bit
 +
|If the display color should be inherited to depending values<br />''DefaultValue: (0)''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogInsertedWhen
 +
|smalldatetime
 +
|Date and time when record was first entered (typed or imported) into this system.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|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.<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedWhen
 +
|smalldatetime
 +
|Date and time when record was last updated.<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
===Table: TermResource===
 +
A resource connected to the scientific term, e.g. a picture, video etc.
 +
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
 +
|-style="text-align:left; background:#D3D3D3;"
 +
!Column
 +
!Data type
 +
!Description
 +
!Requ.
 +
!Ind.
 +
|-
 +
|<u>RepresentationID</u>
 +
|int
 +
|ID of the term, refers to table TermRepresentation, Foreign key
 +
|R
 +
|U
 +
|-style="background:WhiteSmoke;"
 +
|<u>URI</u>
 +
|varchar (255)
 +
|The complete URI address of the resource. This is only a cached value, if ResourceID is available and referring to the module DiversityResources
 +
|R
 +
|U
 +
|-
 +
|ResourceURI
 +
|varchar (255)
 +
|The URI of the resource, e.g. as stored in the module DiversityResources.
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|Notes
 +
|nvarchar (MAX)
 +
|Notes on the resource
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|Description
 +
|xml (MAX)
 +
|Description of the resource
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|Title
 +
|nvarchar (500)
 +
|Title of the resource
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|IPR
 +
|nvarchar (500)
 +
|Intellectual Property Rights; the rights given to persons for their intellectual property
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|Creator
 +
|nvarchar (500)
 +
|Person or organization originally creating the resource
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|CreatorAgentURI
 +
|varchar (255)
 +
|Link to the module DiversityAgents
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|CopyrightStatement
 +
|nvarchar (500)
 +
|Notice on rights held in and for the resource
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LicenseType
 +
|nvarchar (500)
 +
|Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|InternalNotes
 +
|nvarchar (500)
 +
|Internal notes which should not be published e.g. on websites
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LicenseHolder
 +
|nvarchar (500)
 +
|The person or institution holding the license
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LicenseHolderAgentURI
 +
|nvarchar (500)
 +
|The link to a module containing further information on the person or institution holding the license
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LicenseYear
 +
|nvarchar (50)
 +
|The year of license declaration
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|DisplayOrder
 +
|int
 +
|The sequence in which the resource should be displayed in a user interface
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|DataWithholdingReason
 +
|nvarchar (255)
 +
|If the data set is withhold, the reason for withholding the data, otherwise null
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogCreatedWhen
 +
|datetime
 +
|Point in time when this data set was created<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogCreatedBy
 +
|nvarchar (50)
 +
|Name of the creator of this data set<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|LogUpdatedWhen
 +
|datetime
 +
|Point in time when this data set was updated last<br />''DefaultValue: getdate()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-
 +
|LogUpdatedBy
 +
|nvarchar (50)
 +
|Name of the person to update this data set last<br />''DefaultValue: user_name()''
 +
|&nbsp; - &nbsp;
 +
|&nbsp; - &nbsp;
 +
|-style="background:WhiteSmoke;"
 +
|RowGUID
 +
|uniqueidentifier
 +
|<br />''DefaultValue: newsequentialid()''
 +
|R
 +
|&nbsp; - &nbsp;
 +
|}
 +
 +
 +
 +
 +
'''Footnotes''': The following conventions and abbreviations have been used in the tables: Columns of primary key:<u>underlined</u> '''R''': It is required to enter data in this field. '''I''': The field is indexed to enable faster searching. '''U''': unique index

Revision as of 12:26, 19 October 2023

DiversityScientificTerms Information Model (version 1.0.15, 19 October 2023)

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


dwbST Database Scheme

This information model is available as dwbST 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 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: Section

A section of terms within a scientific terminology, e.g. the terms of a certain geographic area extracted from a terminology

Column Data type Description Requ. Ind.
TerminologyID int The ID of the terminology, refers to PK in table Terminology (foreign key) R U
SectionID int ID of the section, Primary key R U
DisplayText varchar (500) The label or name of the section   -     -  
Notes nvarchar (MAX) Notes about the section   -     -  
LogInsertedBy nvarchar (50) ID of user who first entered (typed or imported) the recorde into this system. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: [dbo].[UserID]() 		  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate() 		  -     -  
LogUpdatedBy nvarchar (50) ID of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: [dbo].[UserID]() 		  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate() 		  -     -  


Table: SectionTerm

A term within a section of terms within a scientific terminology, e.g. the terms of a certain geographic area extracted from a terminology

Column Data type Description Requ. Ind.
TerminologyID int The ID of the terminology, refers to PK in table Terminology (foreign key) R U
SectionID int ID of the section, Part of primary key R U
TermID int ID of the term, Part of primary key R U
LogInsertedBy nvarchar (50) ID of user who first entered (typed or imported) the recorde into this system. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: [dbo].[UserID]() 		  -     -  
LogInsertedWhen smalldatetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate() 		  -     -  
LogUpdatedBy nvarchar (50) ID of user who last updated the data. This is the operator (or typist) name, which may be different from the person responsible.
DefaultValue: [dbo].[UserID]() 		  -     -  
LogUpdatedWhen smalldatetime Date and time when record was last updated.
DefaultValue: getdate() 		  -     -  


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 Requ. Ind.
TerminologyID int The ID of the terminology, refers to PK in table Terminology (foreign key) R U
TermID int ID of the term, Primary key R U
BroaderTermID int ID of the superior term within DiversityScientificTerms to which this record belongs to, e.g. Rosaceae is broader term for Rosa   -     -  
RankingTermID int ID of the ranking term of the term, e.g. Epoch is rank for Paleocene, family is rank of Rosaceae   -     -  
IsRankingTerm bit If the term is a ranking term, e.g. epoch in paleontolgoy
DefaultValue: (0) 		  -     -  
PreferredRepresentationID int The ID of the preferred representation of this term   -     -  
TermIdentifier nvarchar (500) An identifier for a term, valid for all representations of this term   -     -  
Notes nvarchar (MAX) 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: TermDependency

Dependency within terms

Column Data type Description Requ. Ind.
TerminologyID int The ID of the terminology, refers to PK in table Terminology (foreign key) R U
TermID int ID of the term, Part of primary key R U
DependsOnTermID int ID of the term the current term depens on, Part of primary key R U
LogInsertedBy nvarchar (50)
DefaultValue: [dbo].[UserID]() 		  -     -  
LogInsertedWhen smalldatetime
DefaultValue: getdate() 		  -     -  
LogUpdatedBy nvarchar (50)
DefaultValue: [dbo].[UserID]() 		  -     -  
LogUpdatedWhen smalldatetime
DefaultValue: getdate() 		  -     -  


Table: Terminology

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

Column Data type Description Requ. Ind.
TerminologyID int The ID of the terminology (primary key) R U
DisplayText nvarchar (50) The text for the terminology as shown e.g. in a user interface R   -  
Description nvarchar (MAX) The description of the terminology   -     -  
ExternalDatabase nvarchar (200) The name of the source that has been imported 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' 		  -     -  
ProjectURI varchar (500) Link to the module DiversityProjects   -     -  
InternalNotes nvarchar (MAX) Additional notes concerning this data collection   -     -  
ArchiveProtocol nvarchar (MAX) The protocol created during the last archive   -     -  
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 Requ. 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) R U
Property nvarchar (50) The name of the property R U
DisplayText nvarchar (50) The text shown e.g. in a user interface   -     -  
Description nvarchar (MAX) 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: TerminologyPropertyValue

Dedicated property values of a certain property of a scientific term

Column Data type Description Requ. 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) R U
Property nvarchar (50) The name of the property R U
DisplayText nvarchar (50) The text shown e.g. in a user interface R U
Description nvarchar (MAX) Description of the property   -     -  
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 Requ. 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) R U
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 R U
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 (MAX) 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 Requ. Ind.
TerminologyID int An ID to identify an external data collection of terms (primary key, the ID has no meaning outside of the DiversityScientificTerms) R U
TermID int ID of the term, refers to table Term (foreign key) R U
Property nvarchar (50) The property as defined in table TermiologyProperty, Foreign key R U
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 (MAX) 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 reference for a term used in a certain scientific area, e.g. mineralogy, soil sciences, paleontology, stratigraphy

Column Data type Description Requ. Ind.
RepresentationID int ID of the term, refers to table TermRepresentation (foreign key) R U
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 R U
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 (MAX) 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 or German

Column Data type Description Requ. Ind.
RepresentationID int ID of the representation of a term within DiversityScientificTerms (primary key) R U
TerminologyID int The ID of the terminology in which the term belongs (foreign key) R   -  
TermID int ID of the term to which the representation belongs (foreign key) R   -  
DisplayText nvarchar (400) The label or name of the term as shown e.g. in a user interface R   -  
Description nvarchar (MAX) Description of the term   -     -  
HierarchyCache nvarchar (MAX) A system generated list defining the hierarchy of the term above the current entry, necessary to distinguish synonymous entries   -     -  
HierarchyCacheDown nvarchar (MAX) A system generated list defining the hierarchy from the top to 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 (MAX) Notes about the term   -     -  
LanguageCode nvarchar (50) The code of the language of the property (foreign key)
DefaultValue: N'en' 		  -     -  
DisplayOrder int The order as shown in die user interface   -     -  
DisplayARGB int The 8-Bit-ARGB-values (Alpha, red, green und blue) as shown in die user interface   -     -  
DisplayInheritARGB bit If the display color should be inherited to depending values
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() 		  -     -  


Table: TermResource

A resource connected to the scientific term, e.g. a picture, video etc.

Column Data type Description Requ. Ind.
RepresentationID int ID of the term, refers to table TermRepresentation, Foreign key R U
URI varchar (255) The complete URI address of the resource. This is only a cached value, if ResourceID is available and referring to the module DiversityResources R U
ResourceURI varchar (255) The URI of the resource, e.g. as stored in the module DiversityResources.   -     -  
Notes nvarchar (MAX) Notes on the resource   -     -  
Description xml (MAX) Description of the resource   -     -  
Title nvarchar (500) Title of the resource   -     -  
IPR nvarchar (500) Intellectual Property Rights; the rights given to persons for their intellectual property   -     -  
Creator nvarchar (500) Person or organization originally creating the resource   -     -  
CreatorAgentURI varchar (255) Link to the module DiversityAgents   -     -  
CopyrightStatement nvarchar (500) Notice on rights held in and for the resource   -     -  
LicenseType nvarchar (500) Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses   -     -  
InternalNotes nvarchar (500) Internal notes which should not be published e.g. on websites   -     -  
LicenseHolder nvarchar (500) The person or institution holding the license   -     -  
LicenseHolderAgentURI nvarchar (500) The link to a module containing further information on the person or institution holding the license   -     -  
LicenseYear nvarchar (50) The year of license declaration   -     -  
DisplayOrder int The sequence in which the resource should be displayed in a user interface   -     -  
DataWithholdingReason nvarchar (255) If the data set is withhold, the reason for withholding the data, otherwise null   -     -  
LogCreatedWhen datetime Point in time when this data set was created
DefaultValue: getdate() 		  -     -  
LogCreatedBy nvarchar (50) Name of the creator of this data set
DefaultValue: user_name() 		  -     -  
LogUpdatedWhen datetime Point in time when this data set was updated last
DefaultValue: getdate() 		  -     -  
LogUpdatedBy nvarchar (50) Name of the person to update this data set last
DefaultValue: user_name() 		  -     -  
RowGUID uniqueidentifier
DefaultValue: newsequentialid() 		R   -  



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

Retrieved from "https://diversityworkbench.net/w/index.php?title=DiversityScientificTermsModel_v1.0.15&oldid=23443"