ReferencesModel v2.0

From Diversity Workbench
Jump to: navigation, search

DiversityReferences Information Model version 2.0 (13. September 2006)

Authors G. Hagedorn, M. Weiss & A. Kohlbecker 2006
License 80x15.png
Suggested citation G. Hagedorn, M. Weiss & A. Kohlbecker (2006). DiversityReferences information model (version 2.0). http://www.diversityworkbench.net/Portal/ReferencesModel_v2.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.

Introduction

This document contains entity-relationship diagrams and a complete list of all entities and relations used in the information model of this DiversityWorkbench component. It is primarily intended as a technical reference. It can also be helpful, however, in understanding the scope and usage of certain attributes. When you have a question like ‘where does this information fit in’, reading the attribute description under the section ‘Entities’ may help you.

Detailed discussion of the ReferenceManager (tm) model and its relation to the DiversityReferences model can be found under: ReferencesModelComparisonWithReferenceManager11]]


ER Diagrams

The following diagrams show all entities except those entities that are part of the general ‘Workbench’ interface and documentation infrastructure.

Overview over all entities and relations used in the database model

References Model v 2.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.

For ReferencePeriodical and ReferencePeriodicalSynonym see below.

Selected background notes on the model

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.


Descriptions of the tables and the columns

The following detailed information contain for each field a short text. They are currently provided as external html tables (should be migrated into the Wiki).

Note: some field descriptions start with a “*”; this is a temporary marker that these fields cannot be imported/exported to ReferenceManager and are special to the handling of references in this model.

Ref_AvailabilityReprintStatus_Enum Ref_RelatorRole_Enum Ref_Type_Enum ReferenceAvailability ReferenceChanges ReferenceDescriptor ReferenceDescriptorElement ReferenceDescriptorElementRelation ReferenceNote ReferencePeriodical ReferencePeriodicalSynonym ReferencePrivateDescriptor ReferencePrivateNote ReferenceRelator ReferenceTitle


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]
LogCreatedBy int Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: [dbo].[wbCurrentUserID]()
LogCreatedWhen datetime Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: getdate()

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]
LogCreatedBy int * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: [dbo].[wbCurrentUserID]()
LogCreatedWhen datetime * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: getdate()

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.
LogCreatedBy int Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: [dbo].[wbCurrentUserID]()
LogCreatedWhen datetime Date and time when record was first entered (typed or imported) into this system.
DefaultValue: getdate()

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: ReferenceRelator

Reference authors, book editors, or series editors.

Column Data type Description
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!]
Role nvarchar (3) Relator codes from MARC; Reference manager supports only aut = primary author, 2 = secondary author/editor, 3 = series editor. //[RefMan implicit]
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]
RefID int Refers to the ID code of the main ReferenceTitle table (= foreign key)
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))

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 (-1) 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 (-1) 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 (-1) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 18: U1]
UserDef2 nvarchar (-1) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 19: U2]
UserDef3 nvarchar (-1) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 20: U3]
UserDef4 nvarchar (-1) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 21: U4]
UserDef5 nvarchar (-1) User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 22: U5]
WebLinks nvarchar (-1) 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 (-1) 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 (-1) 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 (-1) 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 (-1) 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: [dbo].[wbCurrentUserID]()
LogCreatedBy int * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: [dbo].[wbCurrentUserID]()
LogCreatedWhen datetime * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]
DefaultValue: getdate()
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)


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


Known problems

  • Currently none.

Changes from previous version (DiversityReferences 1.x to 2.0)

Due to general changes (see Workbench Object Linking), a new table WorkbenchUriMappingPattern was introduced, the signature fields removed, and the URI system reworked.

All DiversityWorkbench applications try to avoid a semantic difference between an empty string and the Null value. Whereas version 1 preferred the Null value, version 2 due to the implementation environment prefers the empty string. It seems more difficult in SQL-Server than in JET databases to avoid the use of empty strings (““) in addition to Null. Many existing editing tools insert an empty string into a field when the field content is deleted.

Throughout the information model the alphanumeric RefID field (primary key of the central table) has been changed to a numeric ID field to bring the model into line with other DiversityWorkbench models. The 20-character long alphanumeric RefID field in DiversityReferences version 1 was modeled after the ReferenceManager model. However, in practice all ReferenceManager datasets encountered so far use only positive numeric IDs. To support data migration from old DiversityReferences databases, the table “Ref_AlphanumericRefIDsFromVers1” may be used to store the old IDs.


In the main entity ”ReferenceTitle” sevaral fields have been changed:

  • “ReferenceTitle.RefDescription” has been renamed to “RefDescription_Cache” (filled by a database trigger)
  • “ReferenceTitle.DuplicateCheck” has been renamed to “DuplicateCheck_Cache” (filled by a database trigger)
  • “ReferenceTitle.DuplicateOverride” has been changed from integer to Boolean value (if set true, reference is no longer included in duplicate check)
  • The fields DateFrom_Cache, DateTo_Cache (based on DateYear, DateMonth, DateDay) and Date2From_Cache, Date2To_Cache (based on Date2Year, Date2Month, Date2Day) are added as calculated fields, filled by trigger.
  • ReferenceManager 11 now supports several URL-related fields (see comparison):
33 UR Web/URLs String field, 32K
34 L1 Link to PDF String field, 32K
35 L2 Link to Full Text String field, 32K
36 L3 Related Links String field, 32K
37 L4 Images String field, 32K

Of these, only 33/UR was already present in ReferenceManager 9, on which DiversityReferences v. 1 was based. Therefore, the memo/longtext field “ReferenceTitle.URL” was renamed to ”ReferenceTitle.WebLinks” and the new fields ”ReferenceTitle.LinkToPDF”, ”ReferenceTitle.LinkToFullText”, ”ReferenceTitle.RelatedLinks”, and ”ReferenceTitle.LinkToImages” are introduced. ReferenceManager remains vague about the exact semantics of these fields, leaving this for the user to decide. Especially, the purpose of the catch-all URL/web links field and the more specific ones remains unclear.

Each URL field may contain multiple links; the semicolon is defined as separator (if present in a URL it must therefore be escaped).

In the future it may be desirable to change the current Memo-approach to a separate table with one URL per record, and a role or category field representing the relation of the URL to the reference record.


Changes to dependend entities:

In the entity ”ReferenceAvailability” the autonumber/identity field ”AvailabilityID” was renamed to ”RecordID”

The entity ”ReferenceAuthor” (containing in addition to authors also editors and series editors) has been renamed to ”ReferenceRelator”. The field ReferenceAuthor.AuthorType (containing 1 for Reference Manager: primary author, 2 for Reference Manager: secondary author/editor, 3 for Reference Manager: series editor) has been renamed to “ReferenceRelator.Role” with values derived from http://www.loc.gov/marc.relators/. The role values are restricted to those defined in the new entity “Ref_RelatorRole_Enum”. If it is desired to limit the ability of DiversityReference to those supported by Reference Manager, only aut = primary author, edt = secondary author/editor, series editor may be used. For a complete list see supported relator roles.

Within ”ReferenceRelator”, several fields were changed: Author to Relator, AuthorSequence to Sequence, AuthorAddress to Address.

The entity ”ReferenceKeyword” has been renamed to ”ReferenceDescriptor” and its functionality improved so that keywords may be general keywords as well as descriptors from more specific categories, e.g. taxon names or geographical names.

The entity ”ReferenceUserMarker” has been renamed to ”ReferencePrivateDescriptor” and modeled highly similar to “ReferenceDescriptor”, with the addition of an additional required field “PrivateTo”, storing the user to which the information is private. When updating data, “ReferenceUserMarker.Marker” becomes “ReferencePrivateDescriptor.Content”, and “ReferenceUserMarker.Responsible” becomes “ReferencePrivateDescriptor.PrivateTo”. The DescriptorElement may be set to general keyword (the default).

To avoid a recurring confusion between the ReferenceManager concept of “user-defined fields” and the DiversityReferences concept of fields editable and visible only by users, the following changes have been made:

  • The entity ”ReferenceUserNote” has been renamed to ”ReferencePrivateNote”.
  • As noted above, the entity ”ReferenceUserMarker” has been renamed to ”ReferencePrivateDescriptor”.
  • The entity ”ReferenceUserDefined” has been renamed to ”Ref_ReferenceManagerRedefinableFields”.
  • Finally - to stress the similarity between the public and private fields - the entity ”ReferenceAbstract” was renamed to ”ReferenceNote”.

Field changes in these entities: The field ”ReferenceAbstract.Abstract” was renamed to ”ReferenceNote.Content”; the field ”ReferenceUserNote.Notes” was renamed to ”ReferencePrivateNote.Content”. The field ”ReferenceUserNote.Responsible” was renamed to ”ReferencePrivateNote.PrivateTo”. The fields ”ReferencePrivateNote.Log_CreatedBy/When” and the field ”ReferenceAbstract.Shareable” were dropped. An abstract or a note is now either generally visible (in “ReferenceNote”) or visible only to its creator (in “ReferencePrivateNote”).

”ReferencePeriodicalSyn” is now spelled out as “ReferencePeriodicalSynonym”.

The implementation entities ReferenceWordIdx1/2 (providing full-text word indices) are renamed to “Ref_WordIdx1”/”Ref_WordIdx2”.


Enumerations:

The entity ”ReferenceType” has been renamed to ”Ref_Type_Enum”. Within this entity, the original field “Description” used to contain only short labels. To bring the enumeration into line with other Workbench models, It has been renamed to “Label”, and an additional “Description” field added.


The entity ”Ref_RelatorRole_Enum” has been introduced (compare ”ReferenceRelator” above).

The entity ”Ref_AvailabilityReprintStatus_Enum” has been introduced.


Deleted entities:

The entity ”ReferenceCollection” has been deleted. The collection concept was intended in the previous version to enable the combination of multiple reference manager “databases” into a single DiversityReferences database. When updating data from a previous version of DiversityReferences, the data should be converted into “ReferenceDescriptor” with a specific high-level “Topic” or “SubjectHeading” or “Collection” descriptor category.

The entity ”ReferenceAnonymousNote” has been deleted. It was intended to allow feedback from anonymous internet users about the content of a reference (pointing out errors, etc.). However, it has become clear that such a mechanism is difficult to control (spamming) and that it should rather be a component of its own, providing feedback option on various DiversityWorkbench objects (taxa, specimens, resources, etc.).

The entity ”ReferenceUserDefined” has been deleted, its content (five redefinable fields used in the reference manager software) has been placed directly in ReferenceTitle.

”ReferenceDevelopmentDocumentation” has been wiped of information relating to the development of DiversityReferences 1.0. The information has been moved to the following Wiki page: References Development Documentation v1.0.

”ReferenceImportTags”, containing information for a data-driven ReferenceManager import/export routine, has been deleted.


Intended data visiblity rules

Although not strictly part of the information model, it may be useful to discuss the data visibility and editing security rules an application may want to implement.

1. The information in entities starting with “Ref_” or “Workbench” is considered system information and may be changed only under special circumstances. It is publicly readable and not generally editable.

2. The information in Title, Relators(especially authors, editors) , Descriptor (especially keywords) and Note (especially abstracts) is publicly readable and editable to all members of the user group after login. Note that the name of the five ReferenceManager “user-defined fields” is somewhat misleading: they are shared among all users, as they would be in ReferenceManager.

3. The information in Availability is not public, readable by all members of the user group after login. We consider it desirable to share information that a publication is “in file” among this group. Existing availability records are editable only to the creator and to administrators.

4. Information in PrivateNote and PrivateDescriptor (“user markers”) is visible and editable only to its creators.