Diversity Gazetteer

The following objects are not included:

  • Logging tables
  • Enumeration tables
  • System objects
  • Objects marked as obsolete
  • Previous versions of objects

Tables, …

TABLES

Table ExternalDatabase

External datasource of the data

Column Data type Description Nullable Relation
ExternalDatabaseID int - NO -
ExternalDatabaseName nvarchar (60) - NO -
ExternalDatabaseVersion nvarchar (255) - NO -
ExternalAttribute_NameID nvarchar (255) Attribute of the extarnal source for mapping the data in table GeoName YES -
ExternalAttribute_PlaceID nvarchar (255) Attribute of the extarnal source for mapping the data in table GeoPlace YES -
ExternalCoordinatePrecision nvarchar (255) Description of the coordinate precision in the external source YES -
InternalNotes ntext Notes concerning the import of the data YES -

Table GeoCache

Cached values for the hierarchy of places

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

Table GeoName

The names of the geographical objects

Column Data type Description Nullable Relation
NameID int The ID of the name, PK NO -
Name nvarchar (400) The name of the place NO -
PlaceID int The ID of the place, refers to table GeoPlace YES -
LanguageCode nvarchar (50) The code of the language YES -
ExternalDatabaseID int The ID of the external database as stored in the table ExternalDatabase (foreign key) YES -
ExternalNameID nvarchar (50) The ID in the place in the foreign (source) database YES -
Notes nvarchar (MAX) Notes about the geographic name YES -
HierarchyCache nvarchar (500) The hierarchy of the place, cached value. YES -
HierarchyList nvarchar (255) A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID YES -
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. YES -

Table GeoName_log

Column Data type Description Nullable Relation
NameID int - YES -
Name nvarchar (400) - YES -
PlaceID int - YES -
LanguageCode nvarchar (50) - YES -
ExternalDatabaseID int - YES -
ExternalNameID nvarchar (50) - YES -
LogInsertedBy nvarchar (50) - YES -
LogInsertedWhen smalldatetime - YES -
LogUpdatedBy nvarchar (50) - YES -
LogUpdatedWhen smalldatetime - YES -
LogState char (1) - YES -
LogDate datetime - NO -
LogUser nvarchar (50) - YES -
LogID int - NO -
Notes nvarchar (MAX) - YES -

Table GeoNameLanguage

Column Data type Description Nullable Relation
NameID int - NO -
LanguageCode nvarchar (50) - NO -
LogInsertedBy nvarchar (50) - YES -
LogInsertedWhen smalldatetime - YES -
LogUpdatedBy nvarchar (50) - YES -
LogUpdatedWhen smalldatetime - YES -

Table GeoPlace

The geographical places

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

Table GeoPlace_log

Column Data type Description Nullable Relation
PlaceID int - YES -
Geography geography - YES -
SuperiorPlaceID int - YES -
CountryPlaceID_Cache int - YES -
RegionPlaceID_Cache int - YES -
ExternalDatabaseID int - YES -
ExternalNameID nvarchar (50) - YES -
PlaceType nvarchar (50) - YES -
LogInsertedBy nvarchar (50) - YES -
LogInsertedWhen smalldatetime - YES -
LogUpdatedBy nvarchar (50) - YES -
LogUpdatedWhen smalldatetime - YES -
LogState char (1) - YES -
LogDate datetime - NO -
LogUser nvarchar (50) - YES -
LogID int - NO -
Notes nvarchar (MAX) - YES -

Table GeoPlaceType

The types of the geographical places

Column Data type Description Nullable Relation
PlaceID int ID of the place, part of PK NO -
PlaceType nvarchar (50) Type of the place, part of PK NO -
LogInsertedBy nvarchar (50) Name of user who first entered (typed or imported) the data. YES -
LogInsertedWhen smalldatetime Date and time when the data were first entered (typed or imported) into this database. YES -
LogUpdatedBy nvarchar (50) Name of user who last updated the data. YES -
LogUpdatedWhen smalldatetime Date and time when the data were last updated. YES -

Table GeoProject

The projects the names are linked to

Column Data type Description Nullable Relation
ProjectID int The ID of the project, part of PK NO -
NameID int The ID of the name, part of PK NO -

Table LanguageCode_Enum

The codes for the languages

Column Data type Description Nullable Relation
Code nvarchar (50) A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. NO -
Description nvarchar (500) Description of enumerated object, displayed in the user interface YES -
DisplayText nvarchar (50) Short abbreviated description of the object, displayed in the user interface YES -
DisplayOrder smallint The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. YES -
DisplayEnable bit Enumerated objects can be hidden from the user interface, if this attribute is set to false (= unchecked check box) YES -
ParentCode nvarchar (50) The code of the superior entry, if a hierarchy within the entries is necessary YES Refers to table LanguageCode_Enum
InternalNotes nvarchar (500) Internal development notes on usage, definition, etc. of an enumerated object YES -
RowGUID uniqueidentifier -Default value: newsequentialid() NO -

Table PlaceType_Enum

Column Data type Description Nullable Relation
Code nvarchar (50) - NO -
Description nvarchar (500) - YES -
DisplayText nvarchar (50) - YES -
DisplayOrder smallint - YES -
DisplayEnable bit - YES -
ParentCode nvarchar (50) - YES -
InternalNotes nvarchar (500) - YES -
HierarchyPrefixUp nvarchar (50) - YES -
HierarchyPrefixDown nvarchar (50) - YES -
HierarchyPostfixUp nvarchar (50) - YES -
HierarchyPostfixDown nvarchar (50) - YES -

Table ProjectProxy

The projects as stored in the module DiversityProjects

Column Data type Description Nullable Relation
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) NO -
Project nvarchar (50) The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) YES -
ImageDescriptionTemplate xml (MAX) Template for the description of images YES -
ProjectURI varchar (255) The URI of the project, e.g. as provided by the module DiversityProjects. YES -
CreateArchive bit If an archive e.g. by a task schedule should be created YES -
ArchiveProtocol nvarchar (MAX) The protocol created during the last archive YES -
StableIdentifierBase varchar (500) The initial string of the stable identifier for data of this project YES -
StableIdentifierTypeID int The type of the stable identifier for data of this project - evaluated in function StableIdentifier for the creation of stable identifiers YES -
RowGUID uniqueidentifier -Default value: newsequentialid() NO -
LastChanges datetime The recent date when data within the project had been changed YES -
IsLocked bit If the data within the project should not be changeed and the access for all users is restricted to read onlyDefault value: (0) YES -

Table ProjectUser

The projects which a user can access

Column Data type Description Nullable Relation
LoginName nvarchar (50) A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc.. NO Refers to table UserProxy
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) NO Refers to table ProjectProxy
ReadOnly bit If the user has only read access to data of this projectDefault value: (0) YES -
RowGUID uniqueidentifier -Default value: newsequentialid() NO -

Depending on:

  • ProjectProxy
  • UserProxy

trgInsProjectUser

Setting ReadOnly in dependence of locked projects

Table UserProxy

The user as stored in the module DiversityAgents

Column Data type Description Nullable Relation
LoginName nvarchar (50) A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc. NO -
CombinedNameCache nvarchar (255) The short name of the user, e.g. P. SmithDefault value: NULL NO -
AgentURI varchar (255) URI of a agent in the module DiversityAgents YES -
Queries xml (MAX) Queries created by the user YES -
RowGUID uniqueidentifier -Default value: newsequentialid() NO -
Settings xml (MAX) The settings for the user YES -
CurrentProjectID int The current project selected by the user YES -
ID int ID of the user NO -
PrivacyConsent bit If the user consents the storage of his user name in the database YES -
PrivacyConsentDate datetime The time and date when the user consented or refused the storage of his user name in the database YES -