DiversitySamplingPlots Information Models

under construction!! - DiversitySamplingPlots Information Model (version 03.00.05, 01. July 2014)

---

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:

• “SamplingPlot” is the central entity, containing the information directly related to ...

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

---

Table: Project

Informations about the projects

Column	Data type	Description	Requ.	Ind.
ProjectID	int	PK, ID of the Project	R	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	R	-
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	-	-
ProjectCopyright	nvarchar (255)	Copyright statement for the data collected for and revised by this project	-	-
ProjectRights	nvarchar (255)	The rights, e.g. the copyright of the project	-	-
ProjectLicenseURI	varchar (255)	The license under which the project is re-used or may be re-used	-	-
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	-	-
ProjectSettings	xml (MAX)	The settings used within the project	-	-
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: user_name()	-	-
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: user_name()	-	-
LogUpdatedWhen	smalldatetime	Date and time when the data were last updated. DefaultValue: getdate()	-	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	R	-

Table: ProjectAgent

The persons or institutions involved in the project

Column	Data type	Description	Requ.	Ind.
ProjectID	int	Part of PK, ID of the Project	R	U
AgentName	nvarchar (255)	The name of the agent. Only cached value where AgentURI is given	R	U
AgentURI	varchar (255)	An URL linked to e.g. the module DiversityAgents within the Diversity Workbench	R	U
AgentRole	nvarchar (50)	The role of the agent within the project as defined in ProjectAgentRole_Enum	-	-
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: user_name()	-	-
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: user_name()	-	-
LogUpdatedWhen	smalldatetime	Date and time when the data were last updated. DefaultValue: getdate()	-	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	R	-

Table: ProjectResource

Resources of the project

Column	Data type	Description	Requ.	Ind.
ProjectID	int	Refers to the ID of the project (= Foreign key and part of primary key)	R	U
URI	varchar (255)	The complete URI address of the resource.	R	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: user_name()	-	-
LogUpdatedWhen	datetime	The last time when this dataset was updated DefaultValue: getdate()	-	-
LogUpdatedBy	nvarchar (50)	Who was the last to update this dataset DefaultValue: user_name()	-	-
RowGUID	uniqueidentifier	DefaultValue: newsequentialid()	R	-

Table: ProjectUser

The projects available for a user

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

Table: UserProxy

The users with access to the database

Column	Data type	Description	Requ.	Ind.
LoginName	nvarchar (50)	A login name of the user for access to the database server	R	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.	R	-
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()	R	-

Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined R: It is required to enter data in this field. I: The field is indexed to enable faster searching. U: unique index