DiversityAgentsModel 2.1.9

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

---

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

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