DiversityAgents

Model 2.1.24

(10 October, 2024)
Authors M. Weiss, A. Grunz, G. Hagedorn, D. Triebel, S. Seifert
License CC BY-ND 3.0
Suggested citation M. Weiss, A. Grunz, G. Hagedorn, D. Triebel & S. Seifert (2024). DiversityAgents information model (version 2.1.24). http://www.diversityworkbench.net/Portal/DiversityAgentsModel_2.1.24
Notes The model is implemented on MS SQL Server and the data types are also MS SQL Server specific

Introduction:

DiversityAgents is the module for the administration of agents within the Diversity Workbench. Agents can be persons, organisations, institutions or departments and DiversityAgents allows the management of all sorts of information on those agents like details about names and addresses, allows the modelling of hierarchies in institutions or the relations between agents. It is manly used in Diversity Workbench for detailed information about collectors, editors or observers of specimens or collections but is also used in the access management in Diversity Workbench. But DiversityAgents keeps only data directly connected with the handling of agents and provides this information for other modules like DiversityCollection and DiversityTaxonNames. Data of other realms like e.g. projects or general references are handled in separate modules. DiversityAgents can be used as a stand-alone application.

  • Agent is the central entity, containing the information directly related to the agent.
  • AgentProject keeps the relation to the projects. Each agent may be included in several projects.
  • AgentImage keeps the images for an agent.
  • AgentContactInformation is the central entity, containing the contact informations for the Agent.
  • AgentRelation keeps the relations between the agents.
  • AgentReference keeps the references containing informations about the agent.

Information model

The following sections define the information model used in DiversityAgents. First the ER-diagram is presented to give an overview over the relations between the entities/tables and views. Then the structure is separately documented for each table with textual information which attributes are stored in which column.

ER-Diagram

Overview over all entities and relations used in the database model

ER-Diagram

TABLES

Table Agent

The main table with the data of the agent

Column Data type Description Nullable Relation
AgentID int Unique ID for the Agent (= Primary key) NO -
AgentParentID int The AgentID of the superior agent if agents are organized within a hierarchy YES Refers to table Agent
AgentName nvarchar (200) The whole name of the agent as shown e.g. for selection in a user interface. For persons this entry will be generated as follows: LastName, FirstNames, AgentTitle. Default value: '' NO -
Version smallint The version of an agent record (revision number, internally filled by system). Default value: (1) NO -
AgentTitle nvarchar (50) The title of the agent, e.g. Dr., Prof. YES Refers to table AgentTitle_Enum
GivenName nvarchar (255) The first names of the agent (if a person) or the name of e.g. an institution YES -
GivenNamePostfix nvarchar (50) Variable part of name, correctly placed at end of given names YES -
InheritedNamePrefix nvarchar (50) Variable part of name, correctly placed at the beginning of the inherited names YES -
InheritedName nvarchar (255) The last names of the agent (if a person) YES -
InheritedNamePostfix nvarchar (50) Additions after inherited name, like generation (Jr., III.) or names of religious orders YES -
Abbreviation nvarchar (50) Abbreviation of the agent YES -
AgentType nvarchar (50) The type of the agent, e.g. person, company. Default value: N’person' YES Refers to table AgentType_Enum
AgentRole nvarchar (255) The role of an agent esp. a person within an organization. e.g. “Database Administrator” or “Curator” YES -
AgentGender nvarchar (50) The gender of the agent YES Refers to table AgentGender_Enum
Description nvarchar (1000) A description of the agent YES -
OriginalSpelling nvarchar (200) Name as originally written in e.g. chinese or cyrillic letters YES -
Notes nvarchar (MAX) Notes about the agent YES -
ValidFromDate datetime The date of the begin of the existence of the agent, e.g. the birthday of a person or the founding of an institution, calculated from ValidFromDay, - Month and -Year YES -
ValidFromDay tinyint The day of the begin of the existence of the agent YES -
ValidFromMonth tinyint The month of the begin of the existence of the agent YES -
ValidFromYear smallint The year of the begin of the existence of the agent YES -
ValidUntilDate datetime The date of the end of the exsistence of the agent, e.g. death of a person or closing of an institute, calculated from ValidUntilDay, - Month and -Year YES -
ValidUntilDay tinyint The day of the end of the existence of the agent YES -
ValidUntilMonth tinyint The month of the end of the existence of the agent YES -
ValidUntilYear smallint The year of the end of the existence of the agent YES -
ValidDateSupplement nvarchar (255) Verbal or additional date information, e.g. ’end of summer 1985’, ‘first quarter’. The time of the valid date if necessary. YES -
PlaceOfBirth nvarchar (500) The place (e.g. a city) where a person was born or an institution was founded YES -
PlaceOfDeath nvarchar (500) The place (e.g. a city) where the person died YES -
SynonymToAgentID int The AgentID of the agent which was selected as a replacement for the current agent, e.g. if to equal datasets were imported from different sources YES Refers to table Agent
SynonymisationType nvarchar (50) The type of the synonymisation like “replaced with”, “synonym to” YES Refers to table AgentSynonymisationType_Enum
RevisionLevel nvarchar (50) The level of the revision of the agent, e.g. ‘unchecked’, ‘final revision’. Default value: N’unchecked’ YES Refers to table RevisionLevel_Enum
DataWithholdingReason nvarchar (255) If the dataset is withhold, the reason for withholding the data, otherwise null YES -
IgnoreButKeepForReference bit If true, the record is ignored for all purposes of evaluation (because contradicted). It is kept only to maintain the cited reference. If no reference is given, it may be deleted instead. Default value: (0) YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -
ContentLanguage nvarchar (50) The language of the content YES Refers to table LanguageCode_Enum

Depending on:

  • AgentGender_Enum
  • AgentSynonymisationType_Enum
  • AgentTitle_Enum
  • AgentType_Enum
  • LanguageCode_Enum
  • RevisionLevel_Enum

trgInsAgent

Setting the value of column AgentName according to standard derived from content of given values in columns InheritedNamePrefix, Inheritedname, GivenName, GivenNamePostfix, InheritedNamePostfix and AgentTitle

Table AgentContactInformation

The contact information resp. addresses of the agents

Column Data type Description Nullable Relation
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key) NO Refers to table Agent
DisplayOrder tinyint Display order of records in user interface. DisplayOrder 1 corresponds to the preferred address (= part of primary key) NO -
AddressType nvarchar (50) Type of the adress, e.g. private YES Refers to table AddressType_Enum
Country nvarchar (255) Country of the address YES -
City nvarchar (255) City of the address YES -
PostalCode nvarchar (50) ZIP or postcode of the address (usually output before or after the city) YES -
Streetaddress nvarchar (255) Usually street name and number, but may also contain post office box YES -
Address nvarchar (255) Free text postal address of the agent YES -
Telephone nvarchar (50) Phone number, including area code YES -
CellularPhone nvarchar (50) The number of a mobile telephone device of the agent YES -
Telefax nvarchar (50) Fax number, including area code YES -
Email nvarchar (255) E-mail address of the agent YES -
URI nvarchar (255) URI pointing to a homepage containing further information YES -
Notes nvarchar (MAX) Notes about this address YES -
ValidFrom datetime The date when this address became valid as date according to ISO 8601 YES -
ValidUntil datetime The date of the expiration of the validity of this address as date according to ISO 8601 YES -
DataWithholdingReason nvarchar (255) If the dataset is withhold, the reason for withholding the data, otherwise null. Default value: N’Withhold by default’ YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -

Depending on:

  • AddressType_Enum
  • Agent

trgInsAgentContactInformation

Table AgentDescriptor

The Descriptors for the agents

Column Data type Description Nullable Relation
AgentID int Unique ID for the Agent (foreign key + part of primary key) NO Refers to table Agent
DescriptorID int Unique ID for the descriptor, Part of PK NO -
Descriptor nvarchar (200) The Descriptor. Default value: '’ NO -
URL varchar (500) URL of the Descriptor. In case of a module related Descriptor, the link to the module entry resp. the related webservice. Default value: '' YES -
DescriptorType nvarchar (50) Type of the Descriptor as described in table AgentDescriptorType_Enum. Default value: N’Descriptor' YES Refers to table AgentDescriptorType_Enum
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. Default value: suser_sname() YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. Default value: getdate() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -
RowGUID uniqueidentifier -. Default value: newsequentialid() NO -

Depending on:

  • Agent
  • AgentDescriptorType_Enum

Table AgentExternalDatabase

The external databases from which data in the database may have been imported from

Column Data type Description Nullable Relation
ExternalDatabaseID int An ID to identify an external data collection of plant names (primary key, the ID has no meaning outside of the DiversityWorkbench system) NO -
ExternalDatabaseName nvarchar (100) The name of the data collection that has been integrated or can be linked to for further analysis YES -
ExternalDatabaseVersion nvarchar (255) The version of this data collection (either official version number, or dates when the collection was integrated) YES -
Rights nvarchar (500) A description of copyright agreements or permission to use data from the external database YES -
ExternalDatabaseAuthors nvarchar (200) The persons or institutions responsible for the external database YES -
ExternalDatabaseURI nvarchar (300) The link to the database provider resp. the external database YES -
ExternalDatabaseInstitution nvarchar (300) The institution responsible for the external database YES -
InternalNotes nvarchar (1500) Additional notes concerning this data collection YES -
ExternalAttribute_AgentID nvarchar (255) The table and field name in the external data collection to which PlantNameAnalysis.ExternalNameID refers YES -
PreferredSequence tinyint For selection in e.g. picklists: of several equal names only the name from the source with the lowest preferred sequence will be provided. YES -
Disabled bit If this source should be disabled for selection of names e.g. in picklists YES -
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. YES -

Table AgentExternalID

The ID’s of data that were imported from foreign souces

Column Data type Description Nullable Relation
AgentID int Unique ID for the Agent (foreign key + part of primary key) NO Refers to table Agent
ExternalDatabaseID int The ID of an external taxonomic data collection as defined in AgentExternalDatabase (foreign key + part of primary key). Default value: '' NO Refers to table AgentExternalDatabase
ExternalAgentURI varchar (255) The URI (e.g. LSID) of the external agent YES -
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. Default value: suser_sname() YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. Default value: getdate() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -

Depending on:

  • Agent
  • AgentExternalDatabase

Table AgentHierarchyOther

Other hierarchies of the agent

Column Data type Description Nullable Relation
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key) NO Refers to table Agent
HierarchyID int Identifier of the hierarchy, part of PK NO -
AgentParentID int The AgentID of the superior agent if agents are organized within a hierarchy NO Refers to table Agent
ValidFrom datetime The start time of the hierarchy, e.g. when a person was employed in an institution YES -
ValidUntil datetime The end of a hierarchy, e.g. when an employment ended YES -
Notes nvarchar (MAX) Notes about the other hierarchy YES -
LogCreatedWhen datetime Point in time when this data set was created. Default value: getdate() YES -
LogCreatedBy nvarchar (50) Name of the creator of this data set. Default value: suser_sname() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -
RowGUID uniqueidentifier -. Default value: newsequentialid() NO -

Depending on:

  • Agent

Table AgentIdentifier

Identifier for the agent

Column Data type Description Nullable Relation
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key) NO Refers to table Agent
Identifier nvarchar (400) Identifier for the agent, part of PK NO -
IdentifierURI varchar (500) URI of Identifier YES -
Type nvarchar (50) Type of the identifier as defined in table AgentIdentifierType_Enum YES Refers to table AgentIdentifierType_Enum
Notes nvarchar (MAX) Notes about the identifier YES -
LogCreatedWhen datetime Point in time when this data set was created. Default value: getdate() YES -
LogCreatedBy nvarchar (50) Name of the creator of this data set. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
RowGUID uniqueidentifier -. Default value: newsequentialid() NO -

Depending on:

  • Agent
  • AgentIdentifierType_Enum

Table AgentImage

The images of the agents

Column Data type Description Nullable Relation
AgentID int Unique ID for the Agent (foreign key + part of primary key) NO Refers to table Agent
URI varchar (255) The complete URI address of the image. This is only a cached value if ResourceID is available referring to the module DiversityResources NO -
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. Default value: suser_sname() YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. Default value: getdate() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -
Description nvarchar (MAX) Description of the resource YES -
Type nvarchar (50) The type of the image YES Refers to table AgentImageType_Enum
Sequence int The sequence of the image. Default value: (1) YES -
DataWithholdingReason nvarchar (255) If the dataset is withhold, the reason for withholding the data, otherwise null. Default value: N’Withhold by default' YES -

Depending on:

  • Agent
  • AgentImageType_Enum

trgInsAgentImage

Table AgentKeyword

The keywords for the agents

Column Data type Description Nullable Relation
AgentID int Unique ID for the Agent (foreign key + part of primary key) NO Refers to table Agent
Keyword nvarchar (200) The keyword NO -
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. Default value: suser_sname() YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. Default value: getdate() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -

Depending on:

  • Agent

trgInsAgentKeyword

Table AgentProject

The projects of the agents

Column Data type Description Nullable Relation
AgentID int Unique ID for the Agent (= Foreign key) NO Refers to table Agent
ProjectID int The ID of the project. Default value: (0) NO Refers to table ProjectProxy
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. Default value: suser_sname() YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. Default value: getdate() YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. Default value: suser_sname() YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. Default value: getdate() YES -

Depending on:

  • Agent
  • ProjectProxy

Table AgentReference

References containing informations about the agent

Column Data type Description Nullable Relation
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key) NO Refers to table Agent
ReferenceTitle nvarchar (255) The title of the publication where information about the agent was published. Note this is only a cached value where ReferenceURI is present NO -
ReferenceURI varchar (255) URI of reference where information about the agent, e.g. referring to the module DiversityReferences YES -
ReferenceDetails nvarchar (50) Details within the reference, e.g. pages YES -
ContainsImage tinyint If the reference contains an image of the agent YES -
ContainsReferencelist tinyint If the reference contains a publication list of the agent YES -
Notes nvarchar (255) Notes about the reference YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. YES -

Depending on:

  • Agent

trgInsAgentReference

Table AgentRelation

Relations of the agent to other agents

Column Data type Description Nullable Relation
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key) NO Refers to table Agent
RelatedAgentID int The AgentID of the related agent NO -
RelationType nvarchar (50) The type of the relation between the agents, e.g. “parent of”, “child of”, “married to” YES Refers to table AgentRelationType_Enum
Notes nvarchar (255) Notes about the relation YES -
DataWithholdingReason nvarchar (255) If the dataset is withhold, the reason for withholding the data, otherwise null YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. YES -

Depending on:

  • Agent
  • AgentRelationType_Enum

trgInsAgentRelation

Enumeration tables

Tabledesign

Column Data type Description
Code nvarchar (50) A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it.
ParentCode nvarchar (50) The code of the superior entry, if a hierarchy within the entries is necessary
ParentRelation nvarchar (50) Relation to parent entry, e.g. part of
Description nvarchar (500) Description of enumerated object, displayed in the user interface
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.
DisplayEnable bit Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box)
URL varchar (500) A link to further information about the enumerated object
InternalNotes nvarchar (500) Internal development notes about usage, definition, etc. of an enumerated object
Icon image A symbol representing this entry in the user interface
ModuleName varchar (50) If the enumerated entry is related to a DiversityWorkbench module or a related webservice, the name of the DiversityWorkbench module, e.g. DiversityAgents
RowGUID uniqueidentifier -. Default value: newsequentialid()

Table AddressType_Enum

Lookup table for the types of the addresses

Dependent tables:

  • AgentContactInformation

Content

Code Description
office office address
private private address

Table AgentDescriptorType_Enum

The type of the Descriptors

Dependent tables:

  • AgentDescriptor

Table AgentGender_Enum

Gender of the agent: female or male

Dependent tables:

  • Agent

Content

Code Description
female
male

Table AgentIdentifierType_Enum

Types of the agent identifier, e.g. ISNI

Dependent tables:

  • AgentIdentifier

Content

Code Description
ISNI International Standard Name Identifier (ISO 27729)
ORCID Open Researcher Contributor Identification
ROR Research Organization Registry

Table AgentImageType_Enum

Types of images in table AgentImage, e.g. Logo

Dependent tables:

  • AgentImage

Content

Code Description
Logo Logo
Portrait Portrait

Table AgentNameDisplayType_Enum

Display types of the names of the agents, defining e.g. the position of title, given and inherited part of the name and their abbreviation

Dependent tables:

  • ProjectProxy

Content

Code Description
Abbr The international defined standard arbbreviation; e.g. L. for Linne
G. Ii Given name. Inherited name; e.g. H. Hertel
Gg Ii Given name InheritedName; e.g. Hannes Hertel
Ii, G. Inherited name, GivenName; e.g. Hertel, H.
Ii, Gg Inherited name, GivenName; e.g. Hertel, Hannes
Tt. G. Ii Titel Given name. Inherited name; e.g. Prof. H. Hertel
Tt. Gg Ii Titel Given name Inherited name; e.g. Prof. Hannes Hertel

Table AgentRelationType_Enum

Relation types of agents, e.g. Child of

Dependent tables:

  • AgentRelation

Content

Code Description
Child of
Married to
Parent of

Table AgentSynonymisationType_Enum

Synonymisation types of agents, e.g. synonym to

Dependent tables:

  • Agent

Content

Code Description
replaced with if the agent was replaced by another entry
synonym to if the agent is synonym to another agent

Table AgentTitle_Enum

Lookup table for the titles of the agents

Dependent tables:

  • Agent

Content

Code Description
Dr.
Frater
Pater
Prof.
Prof. Dr.

Table AgentType_Enum

Lookup table for the types of the agents

Dependent tables:

  • Agent

Content

Code Description
Collection
Company
Department
Group
Institution
Person
University

Table LanguageCode_Enum

The codes for the languages

Dependent tables:

  • Agent

Content

Code Description
af Afrikaans
ar Arabisch
az Aserbaidschanisch
be Belarussisch
bg Bulgarisch
ca Katalanisch
cs Tschechisch
da Dänisch
de Deutsch
el Griechisch
en Englisch
es Spanisch
et Estnisch
eu Baskisch
fa Farsi
fi Finnisch
fo Färingisch
fr Französisch
gl Galizisch
gu Gujarati
he Hebräisch
hi Hindi
hr Kroatisch
hu Ungarisch
hy Armenisch
id Indonesisch
is Isländisch
it Italienisch
ja Japanisch
ka Georgisch
kk Kasachisch
kn Kannada
ko Koreanisch
ky Kirgisisch
lt Litauisch
lv Lettisch
mk Mazedonisch
mn Mongolisch
mr Marathi
ms Malaiisch
nl Niederländisch
no Norwegisch
pa Punjabi
pl Polnisch
pt Portugiesisch
ro Rumänisch
ru Russisch
sa Sanskrit
sk Slowakisch
sl Slowenisch
sq Albanisch
sv Schwedisch
sw Swahili
ta Tamil
te Telugu
th Thai
tr Türkisch
tt Tatarisch
uk Ukrainisch
ur Urdu
uz Usbekisch
vi Vietnamesisch
zh Chinesisch

Table RevisionLevel_Enum

Lookup table for the revision level

Dependent tables:

  • Agent

Content

Code Description
checked checked
final revision final revision
review required review required
to be deleted to be deleted
unchecked unchecked