C) Data Modeling in OLTP Databases

In the realm of manufacturing data management, a key focus is not just on the real-time use of data but also on its storage for later analysis. This is particularly relevant in our discussions on OLTP and OLAP systems, which meet the distinct data needs of frontline workers and business analysts. More details can be found in our UMH blog article.

Our goal is to store specific data points from the Unified Namespace's conceptual framework, such as umh/v1/enterprise/site/area/productionLine/workCell/originID/_schema. There are two key schemas involved in storing this data: _historian and _analytics.

We selected TimescaleDB's hypertable functionality for storing time-series data, valuing its reliability, scalability, and smooth integration into our IT/OT framework. Beyond time-series data, we also manage relational data, and TimescaleDB efficiently supports both, leveraging its underlying PostgreSQL capabilities. This decision, as detailed in our ADRs, represents a harmonious blend of technical sophistication and operational practicality.

Table 1: asset

In both _historian and _analytics schemas, we reference an asset defined in the ISA95 model, like an enterprise or a workcell within a production line. This leads us to the creation of our first table:

CREATE TABLE asset (
  id SERIAL PRIMARY KEY,
  enterprise TEXT NOT NULL,
  site TEXT DEFAULT '' NOT NULL,
  area TEXT DEFAULT '' NOT NULL,
  line TEXT DEFAULT '' NOT NULL,
  workcell TEXT DEFAULT '' NOT NULL,
  origin_id TEXT DEFAULT '' NOT NULL,
  UNIQUE (enterprise),
  UNIQUE (enterprise, site),
  UNIQUE (enterprise, site, area),
  UNIQUE (enterprise, site, area, line),
  UNIQUE (enterprise, site, area, line, workcell),
  UNIQUE (enterprise, site, area, line, workcell, origin_id)
);

In constructing the asset table, we prioritize easy data retrieval. Our approach, detailed in the ADRs, includes using a denormalized SQL table for simplicity and applying unique constraints for data integrity.

Table 2: tag

The _historian schema is structured to store time-series data effectively in TimescaleDB. When payloads have Keys with a Number as value, the key for that asset will be the name, and the value will be the value. The originid will include information about the origin of each tag (e.g., the origin PLC), though it is not implemented yet.

CREATE TABLE tag (
  timestamp TIMESTAMPTZ NOT NULL,
  name TEXT NOT NULL,
  origin TEXT NOT NULL,
  asset_id INT REFERENCES asset(id) NOT NULL,
  value REAL,
  UNIQUE (name, asset_id, timestamp)
);
SELECT create_hypertable('tag', 'timestamp');
CREATE INDEX ON tag (asset_id, timestamp DESC);
CREATE INDEX ON tag (name);

• Users have two simple options to query data
  • SELECT name, value, timestamp
    FROM tag
    WHERE asset = get_asset_id(
    'enterprise1',
    'site1',
    'area1',
    'line1',
    'workcell1',
    'originid1'
    );
  • SELECT tag.name, tag.value, tag.timestamp
    FROM tag
    JOIN asset ON tag.asset = asset.id
    WHERE asset.enterprise = 'enterprise1'
    AND asset.site = 'site1'
    AND asset.area = 'area1'
    AND asset.line = 'line1'
    AND asset.workcell = 'workcell1'
    AND asset.origin_id = 'originid1';