Backing Store Table Reference

The backing store uses relational tables and SQL data types for ease of maintenance. The SQL (DDL) scripts use ANSI SQL type definitions (where supported by the target DBMS product).

Each ontology type in the backing store has its own primary table and zero or more second-level tables. There are only two levels of tables, which makes the database easier to manage and easier to understand. Because the backing store adheres to SQL standards and a straight-forward structure, standard database tools can be used to view backing store data.

Primary Tables

Primary tables contain only primitive properties such as the following:

Property Note
cacheId
Entity version number (starts with 1)
time_created$
Time when the entity was created
time_last_modified$
Time when the entity was last modified
parent$_id$
Id of the parent for contained concepts
id$
Unique Id of the entity (must be unique across all entities)
extId$
Unique (or null) extId assigned
state$
Always set to ‘C’ meaning ‘Created’ (reserved for future use)

Secondary Tables

Secondary tables are used for complex properties, that is, arrays, properties with history, and concept relationship properties. Each array and history-enabled property has a separate table. Only primitive properties are stored in the primary table.

Secondary table structure
property Type Column Description
Array
pid$
Parent ID
 
valPid$
Array index
 
val
Item’s value
History
pid$
Parent ID
 
howMany
Number of history items
 
timeIdx
Item’s time stamp
 
val
Item’s value
Array with History
pid&
Parent ID
 
valPid$
Array index
 
howMany
Number of history items
 
timeIdx
Item’s time stamp
 
val
Item’s value

Reverse Reference Tables

Each concept also has a reverse reference table. This table’s name contains the concept name and ends with the characters _rrf$. It has these columns:

Column Description
pid$
Parent ID from the main concept table
propertyName$
Property name (field) from the referencing concept.
id$
Identifier (id$) of the referencing concept.

Class-to-Table Mapping

This table contains the mapping between class names and table names, and the mapping between complex property field names and secondary table names. For example:
'be.gen.Ontology.DeleteVerifyEvent', 'D_DeleteVerifyEvent‘
'be.gen.Ontology.Treatment', 'D_Treatment‘
‘be.gen.Ontology.Treatment', 'rrf$', 'D_Treatment_rrf$‘
'be.gen.Ontology.BaseAlert', 'treatments', 'D_BaseAlert_treatments'