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