GazetteersModel 01.00.31

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

---

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

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