DiversityReferencesModel 2.1.0

From Diversity Workbench
Jump to: navigation, search

DiversityReferences Information Model (version 2.1.0 7. January 2020)

Authors G. Hagedorn, M. Weiss, A. Kohlbecker, A. Link & D. Triebel 2020
License 80x15.png
Suggested citation G. Hagedorn, M. Weiss, A. Kohlbecker, A. Link & D. Triebel (2020). DiversityReferences information model (version 2.1.0). https://diversityworkbench.net/Portal/DiversityReferencesModel_2.1.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.



dwbR Database Scheme

This information model is available as dwbR 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

References Model 2.1.0_IMG

A short introduction:

The central table for each reference is the ReferenceTitle. It contains the majority of fields that correspond to the ReferenceManager model. Some information, especially information that may occur multiple times, is provided through secondary tables. Authors, editors, and series editors (as well as potential support for creators and collaborators not supported in ReferenceManager) are found in the table ReferenceRelator (each author is one record). The availability (who has it, has it some storage code, when was it ordered, etc.) is found in ReferenceAvailability. Keywords, and markers for “collections” can be found in ReferenceDescriptor/ReferencePrivateDescriptor, notes, abstracts, etc. in ReferenceNote/ReferencePrivateNote.

Relators (authors, editors, etc.) are treated as strings, not as identifiable objects. The same person may publish under slightly or strongly varying names, and the same name may belong to multiple persons. Researching authorship identity is not normally done and in many cases would be next to impossible.

The tables ReferencePeriodical and ReferencePeriodicalSynonym contain an independent subsystem to manage known and corrected journal, magazine, etc. titles and their synonyms. Synonyms may be the full title, possibly in various languages, various abbreviation schemes (including semi-private ones). For example, the “Canadian Journal of Botany” may also be known as “Canad. Journ. Botany”, “Can. J. Bot”, “CJB”, but also “Revue canadienne de botanique”. The synonymy list may contain selected spelling errors as well.

Note, that the two tables support client software in advising users about potential errors during data entry, improving the speed and accuracy of data entry for complicated journal names, or find spelling errors during revisions. They may also be used when generating formatted output. They do not, however, constrain data entry: ReferenceTitle.Journal may contain titles not present in ReferencePeriodical/ReferencePeriodicalSynonym. Many periodical names are historical and we believe it is not efficient to first initiate a special editing process (perhaps with revision) before being able to enter a reference citing a periodical name. Furthermore, references may contain incorrect information, and considerable research may be necessary to establish this.

Additional information about differences to older major database versions can be found at the predecessor of this database version: DiversityReferences 2.0. September 13th, 2006


Table: Ref_AvailabilityReprintStatus_Enum

Provides codes for ReferenceAvailability.ReprintStatus field

Column Data type Description
ReprintStatus smallint Reprint status code, as used in ReferenceAvailability
DefaultValue: (0)
Label nvarchar (80) A concise label for the status
Description nvarchar (1000) A description, explaining usage, semantics, and scope to the user
ShowInPicklist bit Only values/codes selected here are available in a pick list; others may be entered if the user knows their value/code
DefaultValue: (0)
DisplayOrder int Order in which values will be display in a pick list or report
DefaultValue: (0)
Notes nvarchar (4000) Internal notes regarding usage or semantics


Table: Ref_RelatorRole_Enum

Provides codes for creator or contributor roles like author, editor, photographer, advisor, etc. These roles are used in ReferenceRelator

Column Data type Description
RelatorRole nvarchar (3) A relator type describing the relation of a person or institution to a publication. Relator codes are currently based on MARC
Label nvarchar (80) A concise label for the role
Description nvarchar (MAX) A description, explaining usage, semantics, and scope to the user
ShowInPicklist bit Only values/codes selected here are available in a pick list; others may be entered if the user knows their value/code
DefaultValue: (0)
DisplayOrder int Order in which values will be display in a pick list or report
DefaultValue: (0)
Notes nvarchar (1000) Internal notes regarding usage or semantics


Table: Ref_Type_Enum

Reference type definitions. Which reference attributes are enabled and how they should be labeled? Currently only the usage of a type is defined here; the attribute labels are defined directly in the vba code.

Column Data type Description
RefType nvarchar (10) ReferenceTitle types, as used by Reference Manager (tm) v.9-11. Additional types may be added if they occur in the import.
Label nvarchar (80) A description, explaining the scope and use of the reference type to the user
Description nvarchar (1000) A description, explaining the scope and use of the reference type to the user
ShowInPicklist bit Only reference types selected here are available in a pick list; other types may be entered if the user knows their type code.
DefaultValue: (0)
Notes nvarchar (MAX) Internal notes
RequiredDate bit  
RequiredJour bit  
Title nvarchar (255) Labeltext for the field ReferenceTitle.Title
Date nvarchar (255) Labeltext for the combination of fields ReferenceTitle.DateYear + ReferenceTitle.DateMonth + ReferenceTitle.DateDay
Date2 nvarchar (255) Labeltext for the combination of fields ReferenceTitle.Date2Year + ReferenceTitle.Date2Month + ReferenceTitle.Date2Day
SourceTitle nvarchar (255) Labeltext for the field ReferenceTitle.SourceTitle
SeriesTitle nvarchar (255) Labeltext for the field ReferenceTitle.SeriesTitle
Periodical nvarchar (255) Labeltext for the field ReferenceTitle.SeriesTitle
Volume nvarchar (255) Labeltext for the field ReferenceTitle.Volume
Issue nvarchar (255) Labeltext for the field ReferenceTitle.Issue
Pages nvarchar (255) Labeltext for the field ReferenceTitle.Pages
Publisher nvarchar (255) Labeltext for the field ReferenceTitle.Publisher
PublPlace nvarchar (255) Labeltext for the field ReferenceTitle.PublPlace
Edition nvarchar (255) Labeltext for the field ReferenceTitle.Edition
ISSN_ISBN nvarchar (255) Labeltext for the field ReferenceTitle.ISSN_ISBN
WebLinks nvarchar (255) Labeltext for the field ReferenceTitle.URL
Miscellaneous1 nvarchar (255) Labeltext for the field ReferenceTitle.Miscellaneous1
Miscellaneous2 nvarchar (255) Labeltext for the field ReferenceTitle.Miscellaneous2
Miscellaneous3 nvarchar (255) Labeltext for the field ReferenceTitle.Miscellaneous3
Child_Address nvarchar (255)  
Child_ReprintStatus nvarchar (255) Labeltext for the child table field ReferenceAvailability.ReprintStatus
UserDef1 nvarchar (255) Labeltext for the child table field ReferenceUserDefined.UserDef1
UserDef2 nvarchar (255) Labeltext for the child table field ReferenceUserDefined.UserDef2
UserDef3 nvarchar (255) Labeltext for the child table field ReferenceUserDefined.UserDef3
UserDef4 nvarchar (255) Labeltext for the child table field ReferenceUserDefined.UserDef4
UserDef5 nvarchar (255) Labeltext for the child table field ReferenceUserDefined.UserDef5
Child_PrivateNote nvarchar (255) Labeltext for the child table field ReferenceUserNote.Notes
Child_PrivateDescriptor nvarchar (255) Labeltext for the child table field ReferenceUserMarker.Marker
Child_Descriptor nvarchar (255) Labeltext for the child table field ReferenceKeyword.Keyword
Child_Note nvarchar (255) Labeltext for the child table field ReferenceAbstact.Abstact
Child_Availability nvarchar (255) Labeltext for the child table field ReferenceAvailability.?


Table: ReferenceAvailability

Availability and location of reference items in private or official filing system; e.g., book signatures or reprint article availability. Each responsible user may enter multiple filing codes.

Column Data type Description
RefID int Refers to the ID code of the main ReferenceTitle table (= foreign key)
RecordID int Unique random ID (see trigger) to identify the availability record. (Technical note: to improve reliability of database replication, the primary key is formed in combination with the RefID. Note that FilingCode is optional and not suitable.)
DefaultValue: CONVERT([int],rand()*(2147483647.1),(0))
FilingCode nvarchar (255) Information about availability or location of a copy of the referenced publication: Filing code of reprint or book in private filing system, institutional catalogue code, signature, official call number, or shelf code in a library. //[RefMan 27: AV]
ReprintStatus smallint Refers to filing system of responsible person. Reprint may be 'Not in file' (0), 'On request (card to author)' (1), 'On request (internal order/copy marker)' (2), 'On request (interlibrary loan)' (3), or 'In file' (4) //[RefMan 08: RP pro parte]
DefaultValue: (0)
RequestDate datetime Only if ReprintStatus = 'On request' (1/2): The date on which the reprint was requested. //[RefMan 08: RP pro parte]
Responsible int The person responsible for the availability/filing code information, and to which the reprint status/request date refers. //[RefMan: not supported]


Table: ReferenceDescriptor

Object names, event names, keywords, etc., providing indexing information for a resource.

Column Data type Description
RefID int ID of external resource to which the descriptor applies (foreign key)
Language varchar (5) Language in which element content is expressed. Necessary even for numeric or date content (because expressed through string using language-specific conventions)
DefaultValue: 'en'
ElementID int ID of a descriptor element concept (foreign key)
DefaultValue: (0)
Content nvarchar (255) A name, state, or value text for the descriptor element.
ContentURI varchar (255) The URI of a conceptual ontological resource considered equivalent with the content, especially URIs for taxon names or keywords from ontologies.
InstanceGrouping smallint Normally Null. If set, element relations are evaluated only within same-numbered instances. Example: 3 host-pathogen-pairs exist in one resource, each pair would get same instance number. Still, a place name set to instance=Null would apply to all.
ID int Internal system generated primary key. Note that multiple values for a descriptor concept may be added (e.g. keywords)


Table: ReferenceDescriptorElement

Examples of descriptor elements (= concepts for variables) are keyword, taxon name, pathogen name, host name, or host feature. Association with ResourceCollections is defined in ReferenceDescriptorAssociation, relations in Res.DescriptorElementRelation.

Column Data type Description
ElementID int Numeric identifier (primary key).
ElementAbbrev nvarchar (25) Short abbreviated name for descriptor element.
ElementLabel nvarchar (80) Concise English label of a descriptor element definition.
DisplayOrder int Order in which elements are displayed independently of a resource collection (for order within a collection see ReferenceDescriptorAssociation.DisplayOrder).
DefaultValue: (0)
ElementDescription nvarchar (1000) A free-form text that may be displayed in user interfaces as explanatory text.
ElementURI varchar (255) The URI of a conceptual ontological resource considered equivalent with this descriptor element.
InternalNotes nvarchar (1000) Internal notes and remarks. Although normally not published in public reports, this should not be used for truly confidential information.
DisplayEnabled bit Whether this DescriptorElement is to be displayed in the user interface.


Table: ReferenceDescriptorElementRelation

General relations between descriptor elements (applicable to all values present in ReferenceDescriptor)

Column Data type Description
FromElement int Starting point of relation (foreign key, part of primary key)
RoleLabel nvarchar (25) Abbreviated label for relation in forward direction
ToElement int End point of relation (foreign key, part of primary key)
RoleDescription nvarchar (1000) A free-form text that may be displayed in user interfaces as explanatory text.
RoleURI varchar (255) The URI of a conceptual ontological resource considered equivalent with the role of this relation.
InternalNotes nvarchar (1000) Internal notes and remarks. Although normally not published in public reports, this should not be used for truly confidential information.


Table: ReferenceNote

Public reference abstracts/notes.

Column Data type Description
RefID int Refers to the ID code of the main ReferenceTitle table (= foreign key)
Responsible int * The person responsible for the abstract information. Abstracts are copyright protected! Clearly state if the abstract was not written by you, but copied from the publication itself or a bibliographic database. //[RefMan: not supported]
DefaultValue: [dbo].[wbCurrentUserID]()
Language varchar (20) * Language of the abstract, as ISO 2 letter codes. //[RefMan : not supported!]
DefaultValue: 'UNK'
Content nvarchar (4000) An abstract containing a short summary of the content of the article or book //[RefMan 25: N2]


Table: ReferencePeriodical

Periodical (journal/magazine, etc.) titles.

Column Data type Description
Abbreviation nvarchar (255) Standardized abbreviation of periodical or journal. Use periods after the abbreviations //[RefMan 11: JA, JO]
FullName nvarchar (255) Full, non-abbreviated name of periodical or journal //[RefMan 11: JF, JO]
Notes nvarchar (4000) Notes, remarks, or comments regarding the journal/periodical as a whole, incl. "continued as (new title)" or notes about local availability
ImportedFrom nvarchar (255) If imported from another database: The name of the database system or provider; otherwise empty.
PeriodicalID int  


Table: ReferencePeriodicalProject

Column Data type Description
Abbreviation nvarchar (255) Standardized abbreviation of periodical or journal, as defined in ReferencePeriodical entity (= foreign key).
ProjectID int ID of the project to which the periodical belongs (Projects are defined in DiversityProjects)


Table: ReferencePeriodicalSynonym

Periodical (journal/magazine, etc.) titles: thesaurus with synonyms -> valid name. Any entry in Periodical.Abbreviation and Periodical.FullName must also be added to the synonym table.

Column Data type Description
Synonym nvarchar (255) Alternative, synonymous names for the main record. Automatically translated into StdAbbrev if a thesaurus pick list is used in forms. //[RefMan 11: J1, J2]
Abbreviation nvarchar (255) Standardized abbreviation of periodical or journal, as defined in ReferencePeriodical entity (= foreign key).
Source nvarchar (255) Source of the synonym/thesaurus name: 'ABBR' for standard abbreviation, 'FULL' for standard full name, else name or abbrev. of user who added a non-standard synonym (like PNAS for Proc. Nat. Acad. Sci.)


Table: ReferencePrivateDescriptor

Object names, event names, keywords, etc., providing indexing information for a resource.

Column Data type Description
RefID int ID of external resource to which the descriptor applies (foreign key)
PrivateTo int (UNUSED : using table ProvateDescriptor instead!)Either the UserID of the user who created this descriptor for private usage or null for a public descriptor
DefaultValue: [dbo].[wbCurrentUserID]()
Language varchar (5) Language in which element content is expressed. Necessary even for numeric or date content (because expressed through string using language-specific conventions)
DefaultValue: 'en'
ElementID int ID of a descriptor element concept (foreign key)
DefaultValue: (0)
Content nvarchar (255) A name, state, or value text for the descriptor element.
ContentURI varchar (255) The URI of a conceptual ontological resource considered equivalent with the content, especially URIs for taxon names or keywords from ontologies.
InstanceGrouping smallint Normally Null. If set, element relations are evaluated only within same-numbered instances. Example: 3 host-pathogen-pairs exist in one resource, each pair would get same instance number. Still, a place name set to instance=Null would apply to all.
ID int Internal system generated primary key. Note that multiple values for a descriptor concept may be added (e.g. keywords)


Table: ReferencePrivateNote

User specific notes. Each user will only see the notes entered under the same responsible user name.

Column Data type Description
RefID int Refers to the ID code of the main ReferenceTitle table (= foreign key)
PrivateTo int * The person responsible for the Notes. //[RefMan: not supported]
DefaultValue: [dbo].[wbCurrentUserID]()
Content nvarchar (4000) Internal notes regarding the reference title. Notes will normally be visible only under the same Responsible login name. //[RefMan 06: N1, AB]


Table: ReferenceProject

The projects within which the Reference were placed

Column Data type Description
RefID int Refers to the ID of ReferenceTitle (= Foreign key and part of primary key)
ProjectID int ID of the project to which the Reference belongs (Projects are defined in DiversityProjects)


Table: ReferenceRelator

Reference authors, book editors, or series editors.

Column Data type Description
RefID int Refers to the ID code of the main ReferenceTitle table (= foreign key)
Role nvarchar (3) Relator codes from MARC; Reference manager supports only aut = primary author, 2 = secondary author/editor, 3 = series editor. //[RefMan implicit]
Sequence int The sequence of authors of the article. (Default based on system date/time; counter attrib. wouldn't work with replication -> random sequence! Note: Au+RefID+Type is not necessarily unique. Two authors may have identical abbreviated names, e.g. spouses!)
DefaultValue: CONVERT([int],(99999)*(CONVERT([float],getdate(),(0))-(37200)),(0))
Name nvarchar (255) Author, editor, etc. Example: 'Miller, W. I., Jr.'. Format: Last name, comma, first initial with period and blank, optional middle initial, and optional comma plus suffix (Jr./Sr./III./MD etc.) //[RefMan 04: A1/AU, 14: A2/ED, 24: A3; no '*' allowed!]
AgentURI varchar (255) The URI of the Agent, e.g. as stored within the module DiversityAgents
SortLabel nvarchar (255) Name of the agent without special characters formatted to facilitate sorting
Address nvarchar (1000) The address of the author, if available. Entered only in cases where it is of special relevance to one of the users of the database (or if imported from a database). //[RefMan 32: AD]


Table: ReferenceTitle

Main entity; compatible with Reference Manager™ v.9-11

Column Data type Description
RefType nvarchar (10) Type of literature reference, determines which fields are available for data entry. The value must come from the pick list provided. //[RefMan 01: TY]
DefaultValue: 'JOUR'
RefID int Unique reference ID code for the reference record. Currently supporting only integer numbers. Note that RefMan in principle supports 20 char., but uses only integers. //[RefMan 02: ID]
DefaultValue: CONVERT([int],rand()*(2147483647.1),(0))
RefDescription_Cache nvarchar (255) * A short system generated text identifying the reference, usually authors, year, title. Example: 'Smith. & Nao 1999. New Taxa.' //[RefMan: not applicable]
Title nvarchar (MAX) The main (primary) title. Use normal capitalization, omit a period ('.') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 03: TI, T1, CT, BT only for BOOK & UNPB]
DateYear smallint Year of the publication date (primary date). Only numbers are allowed and the year must be entered with 4 digits ('1998', not '98'). //[RefMan 05: Y1,PY pro parte]
DateMonth smallint Optional: The month of the publication date. [Note: in DateYear/Month/Day the information printed on the book or journal are entered, even if this is not the true date!] //[RefMan 05: Y1,PY pro parte]
DateDay smallint Optional: The day of the publication date. [Note continued: if the true date is relevant, e.g. for the purpose of nomenclatural priority, it can be entered under DateSecondary.] //[RefMan 05: Y1,PY pro parte]
DateSuppl nvarchar (255) Optional: A date supplement, like 'approx.', a season ('Summer'), a quarter ('1st Quarter'), or any other information regarding the publication date. //[RefMan 05: Y1,PY pro parte]
SourceTitle nvarchar (MAX) The book (secondary) title in cases where the reference is an article or chapter from a book. Use normal capitalization.//[RefMan 13: T2; BT for all types except BOOK & UNPB]
SeriesTitle nvarchar (255) The series title. Use normal capitalization, omit a period ('.') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 23: T3]
Periodical nvarchar (255) Journal/periodical in which the article appeared. Linked to the Abbreviation attribute of ReferencePeriodical. //[RefMan 11: JF, JO, JA]
Volume nvarchar (255) The volume (for periodicals or journals, excluding the issue number), report number, etc. //[RefMan 12: VL pro parte, comp. Edition!]
Issue nvarchar (255) The issue, if any. Useful also to enter a special designation for a supplement, for example for 'xxx 45 (Suppl.)' enter volume = 45 and issue = 'Suppl.'. Do not put '()' around the issue number. //[RefMan 15: IS]
Pages nvarchar (255) The page, table, or figure numbers for the reference, e.g. '23-41', '341 pp.', or '20, 22-24, 32' (for non-consecutive pages). //[RefMan 09: SP + 10: EP]
Publisher nvarchar (255) The name of the publisher (publishing company or institution, including universities or scientific societies). //[RefMan 17: PB]
PublPlace nvarchar (255) The location where the item being referenced was published, such as a city and state. //[RefMan 16: CY, CP]
Edition smallint Number of the edition of a book. Use only positive integer numbers. //[RefMan 12: VL pro parte, compare Volume!]
DateYear2 smallint Year of a secondary date, esp. the true publ. date where relevant for nomenclatural priority. Only numbers are allowed and the year must be entered with 4 digits ('1998', not '98'). //[RefMan 28: Y2 pro parte]
DateMonth2 smallint Optional: The month of a secondary date. //[RefMan 28: Y2 pro parte]
DateDay2 smallint Optional: The day of a secondary date. //[RefMan 28: Y2 pro parte]
DateSuppl2 nvarchar (255) Optional: A date supplement a secondary date, like 'approx.', a season ('Summer'), a quarter ('1st Quarter'), or any other information regarding the secondary date. //[RefMan 28: Y2 pro parte]
ISSN_ISBN nvarchar (18) The 'International Standard Serial Number' or 'International Standard Book Number'. Optional information; use is recommended only for publications that are otherwise difficult to order. //[RefMan 26: SN]
Miscellaneous1 nvarchar (255) Various reference type dependent information; e.g. the total number of volumes for books //[RefMan 29: M1]
Miscellaneous2 nvarchar (255) Various reference type dependent information //[RefMan 30: M2]
Miscellaneous3 nvarchar (255) Various reference type dependent information //[RefMan 31: M3]
UserDef1 nvarchar (MAX) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 18: U1]
UserDef2 nvarchar (MAX) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 19: U2]
UserDef3 nvarchar (MAX) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 20: U3]
UserDef4 nvarchar (MAX) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 21: U4]
UserDef5 nvarchar (MAX) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 22: U5]
WebLinks nvarchar (MAX) One or several URLs; use the semicolon as separator (http://www...). A URL may point to a local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 33: UR]
LinkToPDF nvarchar (MAX) One or several URLs; use the semicolon as separator (http://www...). A URL may point to a local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 34: L1]
LinkToFullText nvarchar (MAX) One or several URLs; use the semicolon as separator (http://www...). A URL may point to a local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 35: L2]
RelatedLinks nvarchar (MAX) One or several URLs; use the semicolon as separator (http://www...). A URL may point to a local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 36: L3]
LinkToImages nvarchar (MAX) One or several URLs; use the semicolon as separator (http://www...). A URL may point to a local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 37: L4]
SourceRefID int * Independent publication (e.g. an edited book) in which a dependent publication was published. Refers to the ID code of a reference already entered in this system. Provided as an alternative to ref. manager's denormalized storage!
Language nvarchar (25) * Language of the article/book, as ISO 2 letter codes. //[RefMan: not supported!]
DefaultValue: 'UNK'
DuplicateCheck_Cache nvarchar (255) * A system generated string (typically Au. 1-4/Yr./Jour./Vol./first page) that is assumed to be unique. Use 'DuplicateOverride' to override if two reference titles are falsely identified as duplicates. //[RefMan: not applicable]
DuplicateOverride bit * A number to manually override automatic duplicate check, enter a number 1-255 if the system claims that non-duplicate entries are duplicates. //[RefMan: not applicable]
DefaultValue: (0)
ReplaceWithRefID int * Old RefIDs are maintained to provide stable object links. Instead of direct deletes, users may select a reference to be the valid one, into which all related information (keywords, markers) is merged.
Problem nvarchar (4000) * A problem that occurred during data editing within the application. Typically the entries here should later be deleted after help has been obtained. Do not enter scientific or bibliographic problems here; use Notes for such permanent problems!
ProblemUpdatedBy int * Operator who entered the problem text
ProblemUpdatedWhen datetime * Date and time when problem was recorded
CitationText nvarchar (1000) * Full text of a citation that describes the current reference. Use if ref. is only known as a citation in the bibliography of another publication, or if imported from unstructure data source. Empty if reference is transcribed from original publication!
CitationFrom nvarchar (255) * Description of publication, Only known as citation from bibliography of another publication given here (as ID code or author/year description); Empty if transcribed from original publication!
ImportedFrom nvarchar (80) * If imported from a reference database (esp. a commercial one): The name of the database system or provider; otherwise empty. This information is important to prevent copyright violations!
ImportedID nvarchar (50) * If imported from a reference database (esp. a commercial one): The ID identifying the record in that database; otherwise empty. Requires ImportedFrom //[RefMan: not supported!; Internal management attribute]
PlausibilityCheckedBy int * Person responsible for a first plausibility or consistency check. User and Date are automatically filled if 'Original check' performed directly. //[RefMan: not applicable. Internal mgmt.attribute]
PlausibilityCheckedWhen datetime * Date and time when plausibility/consistency was checked (i.e. data entry rules and spelling errors checked, no comparison with original publication) //[RefMan: not applicable. Internal mgmt.attribute]
OriginalComparedBy int * Name of user responsible for comparison of ReferenceTitle record with original publication //[RefMan: not applicable. Internal mgmt.attribute]
OriginalComparedWhen datetime * Date and time when entry was compared with the original publication (important esp. when ReferenceTitle was entered from secondary ReferenceTitle list) //[RefMan: not applicable. Internal mgmt.attribute]
DateFrom_Cache datetime Calculated field, based on DateYear, DateMonth, DateDay, where missing information is replaced with earliest possible value (e.g. "1999" results in 1.1.1999)
DateTo_Cache datetime Calculated field, based on DateYear, DateMonth, DateDay, where missing information is replaced with latest possible value (e.g. "1999" results in 31.12.1999)
Date2From_Cache datetime Calculated field, based on Date2Year, Date2Month, Date2Day, where missing information is replaced with earliest possible value (e.g. "1999" results in 1.1.1999)
Date2To_Cache datetime Calculated field, based on Date2Year, Date2Month, Date2Day, where missing information is replaced with latest possible value (e.g. "1999" results in 31.12.1999)
Responsible int * Person responsible for entering this reference into the data collection
DefaultValue: (-1)
SysRowVersion timestamp (System generated value used by SQL Server to optimize data updates; do not confuse this with a date/time value!)
SysRecordVersion int (Under trigger control; number automatically increased with every record update, enabling manual version tracking)
DefaultValue: (0)
ParentRefID int Refers to the RefID of the superior reference



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


Project related tables

The access to the data is managed via projects where each project of DiversityReferences may be linked to a project from DiversityProjects. Every entry of table ReferenceTitle is assigned to the related projects by table ReferenceProject. DiversityReferences contains the tables UserProxy, ProjectUser and ProjectProxy to allow an independent administration of the basic functions related to projects and users.

References Project_2_1_0_IMG

Table: ProjectProxy

Column Data type Description
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)
Project nvarchar (50) The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects)
ProjectURI varchar (255) The URI of the project, e.g. as provided by the module DiversityProjects.


Table: ProjectUser

Column Data type Description
LoginName nvarchar (50) A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)


Table: UserProxy

Column Data type Description
LoginName nvarchar (50) A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..
CombinedNameCache nvarchar (255) The short name of the user, e.g. P. Smith
UserURI varchar (255) URI of a user in a remote module, e.g. refering to UserInfo.UserID in database DiversityUsers
Queries xml (MAX) Queries created by the user
CurrentProjectID int The current project selected by the user
AgentURI varchar (255) The link of the dataset to the module DiversityAgents
ID int The ID for the user. Replacement of wbCurrentUserID retrieved from DiversityUsers
PrivacyConsent bit If the user consents the storage of his user name in the database
PrivacyConsentDate datetime The time and date when the user consented or refused the storage of his user name in the database
UserID int  



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