SamplingPlotsModel v03.00.05

From Diversity Workbench
Jump to: navigation, search

DiversitySamplingPlots Information Model (version 03.00.05, 01. July 2014)

Authors M. Weiss, W. Reichert & D. Triebel 2014
License 80x15.png
Suggested citation M. Weiss, W. Reichert & D. Triebel (2014). DiversitySamplingPlots information model (version 03.00.05). http://diversityworkbench.net/Portal/SamplingPlotsModel_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

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:

  • “SamplingPlot” is the central entity.


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



Table: LocalisationSystem

The geographic localisation systems, e.g. coordinates

Column Data type Description
LocalisationSystemID int Unique ID for the localisation system (= Primary key)
LocalisationSystemParentID int LocalisationSystemID of the superior LocalisationSystem
LocalisationSystemName nvarchar (100) Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS
DefaultAccuracyOfLocalisation nvarchar (50) The default for the accuracy of values that can be reached with this method
DefaultMeasurementUnit nvarchar (50) The default measurement unit for the localisation system, e.g. m, geograpic coordinates
ParsingMethodName nvarchar (50) Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table SamplingPlotLocalisation
DisplayText nvarchar (50) Short abbreviated description of the localisation system as displayed in the user interface
DisplayEnable bit Specifies if this item is enabled to be used within the database. LocalisationSystems can be disabled to avoid seeing them, but to keep the definition for the future.
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.
Description nvarchar (255) Description of the localisation method
DisplayTextLocation1 nvarchar (50) Short abbreviated description of the attribute Location1 in the table CollectionGeography as displayed in the user interface
DescriptionLocation1 nvarchar (255) Description of the attribute Location1 in the table CollectionGeography as displayed in the user interface
DisplayTextLocation2 nvarchar (50) Short abbreviated description of the attribute Location2 in the table CollectionGeography as displayed in the user interface
DescriptionLocation2 nvarchar (255) Description of the attribute Location2 in the table CollectionGeography as displayed in the user interface
RowGUID uniqueidentifier
DefaultValue: newsequentialid()


Table: ProjectProxy

Column Data type Description
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)
Project nvarchar (50) The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects)
ProjectURI varchar (255) The URI of the project, e.g. as provided by the module DiversityProjects.


Table: ProjectUser

Column Data type Description
LoginName nvarchar (50)  
ProjectID int  


Table: Property

Column Data type Description
PropertyID int The ID of the sampling property, Primary key
DisplayText nvarchar (50) The title of the property as shown e.g. in a user interface
Description nvarchar (MAX) The description of the property
PropertyParentID int PropertyID of the superior property
PropertyName nvarchar (100) Name of the property used to describe the place of the collection, may be derived from content of the module DiversityScientificTerms e.g. Pflanzengesellschaften
DefaultAccuracyOfProperty nvarchar (50) The default for the accuracy of values that can be reached with this method
DefaultMeasurementUnit nvarchar (50)  
ParsingMethodName nvarchar (50) Internal value, specifying a programming method used for parsing text in field PropertyValue in table CollectionEventProperty
DisplayEnabled bit Specifies if this item is enabled to be used within the database. Properties can be disabled to avoid seeing them, but to keep the definition for the future.
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.


Table: SamplingPlot

A sampling plot with its geographical position and geometry

Column Data type Description
PlotID int The ID of the sampling plot, Primary key
PartOfPlotID int The ID of the plot of which the current plot is a part of, Foreign key
PlotIdentifier nvarchar (500) The identifier or name of the plot
PlotGeography_Cache geography The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints
PlotGeometry_Cache geometry (MAX) The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints
PlotDescription nvarchar (MAX) A short text, describing the sampling plot
PlotType nvarchar (50) The type of the plot, e.g. trap
InternalNotes nvarchar (MAX) Internal notes regarding the sampling plot, not published on the web
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
LogCreatedBy 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()
CountryCache nvarchar (50) The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation


Table: SamplingPlotLocalisation

The geographic localisation of a sampling plot

Column Data type Description
PlotID int Refers to the ID of SamplingPlot (= Foreign key and part of primary key)
LocalisationSystemID int Refers to the ID of LocalisationSystem (= Foreign key and part of primary key)
Location1 nvarchar (255) Either a named location selected from a thesaurus (e. g. 'Germany, Bavaria, Kleindingharting') or altitude range or other values (e. g. 100-200 m)
Location2 nvarchar (255) Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus
LocationAccuracy nvarchar (50) The accuracy of the determination of this locality
LocationNotes nvarchar (MAX) Notes on the location
DeterminationDate smalldatetime Date of the determination of the geographical localisation
DistanceToLocation varchar (50) Distance from the specified place to the real location of the sampling plot (m)
DirectionToLocation varchar (50) Direction from the specified place to the real location of the sampling plot (Degrees rel. to north)
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)
Geography geography The geography of the localisation
AverageAltitudeCache float Calculated altitude as parsed from the location fields
AverageLatitudeCache float Calculated latitude as parsed from the location fields
AverageLongitudeCache float Calculated longitude as parsed from the location fields
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
LogCreatedBy 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()


Table: SamplingPlotProperty

Column Data type Description
PlotID int The ID of the sampling plot, foreign key and part of primary key
PropertyID int ID of the property to which the sampling plot property belongs
PropertyGeometry geometry (MAX) The geometry of the property in relation to the geometry of the sampling plot, e.g. MultiPoints for lower and upper border of a stratigraphic layer
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
LogCreatedBy 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()
PropertyURI varchar (255) URI referring to an external datasource e.g. DiversityScientificTerms
DisplayText nvarchar (255) The text for the property as shown e.g. in a user interface
PropertyHierarchyCache nvarchar (MAX) A cached text of the complete name of the descriptor including superior categories if present
PropertyValue nvarchar (255) The value of a captured feature e.g. temperature, pH, vegetation etc. If there is a range this is the lower or first value
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)
Notes nvarchar (MAX) Notes about the property of the colletion site.
AverageValueCache float For numeric values - a cached average value according to the


Table: SamplingPlotResource

Column Data type Description
PlotID int The ID of the sampling plot, foreign key and part of primary key
ResourceURI varchar (255) URI referring to the file or link of the resource
ResponsibleName nvarchar (255) The name of the agent (person or organization) responsible for this entry.
ResponsibleAgentURI varchar (255) URI of the person or organisation responsible for the data (see e.g. module DiversityAgents)
Notes nvarchar (MAX) Notes about the resource.
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
LogCreatedBy 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()


Table: SamplingProject

The projects to which a sampling plot is related

Column Data type Description
PlotID int The ID of the sampling plot, foreign key and part of primary key
ProjectID int ID of the project to which the sampling plot belongs (Projects are defined in DiversityProjects)
LogCreatedWhen datetime The time when this dataset was created
DefaultValue: getdate()
LogCreatedBy 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()


Table: UserProxy

Column Data type Description
LoginName nvarchar (50) A login name which the user uses for access the DivesityWorkbench, Microsoft domains, etc..
CombinedNameCache nvarchar (255) The short name of the user, e.g. P. Smith
UserURI varchar (255) URI of a user in a remote module, e.g. refering to UserInfo.UserID in database DiversityUsers
Queries xml (MAX) Queries created by the user
Settings xml (MAX) The settings for the user
AgentURI varchar (255) A link to a DiversityAgents module where further informations about the user is available.
CurrentProjectID int The current project ID selected by the user



Footnotes: The following conventions and abbreviations have been used in the tables: Columns of primary key:underlined