DiversityScientificTermsModel v1.0
From Diversity Workbench
Revision as of 17:05, 1 July 2014 by Dagmar Triebel (Talk | contribs)
DiversityScientificTerms Information Model (version 1.0, 20. April 2010)
| Authors | M. Weiss, G. Hagedorn & D. Triebel 2010 |
| License | 
|
| 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
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
