Data Model & System Architecture

Entity Domain Overview

The ABSD_ schema is organised into eight functional domains. Every vessel-domain table uses LRNO + SEQNO as a composite key — SEQNO '00' is the current active record; '01', '02'… are historical versions in descending date order.

Entity Relationship Diagram

The diagram below shows the 21 most-referenced core entities and their relationships. All vessel-domain tables join on LRNO (blue/teal lines). The company domain uses OW_CODE (green). The ABSD_HIOW table is the bridge — it holds both LRNO and OW_CODE, linking vessel records to company records.

SEQNO History Model

Every ABSD_ table uses a two-part primary key: LRNO + SEQNO. SEQNO '00' is always the current active record. When a field changes, the existing row is moved to SEQNO '01' (then previous '01' shifts to '02', etc.) and a new row with SEQNO '00' is inserted. This means history is always accessible by filtering WHERE SEQNO > '00'.

Exception — ABSD_HIFL: The flag/callsign history table uses 95 fixed SEQNO slots ('01'–'95') with explicit rotation logic. See Section 10k for the trigger pattern.

Data Flow Architecture

The diagram below shows the complete end-to-end data journey — from raw external feeds through staging, pipeline processing, the core ABSD_ database, trigger-enforced business rules, output tables, and finally client-facing products. Data can only reach the core database through the pipeline layer; no external source writes directly to ABSD_ tables.