Mapping of IDs for the data transfer into the cache database

In the Diversity Descriptions database the main tables Description, Descriptor and CategoricalState have a numeric "id" as key, which is set by the MS SQL server when the dataset ist crated. All relations between the tables are set by using these unique values. For various reasons those internal IDs are not seen as suitable for all purposes of the cache database. Therefore a mapping of the internal IDs is performed before the transfer of data to the cache database.

Default mapping

The default mapping of the IDs is mainly oriented on the way the states and characters are identified in the widely spread DELTA standard:

• Descriptors are numbered starting with "1" in ascending order and transferred into the cache database table CacheCharacter. The order is determined by the descriptor parameter "display_order" and the alphabetical order. The original "id" is stored as "CharID" in the target table, the descriptor number as "CID".
• CategoricalStates are numbered for each character starting with "1" in ascending order and transferred into the cache database table CacheState. The order is determined by the categorical state parameter "display_order" and the alphabetical order. The original "id" is stored as "StateID" in the target table, the categorical state number as "CS".
• Since the categorical state numbers ("CS") are not unique, each state is identified, e.g. in a description item, by the character and state number ("CID", "CS").
• Descriptions are numbered starting with "1" in ascending order and transferred into the cache database table CacheItem. The order is determined by the alphabetical order. The original "id" is stored as "ItemID" in the target table, the description number as "IID". As an alternative the "IID" may be derived from the field "alternate_id" of the "Description" table (see following section.

The mapping data are stored related to the project in the tables CacheMappingDescriptor, CacheMappingState and CacheMappingDescription of the original database.  

Mapping adjustment

To set the mapping adjustments, click on the  button (see below).

A window as shown below will open.

If no option is selected, the default mapping algorithm described above will be performed for every transfer to the cache database. Any changes, e.g. insertion of a descriptor or re-arrangement of categorical states, will affect the "CID", "CS" and "IID" of the cache database.

If option Keep fixed IDs after first export is selected, the default mapping algorithm described above will be performed only for the first transfer to the cache database. Any changes, e.g. insertion of a descriptor or re-arrangement of categorical states, will NOT affect the "CID", "CS" and "IID" of the cache database. New elements will get a number higher than the last one present. If an element is deleted, this will result in "missing" numbers in the cache database. Pure re-arrangements will have no effect.

The last option Take "IID" from "AID" field only affects the description mapping. By default the descriptions are numbered in alphabetical order. If this option is chosen, it is tried to use the field "alternate_id" ("AID" in the GUI) as item number. Preconditions are that the "AID" is a pure number and that the values are unique. If the "AID" is not supplied or an alpha-numeric string or if the number is already occupied, a new ascending value will be set. By using this option a foreign numbering scheme may be used for the cache database. When selecting this option you might want select Re-initialize "IID" fields to build the description mapping at the next cache transfer.