GazetteersModel 01.00.31

From Diversity Workbench
(Redirected from GazetteersModel v01.00.31)
Jump to: navigation, search

DiversityGazetteers Information Model (version 01.00.31, 04. March 2020)

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


dwbG Database Scheme

This information model is available as dwbG database scheme with each single data table and data column referenced as term or concept by its own stable and persistent URL.


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: GeoCache

Column Data type Description
PlaceID int Primary key as defined in table GeoPlace
Country nvarchar (100) The cached country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID
HierarchyPlaceToCountry nvarchar (2000) The cached hierarchy from the place to the country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID
HierarchyCountryToPlace nvarchar (2000) The cached hierarchy from the country to the place derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID
SuperiorPlaceID int Temporary used value for the superior place ID - empty after successful update of the cache values


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
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: GeoNameLanguage

Column Data type Description
NameID int  
LanguageCode nvarchar (50)  


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
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: GeoPlaceType

Column Data type Description
PlaceID int  
PlaceType nvarchar (50)  


Table: GeoProject

Column Data type Description
ProjectID int  
NameID int  


Table: LanguageCode_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: 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
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
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()
ID int ID of the user
PrivacyConsent bit If the user consents the storage of his user name in the database
PrivacyConsentDate datetime The time and date when the user consented or refused the storage of his user name in the database



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