Difference between revisions of "DiversitySamplingPlots Information Models"
From Diversity Workbench
(Created page with "=under construction!! - DiversitySamplingPlots Information Model (version 03.00.05, 01. July 2014)= {|border=1 cellpadding="5" style="margin:1em 1em 1em 0; background:#FFFFFF...") |
|||
| Line 24: | Line 24: | ||
===Overview over all entities and relations used in the database model=== | ===Overview over all entities and relations used in the database model=== | ||
| − | [[Image: | + | [[Image:ER_DiversitySamplingPlots_030005.png|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. | 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. | ||
Revision as of 15:36, 30 June 2014
Contents
under construction!! - DiversitySamplingPlots Information Model (version 03.00.05, 01. July 2014)
| Authors | M. Weiss, W. Reichert 2014 |
| License | 
|
| Suggested citation | M. Weiss, W. Reichert (2014). DiversitySamplingPlots information model (version 03.00.05). http://diversityworkbench.net/Portal/ProjectsModel_v03.00.05. |
| 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:
- “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
