DiversityAgentsModel v2.1

From Workbench
Jump to: navigation, search

DiversityAgents Information Model (version 2.1, 22. October 2009)

Authors M. Weiss, G. Hagedorn & D. Triebel 2009
License 80x15.png
Suggested citation M. Weiss, G. Hagedorn & D. Triebel (2009). DiversityAgents information model (version 2.1). http://www.diversityworkbench.net/Portal/DiversityAgentsModel_v2.1.
Notes The models currently reside in MS SQL Server, so knowledge of some SQL Server ER-diagram conventions will be helpful.

Besides the screen shots below, a Microsoft T-SQL-Script for the generation of the tables is provided.


ER Diagrams

Overview over all entities and relations used in the database model

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.

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
AgentID int Unique ID for the Agent (= Primary key)
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
InheritedName nvarchar (255) The last names of the agent (if a person)
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"
Description nvarchar (1000) A description of the agent
Notes nvarchar (500) 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.
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'
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
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
AgentID int Refers to the ID of Agent (= Foreign key and part of primary key)
DisplayOrder tinyint Display order of records in user interface. DisplayOrder 1 corresponds to the preferred address (= part of primary key)
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 (255) 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
LogUpdatedBy nvarchar (50) Name of user who last updated the data.
DefaultValue: user_name()
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
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)
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
AgentID int Unique ID for the Agent (foreign key + part of primary key)
ExternalDatabaseID int The ID of an external taxonomic data collection as defined in AgentExternalDatabase (foreign key + part of primary key)
ExternalAgentURI varchar (255) The URI (e.g. LSID) of the external agent

Table: AgentImage

The images of the agents

Column Data type Description
AgentID int Unique ID for the Agent (foreign key + part of primary key)
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

Table: AgentKeyword

The keywords for the agents

Column Data type Description
AgentID int Unique ID for the Agent (foreign key + part of primary key)
Keyword nvarchar (200) The keyword

Table: AgentProject

The projects of the agents

Column Data type Description
AgentID int Unique ID for the Agent (= Foreign key)
ProjectID int The ID of the project
DefaultValue: 0

Table: ProjectProxy

The projects - refers to database DiversityProjects

Column Data type Description
ProjectID int The ID of the project, Primary key
Project nvarchar (50) The name of the project as stored in 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
LoginName nvarchar (50) The login name of the user
ProjectID int The ID of the default project of the user as stored in table ProjectProxy

Table: UserProxy

The user logins - refers to database DiversityUsers

Column Data type Description
LoginName nvarchar (50) The login name of the user, Primary key
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

Table: *_Enum

Column Data type Description
Code nvarchar (50)  
Description nvarchar (500)  
DisplayText nvarchar (50)  
DisplayOrder smallint  
DisplayEnable bit  
InternalNotes nvarchar (500)  
ParentCode nvarchar (50) The code of the superior entry, if a hierarchy within the entries is necessary


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