Data Models

Postgresql Data Model

The Postgresql database is used for storing meta data and Security information The details of each table are described fully in the Mapping Vitalsigns Meta Data section of this documentation. This section will give a high level description of the layout of the model.

Tables shared by all applications
  1. application* - tables that define the attributes of each application
  2. data_input_batch_dir* - tables that describe where to look for input data and mappings used to process it.
  3. data_input_ref* - tables defining reference data
  4. data_layout* - tables defining the layout of data inputs
Application Tables

Each application has its own set of application tables prefixed with a_<app_id> where <app_id> is the app_id field from the application table.

  1. a_<app_id>_data_mapping* - tables that define the data mappings for the application.
  2. a_<app_id>_detail* - tables that define the detail data sets in the application
  3. a_<app_id>_group* - tables that define the organizational structure of the application
  4. a_<app_id>_metric* - tables that define metrics and their display organization
Security Tables
  1. sec_role_data* - tables that define the data roles in the system. Data roles define what applications, and within applications what groups,metrics, and detail data a user may access.
  2. sec_role_func* - tables that define the functional roles in the System. Functional roles define wihch UI functions a user may access (ie. Reporting, Detail Search, etc.).
  3. sec_user* - tables that define the users and their atributes.

MongDb Data Model

The MongoDb database contains collections for detail data and for activity data.

Detail Data

There is one collection per day per detail definition. These collections are named A_<app_id>_<detail_id>_<time_key>_DETAIL_DATA_V2 where app_id is the app_id from application meta data table, detail_id is the detail_id from the a_<app_id>_detail_def meta data table, and time_key is the date (application time zone) in YYYYDDD format.

The fields in a detail document are as follows:

  1. inid - the id from input_log in the Postgresql database that identifies the specific input file the record came from.
  2. rd - the primary date/time stamp of the record.
  3. a series of fields named bt<base_type_id> - these represent the groups the record was tagged with. base_type_id is the base type id from the meta data table a_<app_id>_base_group_type the value of the field will be the base_group_id from a_<app_id>_group_base of the specific group
  4. data fields - each field is named with the field_num from a_<app_id>_detail_field and will contain the raw value from the data input

Interval and Daily Summary Data

The interval and daily summary data is stored in collections named A_<app_id>_BATCH_DATA_I and A_<app_id>_BATCH_DATA_D respectively.

They share a similar data model as follows:

  1. inid - same as detail data inid
  2. tk - the time key for the data - the date in YYYYDDD format
  3. is - interval data only, the start of the interval in seconds past midnight
  4. ie - interval data only, the end of the interval in seconds past midnight
  5. hr - interval data only, the hour of the day
  6. a series of bt<base_type_id> fields - same as described in detail data
  7. as series of m<metric_id> fields - these are the value fields for the metric data. metric_id is the metric_id field from the a_<app_id>_metrics meta data table.
Aggregate Data

In applications with many group types defined it is highly recommended that higher level frequently used summary groups be defined with the aggregate flag set to Y. The aggregates are stored in a collections named A_<app_id>_GROUP_DATA_D. this collection follows the same data model as the BATCH collections, but with few group fields and no inid field as it aggregates across inputs.