DiversityAgentsModel 2.1.9

From Diversity Workbench
(Redirected from DiversityAgentsModel v2.1.9)
Jump to: navigation, search

DiversityAgents Information Model (version 2.1.9, 12 July, 2016)

Authors M. Weiss, G. Hagedorn & D. Triebel 2016
License 80x15.png
Suggested citation M. Weiss, G. Hagedorn & D. Triebel (2016). DiversityAgents information model (version 2.1.9). http://www.diversityworkbench.net/Portal/DiversityAgentsModel_2.1.9
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.



dwbA Database Scheme

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

ER

Some “housekeeping” fields and tables for the documentation when and by whom the data were inserted or changed are not shown in the overview or the detail diagrams.

A short introduction:

  • “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 a 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.

Entities ending with “_Enum” (“AddressType_Enum”, “AgentNameDisplayType_Enum” etc.) contain enumerated values or strings.


Table: Agent

The main table with the data of the agent

Column Data type Description Ind.
AgentID int Unique ID for the Agent (= Primary key) U
AgentParentID int The AgentID of the superior agent if agents are organized within a hierarchy   -  
AgentName nvarchar (200) The whole name of the agent as shown e.g. for selection in an user interface. For persons this entry will be generated as follows: LastName, FirstNames, AgentTitle  
Version smallint The version of a agent record (revision number, internally filled by system)
DefaultValue: (1)
  -  
AgentTitle nvarchar (50) The title of the agent, e.g. Dr., Prof.   -  
GivenName nvarchar (255) The first names of the agent (if a person) or the name of e.g. an institution   -  
GivenNamePostfix nvarchar (50) Variable part of name, correctly placed at end of given names   -  
InheritedNamePrefix nvarchar (50) Variable part of name, correctly placed at the beginning of the inherited names   -  
InheritedName nvarchar (255) The last names of the agent (if a person)   -  
InheritedNamePostfix nvarchar (50) Additions after inherited name, like generation (Jr., III.) or names of religious orders   -  
Abbreviation nvarchar (50) Abbreviation of the agent   -  
AgentType nvarchar (50) The type of the agent, e.g. person, company
DefaultValue: N'person'
  -  
AgentRole nvarchar (255) The role of an agent esp. a person within an organization. e.g. "Database Administrator" or "Curator"   -  
AgentGender nvarchar (50)     -  
Description nvarchar (1000) A description of the agent   -  
OriginalSpelling nvarchar (200) Name as originally written in e.g. chinese or cyrillic letters   -  
Notes nvarchar (MAX) Notes about the agent   -  
ValidFromDate datetime The date of the begin of the exsistence of the agent, e.g. the birthday of a person or the founding of an institution, calculated from ValidFromDay, - Month and -Year   -  
ValidFromDay tinyint The day of the begin of the exsistence of the agent   -  
ValidFromMonth tinyint The month of the begin of the exsistence of the agent   -  
ValidFromYear smallint The year of the begin of the exsistence of the agent   -  
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   -  
ValidUntilDay tinyint The day of the end of the exsistence of the agent   -  
ValidUntilMonth tinyint The month of the end of the exsistence of the agent   -  
ValidUntilYear smallint The year of the end of the exsistence of the agent   -  
ValidDateSupplement nvarchar (255) Verbal or additional date information, e.g. 'end of summer 1985', 'first quarter'. The time of the valid date if necessary.   -  
PlaceOfBirth nvarchar (500) The place (e.g. a city) where a person was born or an institution was founded   -  
PlaceOfDeath nvarchar (500) The place (e.g.a city) where the person died   -  
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   -  
SynonymisationType nvarchar (50) The type of the synonymisation like "replaced with", "synonym to"   -  
RevisionLevel nvarchar (50) The level of the revision of the agent, e.g. 'unchecked', 'final revision'
DefaultValue: N'unchecked'
  -  
DataWithholdingReason nvarchar (255) If the dataset is withhold, the reason for withholding the data, otherwise null   -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  


Table: AgentContactInformation

The contact information resp. addresses of the agents

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


Table: AgentExternalDatabase

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

Column Data type Description Ind.
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) U
ExternalDatabaseName nvarchar (100) The name of the data collection that has been integrated or can be linked to for further analysis   -  
ExternalDatabaseVersion nvarchar (255) The version of this data collection (either official version number, or dates when the collection was integrated)   -  
Rights nvarchar (500) A description of copyright agreements or permission to use data from the external database   -  
ExternalDatabaseAuthors nvarchar (200) The persons or institutions responsible for the external database   -  
ExternalDatabaseURI nvarchar (300) The link to the database provider resp. the external database   -  
ExternalDatabaseInstitution nvarchar (300) The institution responsible for the external database   -  
InternalNotes nvarchar (1500) Additional notes concerning this data collection   -  
ExternalAttribute_AgentID nvarchar (255) The table and field name in the external data collection to which PlantNameAnalysis.ExternalNameID refers   -  
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.   -  
Disabled bit If this source should be disabled for selection of names e.g. in picklists   -  


Table: AgentExternalID

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

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


Table: AgentImage

The images of the agents

Column Data type Description Ind.
AgentID int Unique ID for the Agent (foreign key + part of primary key) U
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 U
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: suser_sname()
  -  
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database.
DefaultValue: getdate()
  -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: suser_sname()
  -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
  -  


Table: AgentKeyword

The keywords for the agents

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


Table: AgentProject

The projects of the agents

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


Table: AgentReference

References containing informations about the agent

Column Data type Description Ind.
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key) U
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 U
ReferenceURI varchar (255) URI of reference where information about the agent, e.g. referring to the module DiversityReferences   -  
ReferenceDetails nvarchar (50) Details within the reference, e.g. pages   -  
ContainsImage tinyint If the reference contains an image of the agent   -  
ContainsReferencelist tinyint If the reference contains a publication list of the agent   -  
Notes nvarchar (255) Notes about the reference   -  
LogUpdatedBy nvarchar (50) Name of user who last updated the data.   -  
LogUpdatedWhen smalldatetime Date and time when the data were last updated.   -  


Table: AgentRelation

Relations of the agent to other agents

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


Table: ProjectProxy

The projects - refers to database DiversityProjects

Column Data type Description Ind.
ProjectID int The ID of the project, Primary key U
Project nvarchar (50) The name of the project as stored in the module DiversityProjects   -  
ProjectURI varchar (255) The URI of the project, e.g. as provided by the module DiversityProjects.   -  
AgentNameDisplayType nvarchar (50) The display type of the name of the agent   -  


Table: ProjectUser

The projects to which users have access to

Column Data type Description Ind.
LoginName nvarchar (50) The login name of the user U
ProjectID int The ID of the default project of the user as stored in table ProjectProxy U


Table: UserProxy

The user logins - refers to database DiversityUsers

Column Data type Description Ind.
LoginName nvarchar (50) The login name of the user, Primary key U
CombinedNameCache nvarchar (50) A combined name of the user, created on the base of an entry in the module DiversityUsers   -  
UserURI varchar (255) Refers to UserInfo.UserID in database DiversityUsers   -  
ProjectID int The ID of the default project of the user as stored in table ProjectProxy   -  
Queries xml (MAX) Queries created by the user   -  
AgentURI varchar (255) A link to a DiversityAgents module where further informations about the user is available.   -  



Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined I: The field is indexed to enable faster searching. U: unique index