This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Database

The database stores the messages in different tables.

1: assetTable
2: configurationTable
3: countTable
4: orderTable
5: processValueStringTable
6: processValueTable
7: productTable
8: recommendationTable
9: shiftTable
10: stateTable
11: uniqueProductTable

Introduction

We are using the database TimescaleDB, which is based on PostgreSQL and supports standard relational SQL database work, while also supporting time-series databases. This allows for usage of regular SQL queries, while also allowing to process and store time-series data. Postgresql has proven itself reliable over the last 25 years, so we are happy to use it.

If you want to learn more about database paradigms, please refer to the knowledge article about that topic. It also includes a concise video summarizing what you need to know about different paradigms.

Our database model is designed to represent a physical manufacturing process. It keeps track of the following data:

The state of the machine
The products that are produced
The orders for the products
The workers’ shifts
Arbitrary process values (sensor data)
The producible products
Recommendations for the production

Please note that our database does not use a retention policy. This means that your database can grow quite fast if you save a lot of process values. Take a look at our guide on enabling data compression and retention in TimescaleDB to customize the database to your needs.

A good method to check your db size would be to use the following commands inside postgres shell:

SELECT pg_size_pretty(pg_database_size('factoryinsight'));

1 - assetTable

assetTable is contains all assets and their location.

Usage

Primary table for our data structure, it contains all the assets and their location.

Structure

key	data type	description	example
`id`	`int`	Auto incrementing id of the asset	0
`assetID`	`text`	Asset name	Printer-03
`location`	`text`	Physical location of the asset	DCCAachen
`customer`	`text`	Customer name, in most cases “factoryinsight”	factoryinsight

Relations

DDL

 CREATE TABLE IF NOT EXISTS assetTable
 (
     id         SERIAL  PRIMARY KEY,
     assetID    TEXT    NOT NULL,
     location   TEXT    NOT NULL,
     customer   TEXT    NOT NULL,
     unique (assetID, location, customer)
 );

2 - configurationTable

configurationTable stores the configuration of the UMH system.

Usage

This table stores the configuration of the system

Structure

key	data type	description	example
`customer`	`text`	Customer name	factoryinsight
`MicrostopDurationInSeconds`	`integer`	Stop counts as microstop if smaller than this value	120
`IgnoreMicrostopUnderThisDurationInSeconds`	`integer`	Ignore stops under this value	-1
`MinimumRunningTimeInSeconds`	`integer`	Minimum runtime of the asset before tracking micro-stops	0
`ThresholdForNoShiftsConsideredBreakInSeconds`	`integer`	If no shift is shorter than this value, it is a break	2100
`LowSpeedThresholdInPcsPerHour`	`integer`	Threshold once machine should go into low speed state	-1
`AutomaticallyIdentifyChangeovers`	`boolean`	Automatically identify changeovers in production	true
`LanguageCode`	`integer`	0 is german, 1 is english	1
`AvailabilityLossStates`	`integer[]`	States to count as availability loss	{40000, 180000, 190000, 200000, 210000, 220000}
`PerformanceLossStates`	`integer[]`	States to count as performance loss	{20000, 50000, 60000, 70000, 80000, 90000, 100000, 110000, 120000, 130000, 140000, 150000}

Relations

DDL

CREATE TABLE IF NOT EXISTS configurationTable
(
    customer TEXT PRIMARY KEY,
    MicrostopDurationInSeconds INTEGER DEFAULT 60*2,
    IgnoreMicrostopUnderThisDurationInSeconds INTEGER DEFAULT -1, --do not apply
    MinimumRunningTimeInSeconds INTEGER DEFAULT 0, --do not apply
    ThresholdForNoShiftsConsideredBreakInSeconds INTEGER DEFAULT 60*35,
    LowSpeedThresholdInPcsPerHour INTEGER DEFAULT -1, --do not apply
    AutomaticallyIdentifyChangeovers BOOLEAN DEFAULT true,
    LanguageCode INTEGER DEFAULT 1, -- english
    AvailabilityLossStates INTEGER[] DEFAULT '{40000, 180000, 190000, 200000, 210000, 220000}',
    PerformanceLossStates INTEGER[] DEFAULT '{20000, 50000, 60000, 70000, 80000, 90000, 100000, 110000, 120000, 130000, 140000, 150000}'
);

3 - countTable

countTable contains all reported counts of all assets.

Usage

This table contains all reported counts of the assets.

Structure

key	data type	description	example
`timestamp`	`timestamptz`	Entry timestamp	0
`asset_id`	`serial`	Asset id (see assetTable)	1
`count`	`integer`	A count greater 0	1

Relations

DDL

CREATE TABLE IF NOT EXISTS countTable
(
    timestamp                TIMESTAMPTZ                         NOT NULL,
    asset_id            SERIAL REFERENCES assetTable (id),
    count INTEGER CHECK (count > 0),
    UNIQUE(timestamp, asset_id)
);
-- creating hypertable
SELECT create_hypertable('countTable', 'timestamp');

-- creating an index to increase performance
CREATE INDEX ON countTable (asset_id, timestamp DESC);

4 - orderTable

orderTable contains orders for production.

Usage

This table stores orders for product production

Structure

key	data type	description	example
`order_id`	`serial`	Auto incrementing id	0
`order_name`	`text`	Name of the order	Scarjit-500-DaVinci-1-24062022
`product_id`	`serial`	Product id to produce	1
`begin_timestamp`	`timestamptz`	Begin timestamp of the order	0
`end_timestamp`	`timestamptz`	End timestamp of the order	10000
`target_units`	`integer`	How many product to produce	500
`asset_id`	`serial`	Which asset to produce on (see assetTable)	1

Relations

DDL

CREATE TABLE IF NOT EXISTS orderTable
(
    order_id        SERIAL          PRIMARY KEY,
    order_name      TEXT            NOT NULL,
    product_id      SERIAL          REFERENCES productTable (product_id),
    begin_timestamp TIMESTAMPTZ,
    end_timestamp   TIMESTAMPTZ,
    target_units    INTEGER,
    asset_id        SERIAL          REFERENCES assetTable (id),
    unique (asset_id, order_name),
    CHECK (begin_timestamp < end_timestamp),
    CHECK (target_units > 0),
    EXCLUDE USING gist (asset_id WITH =, tstzrange(begin_timestamp, end_timestamp) WITH &&) WHERE (begin_timestamp IS NOT NULL AND end_timestamp IS NOT NULL)
);

5 - processValueStringTable

processValueStringTable contains process values.

Usage

This table stores process values, for example toner level of a printer, flow rate of a pump, etc. This table, has a closely related table for storing number values, processValueTable.

Structure

key	data type	description	example
`timestamp`	`timestamptz`	Entry timestamp	0
`asset_id`	`serial`	Asset id (see assetTable)	1
`valueName`	`text`	Name of the process value	toner-level
`value`	`string`	Value of the process value	100

Relations

DDL

CREATE TABLE IF NOT EXISTS processValueStringTable
(
    timestamp               TIMESTAMPTZ                         NOT NULL,
    asset_id                SERIAL                              REFERENCES assetTable (id),
    valueName               TEXT                                NOT NULL,
    value                   TEST                                NULL,
    UNIQUE(timestamp, asset_id, valueName)
);
-- creating hypertable
SELECT create_hypertable('processValueStringTable', 'timestamp');

-- creating an index to increase performance
CREATE INDEX ON processValueStringTable (asset_id, timestamp DESC);

-- creating an index to increase performance
CREATE INDEX ON processValueStringTable (valuename);

6 - processValueTable

processValueTable contains process values.

Usage

This table stores process values, for example toner level of a printer, flow rate of a pump, etc. This table, has a closely related table for storing string values, processValueStringTable.

Structure

key	data type	description	example
`timestamp`	`timestamptz`	Entry timestamp	0
`asset_id`	`serial`	Asset id (see assetTable)	1
`valueName`	`text`	Name of the process value	toner-level
`value`	`double`	Value of the process value	100

Relations

DDL

CREATE TABLE IF NOT EXISTS processValueTable
(
    timestamp               TIMESTAMPTZ                         NOT NULL,
    asset_id                SERIAL                              REFERENCES assetTable (id),
    valueName               TEXT                                NOT NULL,
    value                   DOUBLE PRECISION                    NULL,
    UNIQUE(timestamp, asset_id, valueName)
);
-- creating hypertable
SELECT create_hypertable('processValueTable', 'timestamp');

-- creating an index to increase performance
CREATE INDEX ON processValueTable (asset_id, timestamp DESC);

-- creating an index to increase performance
CREATE INDEX ON processValueTable (valuename);

7 - productTable

productTable contains products in production.

Usage

This table products to be produced at assets

Structure

key	data type	description	example
`product_id`	`serial`	Auto incrementing id	0
`product_name`	`text`	Name of the product	Painting-DaVinci-1
`asset_id`	`serial`	Asset producing this product (see assetTable)	1
`time_per_unit_in_seconds`	`real`	Time in seconds to produce this product	600

Relations

DDL

CREATE TABLE IF NOT EXISTS productTable
(
    product_id                  SERIAL PRIMARY KEY,
    product_name                TEXT NOT NULL,
    asset_id                    SERIAL REFERENCES assetTable (id),
    time_per_unit_in_seconds    REAL NOT NULL,
    UNIQUE(product_name, asset_id),
    CHECK (time_per_unit_in_seconds > 0)
);

8 - recommendationTable

recommendationTable contains given recommendation for the shop floor assets.

Usage

This table stores recommendations

Structure

key	data type	description	example
`uid`	`text`	Id of the recommendation	refill_toner
`timestamp`	`timestamptz`	Timestamp of recommendation insertion	1
`recommendationType`	`integer`	Used to subscribe people to specific types only	3
`enabled`	`bool`	Recommendation can be outputted	true
`recommendationValues`	`text`	Values to change to resolve recommendation	{ “toner-level”: 100 }
`diagnoseTextDE`	`text`	Diagnose text in german	“Der Toner ist leer”
`diagnoseTextEN`	`text`	Diagnose text in english	“The toner is empty”
`recommendationTextDE`	`text`	Recommendation text in german	“Bitte den Toner auffüllen”
`recommendationTextEN`	`text`	Recommendation text in english	“Please refill the toner”

Relations

DDL

CREATE TABLE IF NOT EXISTS recommendationTable
(
    uid                     TEXT                                PRIMARY KEY,
    timestamp               TIMESTAMPTZ                         NOT NULL,
    recommendationType      INTEGER                             NOT NULL,
    enabled                 BOOLEAN                             NOT NULL,
    recommendationValues    TEXT,
    diagnoseTextDE          TEXT,
    diagnoseTextEN          TEXT,
    recommendationTextDE    TEXT,
    recommendationTextEN    TEXT
);

9 - shiftTable

shiftTable contains shifts with asset, start and finish timestamp

Usage

This table stores shifts

Structure

key	data type	description	example
`id`	`serial`	Auto incrementing id	0
`type`	`integer`	Shift type (1 for shift, 0 for no shift)	1
`begin_timestamp`	`timestamptz`	Begin of the shift	3
`end_timestamp`	`timestamptz`	End of the shift	10
`asset_id`	`text`	Asset ID the shift is performed on (see assetTable)	1

Relations

DDL

-- Using btree_gist to avoid overlapping shifts
-- Source: https://gist.github.com/fphilipe/0a2a3d50a9f3834683bf
CREATE EXTENSION btree_gist;
CREATE TABLE IF NOT EXISTS shiftTable
(
    id              SERIAL      PRIMARY KEY,
    type            INTEGER,
    begin_timestamp TIMESTAMPTZ NOT NULL,
    end_timestamp   TIMESTAMPTZ,
    asset_id        SERIAL      REFERENCES assetTable (id),
    unique (begin_timestamp, asset_id),
    CHECK (begin_timestamp < end_timestamp),
    EXCLUDE USING gist (asset_id WITH =, tstzrange(begin_timestamp, end_timestamp) WITH &&)
);

10 - stateTable

stateTable contains the states of all assets.

Usage

This table contains all state changes of the assets.

Structure

key	data type	description	example
`timestamp`	`timestamptz`	Entry timestamp	0
`asset_id`	`serial`	Asset ID (see assetTable)	1
`state`	`integer`	State ID (see states)	40000

Relations

DDL

CREATE TABLE IF NOT EXISTS stateTable
(
    timestamp   TIMESTAMPTZ NOT NULL,
    asset_id    SERIAL      REFERENCES assetTable (id),
    state       INTEGER     CHECK (state >= 0),
    UNIQUE(timestamp, asset_id)
);
-- creating hypertable
SELECT create_hypertable('stateTable', 'timestamp');

-- creating an index to increase performance
CREATE INDEX ON stateTable (asset_id, timestamp DESC);

11 - uniqueProductTable

uniqueProductTable contains unique products and their IDs.

Usage

This table stores unique products.

Structure

key	data type	description	example
`uid`	`text`	ID of a unique product	0
`asset_id`	`serial`	Asset id (see assetTable)	1
`begin_timestamp_ms`	`timestamptz`	Time when product was inputted in asset	0
`end_timestamp_ms`	`timestamptz`	Time when product was output of asset	100
`product_id`	`text`	ID of the product (see productTable)	1
`is_scrap`	`boolean`	True if product is scrap	true
`quality_class`	`text`	Quality class of the product	A
`station_id`	`text`	ID of the station where the product was processed	Soldering Iron-1

Relations

DDL

CREATE TABLE IF NOT EXISTS uniqueProductTable
(
    uid                 TEXT        NOT NULL,
    asset_id            SERIAL      REFERENCES assetTable (id),
    begin_timestamp_ms  TIMESTAMPTZ NOT NULL,
    end_timestamp_ms    TIMESTAMPTZ NOT NULL,
    product_id          TEXT        NOT NULL,
    is_scrap            BOOLEAN     NOT NULL,
    quality_class       TEXT        NOT NULL,
    station_id          TEXT        NOT NULL,
    UNIQUE(uid, asset_id, station_id),
    CHECK (begin_timestamp_ms < end_timestamp_ms)
);

-- creating an index to increase performance
CREATE INDEX ON uniqueProductTable (asset_id, uid, station_id);