DiversityProjectsModel 2.1.5
Contents
- 1 DiversityProjects Information Model (version 2.1.5, 12 July 2016)
- 1.1 ER Diagrams
- 1.1.1 Overview over all entities and relations used in the database model
- 1.1.2 A short introduction:
- 1.1.3 Table: Project
- 1.1.4 Table: ProjectAgent
- 1.1.5 Table: ProjectIdentifier
- 1.1.6 Table: ProjectIdentifierType
- 1.1.7 Table: ProjectLicense
- 1.1.8 Table: ProjectReference
- 1.1.9 Table: ProjectResource
- 1.1.10 Table: ProjectSetting
- 1.1.11 Table: ProjectUser
- 1.1.12 Table: Setting
- 1.1.13 Table: UserProxy
- 1.1.14 View: ViewDataCitation
- 1.1 ER Diagrams
DiversityProjects Information Model (version 2.1.5, 12 July 2016)
Authors | M. Weiss, G. Hagedorn & D. Triebel 2016 |
License | ![]() |
Suggested citation | M. Weiss, G. Hagedorn & D. Triebel (2016). DiversityProjects information model (version 2.1.5). http://diversityworkbench.net/Portal/DiversityProjectsModel_2.1.5. |
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
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