ProjectsModel v02.00.02

From Workbench
Jump to: navigation, search

DiversityProjects Information Model (version 02.00.02, 08. April 2013)

Authors M. Weiss, G. Hagedorn & D. Triebel 2013
License 80x15.png
Suggested citation M. Weiss, G. Hagedorn & D. Triebel (2013). DiversityProjects information model (version 02.00.02). http://diversityworkbench.net/Portal/ProjectsModel_v02.00.02.
Notes The models currently reside in MS SQL Server, so knowledge of some SQL Server ER-diagram conventions will be helpful.

Besides the screen shots below, a Microsoft T-SQL-Script for the generation of the tables is provided.


ER Diagrams

Overview over all entities and relations used in the database model

ER

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.
  • “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 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