Difference between revisions of "DiversityScientificTermsModel v1.0.15"
From Diversity Workbench
Anton Link (Talk | contribs) |
(→DiversityScientificTerms Information Model (version 1.0.15, 19 October 2023)) |
||
| Line 11: | Line 11: | ||
|-style="text-align:left; background:#F5F5F5;" | |-style="text-align:left; background:#F5F5F5;" | ||
|Suggested citation | |Suggested citation | ||
| − | |M. Weiss, G. Hagedorn & D. Triebel (2023). DiversityScientificTerms information model (version 1.0.15). http://www.diversityworkbench.net/Portal/ | + | |M. Weiss, G. Hagedorn & D. Triebel (2023). DiversityScientificTerms information model (version 1.0.15). http://www.diversityworkbench.net/Portal/DiversityScientificTermsModel_v1.0.15 |
|- | |- | ||
|-style="text-align:left; background:#FFFFFF;" | |-style="text-align:left; background:#FFFFFF;" | ||
Latest revision as of 19:28, 22 October 2023
Contents
- 1 DiversityScientificTerms Information Model (version 1.0.15, 19 October 2023)
- 1.1 dwbST Database Scheme
- 1.2 ER Diagrams
- 1.2.1 Overview over all entities and relations used in the database model
- 1.2.2 A short introduction:
- 1.2.3 Table: Section
- 1.2.4 Table: SectionTerm
- 1.2.5 Table: Term
- 1.2.6 Table: TermDependency
- 1.2.7 Table: Terminology
- 1.2.8 Table: TerminologyProperty
- 1.2.9 Table: TerminologyPropertyValue
- 1.2.10 Table: TerminologyReference
- 1.2.11 Table: TermProperty
- 1.2.12 Table: TermReference
- 1.2.13 Table: TermRepresentation
- 1.2.14 Table: TermResource
DiversityScientificTerms Information Model (version 1.0.15, 19 October 2023)
| Authors | M. Weiss, G. Hagedorn & D. Triebel 2023 |
| License | 
|
| Suggested citation | M. Weiss, G. Hagedorn & D. Triebel (2023). DiversityScientificTerms information model (version 1.0.15). http://www.diversityworkbench.net/Portal/DiversityScientificTermsModel_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
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
