DiversityAgentsModel v2.1

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

---

ER Diagrams

Overview over all entities and relations used in the database model

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