View source for DiversityAgentsModel v2.1

=DiversityAgents Information Model (version 2.1, 22. October 2009)=
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#F5F5F5;"
|Authors
|M. Weiss, G. Hagedorn & D. Triebel 2009 
|-
|-style="text-align:left; background:# FFFFFF;"
|License
|[http://creativecommons.org/licenses/by-nd/3.0/ http://i.creativecommons.org/l/by-nd/3.0/80x15.png]
|-
|-style="text-align:left; background:#F5F5F5;"
|Suggested citation
|M. Weiss, G. Hagedorn & D. Triebel (2009). DiversityAgents information model (version 2.1). http://www.diversityworkbench.net/Portal/DiversityAgentsModel_v2.1.
|-
|-style="text-align:left; background:#FFFFFF;"
|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 [[Media:DiversityAgents_0201.txt|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===

[[Image:ER_DiversityAgents_0201.gif|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
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>AgentID</u>
|int
|Unique ID for the Agent  (= Primary key)
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|Version
|smallint
|The version of a agent record (revision number, internally filled by system)<br />''DefaultValue: (1)''
|-
|AgentTitle
|nvarchar (50)
|The title of the agent, e.g. Dr., Prof.
|-style="background:#F5F5F5;"
|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)
|-style="background:#F5F5F5;"
|Abbreviation
|nvarchar (50)
|Abbreviation of the agent
|-
|AgentType
|nvarchar (50)
|The type of the agent, e.g. person, company<br />''DefaultValue: N'person'''
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|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.
|-style="background:#F5F5F5;"
|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"
|-style="background:#F5F5F5;"
|RevisionLevel
|nvarchar (50)
|The level of the revision of the agent, e.g. 'unchecked', 'final revision'<br />''DefaultValue: N'unchecked'''
|-
|LogUpdatedBy
|nvarchar (50)
|Name of user who last updated the data.<br />''DefaultValue: user_name()''
|-style="background:#F5F5F5;"
|LogUpdatedWhen
|smalldatetime
|Date and time when the data were last updated.<br />''DefaultValue: getdate()''
|}
===Table: AgentContactInformation===
The contact information resp. addresses of the agents
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>AgentID</u>
|int
|Refers to the ID of Agent (= Foreign key and part of primary key)
|-style="background:#F5F5F5;"
|<u>DisplayOrder</u>
|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
|-style="background:#F5F5F5;"
|Country
|nvarchar (255)
|Country of the address
|-
|City
|nvarchar (255)
|City of the address
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|Address
|nvarchar (255)
|Free text postal address of the agent
|-
|Telephone
|nvarchar (50)
|Phone number, including area code
|-style="background:#F5F5F5;"
|CellularPhone
|nvarchar (50)
|The number of a mobile telephone device of the agent
|-
|Telefax
|nvarchar (50)
|Fax number, including area code
|-style="background:#F5F5F5;"
|Email
|nvarchar (255)
|E-mail address of the agent
|-
|URI
|nvarchar (255)
|URI pointing to a homepage containing further information
|-style="background:#F5F5F5;"
|Notes
|nvarchar (255)
|Notes about this address
|-
|ValidFrom
|datetime
|The date when this address became valid as date according to ISO 8601
|-style="background:#F5F5F5;"
|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.<br />''DefaultValue: user_name()''
|-style="background:#F5F5F5;"
|LogUpdatedWhen
|smalldatetime
|Date and time when the data were last updated.<br />''DefaultValue: getdate()''
|}
===Table: AgentExternalDatabase===
The external databases from which data in the database may have been imported from
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>ExternalDatabaseID</u>
|int
|An ID to identify an external data collection of plant names (primary key, the ID has no meaning outside of the DiversityWorkbench system)
|-style="background:#F5F5F5;"
|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)
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|ExternalDatabaseURI
|nvarchar (300)
|The link to the database provider resp. the external database
|-
|ExternalDatabaseInstitution
|nvarchar (300)
|The institution responsible for the external database
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|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
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>AgentID</u>
|int
|Unique ID for the Agent (foreign key + part of primary key)
|-style="background:#F5F5F5;"
|<u>ExternalDatabaseID</u>
|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
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>AgentID</u>
|int
|Unique ID for the Agent  (foreign key + part of primary key)
|-style="background:#F5F5F5;"
|<u>URI</u>
|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
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>AgentID</u>
|int
|Unique ID for the Agent  (foreign key + part of primary key)
|-style="background:#F5F5F5;"
|<u>Keyword</u>
|nvarchar (200)
|The keyword
|}
===Table: AgentProject===
The projects of the agents
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>AgentID</u>
|int
|Unique ID for the Agent  (= Foreign key)
|-style="background:#F5F5F5;"
|<u>ProjectID</u>
|int
|The ID of the project<br />''DefaultValue: 0''
|}
===Table: ProjectProxy===
The projects - refers to database DiversityProjects
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>ProjectID</u>
|int
|The ID of the project, Primary key
|-style="background:#F5F5F5;"
|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
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>LoginName</u>
|nvarchar (50)
|The login name of the user
|-style="background:#F5F5F5;"
|<u>ProjectID</u>
|int
|The ID of the default project of the user as stored in table ProjectProxy
|}
===Table: UserProxy===
The user logins - refers to database DiversityUsers
{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>LoginName</u>
|nvarchar (50)
|The login name of the user, Primary key
|-style="background:#F5F5F5;"
|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
|-style="background:#F5F5F5;"
|ProjectID
|int
|The ID of the default project of the user as stored in table ProjectProxy
|}
===Table: *_Enum===

{|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF;border:1px #AAA solid; border-collapse:collapse; empty-cells:show;"
|-style="text-align:left; background:#D3D3D3;"
!Column
!Data type
!Description
|-
|<u>Code</u>
|nvarchar (50)
|&nbsp;
|-style="background:#F5F5F5;"
|Description
|nvarchar (500)
|&nbsp;
|-
|DisplayText
|nvarchar (50)
|&nbsp;
|-style="background:#F5F5F5;"
|DisplayOrder
|smallint
|&nbsp;
|-
|DisplayEnable
|bit
|&nbsp;
|-style="background:#F5F5F5;"
|InternalNotes
|nvarchar (500)
|&nbsp;
|-
|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:<u>underlined</u>