DiversityAgentsModel v2.0

From Diversity Workbench
Jump to: navigation, search

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.
ReplacedWithAgentID 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
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


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