DiversityProjectsModel 2.1.5

DiversityProjects Information Model (version 2.1.5, 12 July 2016)

---

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:

• “Project” is the central entity, containing the information directly related to the project.
• “ProjectAgent” keeps the relation to the agents. Each project may have several agents.
• “ProjectResource” keeps the resources used within the project.
• “ProjectIdentifier” keeps external identifiers of the project.
• “ProjectLicense” keeps the licenses of the project.
• “ProjectReference” keeps the references used within the project.
• “ProjectSetting” keeps the settings used within the project.
• “Setting” keeps the definitions of the settings.
• “UserProxy” keeps information about the users with access to the DiversityProjects database.

Entities ending with “_Enum” (“ProjectAgentRole_Enum”) contain enumerated values or strings.

---

Table: Project

Informations about the projects

Column	Data type	Description	Ind.
ProjectID	int	PK, ID of the Project	U
ProjectParentID	int	ID of the superior project	-
Project	nvarchar (50)	A short title for the project as displayed e.g. for selection from a list in an user interface	-
ProjectTitle	nvarchar (200)	An explicit title for the project	-
ProjectDescription	nvarchar (2000)	A short text, describing the purpose and scope of the project	-
ProjectNotes	nvarchar (1000)	Internal notes regarding the project, not published on the web	-
ProjectVersion	nvarchar (255)	Version number (refers to the database content of this project, not to application development)	-
ProjectURL	varchar (255)	An URL where e.g. the project is described	-
ProjectInstitution	nvarchar (500)	The institution responsible for the Project	-
ProjectEditors	nvarchar (255)	The editors of the project	-
ProjectSettings	xml (MAX)	The settings used within the project	-
ProjectRights	nvarchar (255)	The rights, e.g. the copyright of the project	-
ProjectCopyright	nvarchar (255)	Copyright statement for the data collected for and revised by this project	-
ProjectLicenseURI	nvarchar (255)	The license under which the project is re-used or may be re-used	-
LogInsertedBy	nvarchar (50)	Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible. DefaultValue: suser_sname()	-
LogInsertedWhen	smalldatetime	Date and time when record was first entered (typed or imported) into this system. 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()	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

Table: ProjectAgent

The persons or institutions involved in the project

Column	Data type	Description	Ind.
ProjectID	int	Refers to the ID of the project (= Foreign key and part of primary key)	U
AgentName	nvarchar (255)	The name of the agent. Only cached value where AgentURI is given, Part of PK	U
AgentURI	varchar (255)	An URL linked to e.g. the module DiversityAgents within the Diversity Workbench, Part of PK	U
AgentRole	nvarchar (50)	The role of the agent within the project as defined in ProjectAgentRole_Enum	-
AgentType	nvarchar (50)	The type of the agent, e.g. person or institution as defined in ProjectAgentType_Enum	-
AgentSequence	int	The sequence of an agent within a project	-
Notes	nvarchar (MAX)	Notes about the agent	-
LogInsertedBy	nvarchar (50)	Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible. DefaultValue: suser_sname()	-
LogInsertedWhen	smalldatetime	Date and time when record was first entered (typed or imported) into this system. 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()	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

Table: ProjectIdentifier

External identifiers for the project, e.g. a DOI

Column	Data type	Description	Ind.
ProjectID	int	Part of PK, ID of the Project	U
Identifier	nvarchar (255)	The name of the agent. Only cached value where AgentURI is given	U
Type	nvarchar (50)	The type of the identifier as defined in table ExternalIdentifierType	-
URL	varchar (500)	A URL with further informations about the identifier	-
Notes	nvarchar (MAX)	Notes about the identifier	-
LogInsertedBy	nvarchar (50)	Name of user who first entered (typed or imported) the data. This is the operator (or typist) name, which may be different from the person responsible. DefaultValue: suser_sname()	-
LogInsertedWhen	smalldatetime	Date and time when record was first entered (typed or imported) into this system. 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()	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

Table: ProjectIdentifierType

The type of an external identier, e.g. DOI

Column	Data type	Description	Ind.
Type	nvarchar (50)	The type of project identifiers (primary key)	U
ParentType	nvarchar (50)	The superior type of this type	-
URL	varchar (500)	A URL providing with further informations about this type	-
Description	nvarchar (MAX)	The description of this type	-
InternalNotes	nvarchar (MAX)	Internal notes about the type	-
LogCreatedWhen	datetime	Point in time when this data set was created DefaultValue: getdate()	-
LogCreatedBy	nvarchar (50)	Name of the creator of this data set DefaultValue: suser_sname()	-
LogUpdatedWhen	datetime	Point in time when this data set was updated last DefaultValue: getdate()	-
LogUpdatedBy	nvarchar (50)	Name of the person to update this data set last DefaultValue: suser_sname()	-
RowGUID	uniqueidentifier	DefaultValue: newid()	-

Table: ProjectLicense

The Licenses within a project

Column	Data type	Description	Ind.
LicenseID	int	ID of the License, part of primary key	U
ProjectID	int	Refers to the ID of the project (= Foreign key and part of primary key)	U
DisplayText	nvarchar (200)	A display text as shown in the interface generated by the system or edited by the user	-
LicenseURI	varchar (500)	The URL of the license under which the project is re-used or may be re-used	-
LicenseType	nvarchar (500)	Type of an official or legal permission to do or own a specified thing, e. g. Creative Common Licenses, Copyright Licenses	-
LicenseHolder	nvarchar (500)	The person or institution holding the license	-
LicenseHolderAgentURI	varchar (500)	The link to a module containing futher information on the person or institution holding the license	-
LicenseYear	nvarchar (50)	The year of the license declaration	-
Context	nvarchar (500)	The context of the license, e.g. for publication	-
LicenseDetails	nvarchar (500)	Details of the license	-
IPR	nvarchar (500)	Intellectual Property Rights; the rights given to persons for their intellectual property	-
IPRHolder	nvarchar (500)	The person or institution holding the Intellectual Property Rights	-
IPRHolderAgentURI	varchar (500)	The link to a module containing futher information on the person or institution holding the	-
CopyrightStatement	nvarchar (500)	Notice on rights held in and for the resource	-
CopyrightHolder	nvarchar (500)	The person or institution holding the copyright	-
CopyrightHolderAgentURI	varchar (500)	The link to a module containing futher information on the person or institution holding the copyright	-
Notes	nvarchar (MAX)	Notes about the license	-
LogCreatedWhen	datetime	Point in time when this data set was created DefaultValue: getdate()	-
LogCreatedBy	nvarchar (50)	Name of the creator of this data set DefaultValue: suser_sname()	-
LogUpdatedWhen	datetime	Point in time when this data set was updated last DefaultValue: getdate()	-
LogUpdatedBy	nvarchar (50)	Name of the person to update this data set last DefaultValue: suser_sname()	-
RowGUID	uniqueidentifier	DefaultValue: newid()	-

Table: ProjectReference

The references related to the project

Column	Data type	Description	Ind.
ProjectID	int	Refers to the ID of the project (= Foreign key and part of primary key)	U
ReferenceTitle	nvarchar (255)	The title of the publication where information about the project was published. Note this is only a cached value where ReferenceURI is present	U
ReferenceURI	varchar (255)	URI of reference where information about the project, e.g. referring to the module DiversityReferences	-
ReferenceDetails	nvarchar (50)	Details within the reference, e.g. pages	-
Citation	nvarchar (255)	The way the reference should be cited	-
Notes	nvarchar (255)	Notes about the reference	-
LogInsertedWhen	datetime	The time when this dataset was created DefaultValue: getdate()	-
LogInsertedBy	nvarchar (50)	Who created this dataset DefaultValue: suser_sname()	-
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()	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

Table: ProjectResource

Resources of the project

Column	Data type	Description	Ind.
ProjectID	int	Refers to the ID of the project (= Foreign key and part of primary key)	U
URI	varchar (255)	The complete URI address of the resource.	U
SpecificRights	nvarchar (500)	The rights, e.g. the copyright of the resource	-
SpecificLicenseURI	nvarchar (500)	The license under which the resource is re-used or may be re-used	-
ResourceURI	varchar (255)	The URI of the image, e.g. as stored in the module DiversityResources.	-
Notes	nvarchar (MAX)	Notes about the resource	-
LogInsertedWhen	datetime	The time when this dataset was created DefaultValue: getdate()	-
LogInsertedBy	nvarchar (50)	Who created this dataset DefaultValue: suser_sname()	-
LogUpdatedWhen	datetime	The last time when this dataset was updated DefaultValue: getdate()	-
LogUpdatedBy	nvarchar (50)	Who was the last to update this dataset DefaultValue: suser_sname()	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

Table: ProjectSetting

Settings of a project

Column	Data type	Description	Ind.
ProjectID	int	Refers to the ID of the project (= Foreign key and part of primary key)	U
SettingID	int	Refers to ID of Settings, foreign key and part of primary key	U
Value	nvarchar (MAX)	The value of the setting	-
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()	-
RowGUID	uniqueidentifier	DefaultValue: newid()	-

Table: ProjectUser

The projects available for a user

Column	Data type	Description	Ind.
LoginName	nvarchar (50)	A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..	U
ProjectID	int	ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)	U
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

Table: Setting

The settings used within the projects

Column	Data type	Description	Ind.
SettingID	int	ID of the setting, primary key	U
ParentSettingID	int	The ID of the superior setting	-
DisplayText	nvarchar (50)	Short abbreviated description of the setting, displayed in the user interface	-
Description	nvarchar (MAX)	Description of the setting	-
DisplayOrder	int	The sequence in which the items should be shown in an interface	-
LogCreatedWhen	datetime	Point in time when this data set was created DefaultValue: getdate()	-
LogCreatedBy	nvarchar (50)	Name of the creator of this data set DefaultValue: suser_sname()	-
LogUpdatedWhen	datetime	Point in time when this data set was updated last DefaultValue: getdate()	-
LogUpdatedBy	nvarchar (50)	Name of the person to update this data set last DefaultValue: suser_sname()	-
RowGUID	uniqueidentifier	DefaultValue: newid()	-

Table: UserProxy

The users with access to the database

Column	Data type	Description	Ind.
LoginName	nvarchar (50)	A login name of the user for access to the database server	U
CombinedNameCache	nvarchar (255)	The short name of the user, e.g. P. Smith. A cached value derived from an entry in the module DiversityAgents if AgentURI is filled.	-
AgentURI	varchar (255)	URI of a user in the remote module DiversityAgents	-
Queries	xml (MAX)	Queries defined by the user to access the data in the database	-
Settings	xml (MAX)	The settings for the user	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	-

View: ViewDataCitation

The citation of published data from the project; references of the data publication of the project data package

Column	Data type	Description
ProjectID	int	Refers to the ID of table Project (= Foreign key and part of primary key)
ReferenceTitle	nvarchar (255)	The title of the data publication of the project data package. Note this is only a cached value where ReferenceURI is present.
ReferenceURI	varchar (255)	URI of project data citation/ data package citation of the project data package, e.g. referring to the module DiversityReferences.
ReferenceDetails	nvarchar (50)	Details within the project data citation/ data package citation, e.g. versioning
ReferenceType	nvarchar (255)	The type of the project data citation/ data package citation
Notes	nvarchar (MAX)	Notes on the project data citation/ data package citation
URI	nvarchar (500)	URI of project data citation/ data package citation with information on the project dataset(s)
ReferenceCitation	nvarchar (500)	The way the project data/ data package should be cited; suggested citation style

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