GazetteersModel v01.03.05

From Diversity Workbench
Jump to: navigation, search

DiversityGazetteers Information Model (version 01.03.05, 01. July 2014)

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

  • “GeoPlace” is the central entity.


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



Table: ExternalDatabase

Column Data type Description
ExternalDatabaseID int  
ExternalDatabaseName nvarchar (60)  
ExternalDatabaseVersion nvarchar (255)  
ExternalAttribute_NameID nvarchar (255)  
ExternalAttribute_PlaceID nvarchar (255)  
ExternalCoordinatePrecision nvarchar (255)  
InternalNotes ntext (1073741823)  


Table: GeoName

Column Data type Description
NameID int  
Name nvarchar (400) The name of the place
PlaceID int The ID of the place, refers to table GeoPlace
LanguageCode nvarchar (50) The code of the language
ExternalDatabaseID int The ID of the external database as stored in the table ExternalDatabase (foreign key)
ExternalNameID nvarchar (50) The ID in the place in the foreign (source) database
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
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: user_name()
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
HierarchyCache nvarchar (500) The hierarchy of the place, cached value.
Notes nvarchar (MAX) Notes about the geographic name
HierarchyList nvarchar (255) A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID


Table: GeoPlace

Column Data type Description
PlaceID int ID of the place, PK
Geography geography The geography of the place as WGS84 Koordinates and if available the altitude and the area
SuperiorPlaceID int The ID of the superior place
CountryPlaceID_Cache int The ID of the country a place belongs to
RegionPlaceID_Cache int The ID of a region a place belongs to, e.g. a continent for a country
ExternalDatabaseID int The ID of the external database as stored in the table ExternalDatabase (foreign key)
ExternalNameID nvarchar (50) The ID in the place in the foreign (source) database
PlaceType nvarchar (50) The type of the place, Foreign key, refers to table PlaceType_Enum
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data.
DefaultValue: user_name()
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: user_name()
LogUpdatedWhen smalldatetime Date and time when the data were last updated.
DefaultValue: getdate()
PreferredNameID int The NameID of the name preferred for this place. Foreign key refers to table GeoName
Notes nvarchar (MAX) Notes about the geographic place


Table: GeoProject

Column Data type Description
ProjectID int  
NameID int  


Table: PlaceType_Enum

Column Data type Description
Code nvarchar (50)  
Description nvarchar (500)  
DisplayText nvarchar (50)  
DisplayOrder smallint  
DisplayEnable bit  
ParentCode nvarchar (50)  
InternalNotes nvarchar (500)  


Table: ProjectProxy

The projects available within the database

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)
RowGUID uniqueidentifier
DefaultValue: newsequentialid()
ProjectURI varchar (255) The URI of the project, e.g. as provided by the module DiversityProjects.


Table: ProjectUser

The projects available for a user

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


Table: UserProxy

The users with access to the database

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
AgentURI varchar (255) A link to a DiversityAgents module where further informations about the user is available.
Queries xml (MAX)  
CurrentProjectID int The current project selected by the user
RowGUID uniqueidentifier
DefaultValue: newsequentialid()



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