uniqueProductTable

uniqueProductTable contains unique products and their IDs.

Usage

This table stores unique products.

Structure

keydata typedescriptionexample
uidtextID of a unique product0
asset_idserialAsset id (see assetTable)1
begin_timestamp_mstimestamptzTime when product was inputted in asset0
end_timestamp_mstimestamptzTime when product was output of asset100
product_idtextID of the product (see productTable)1
is_scrapbooleanTrue if product is scraptrue
quality_classtextQuality class of the productA
station_idtextID of the station where the product was processedSoldering Iron-1

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);