On 2/13/25 19:12, Adrian Klaver wrote:
On 2/13/25 01:53, Achilleas Mantzios - cloud wrote:
Now my problem is on the design . We have :
a) tags that have primitive values, float4 lets say - this is the
majority, e.g. 60% of all tags
b) tags that contain alarms data also with defined structure, which
have additional data such as time of the initial alarm set,
acknowledgement of this alarm , validity of this alarm. Those
represent smth like 35% fo all tags
c) tags that are basically polymorphic (about 11 of them all in all),
each one has different structure, and their fields/cols range a few
(1) up to many (25)
We have a table for a) and a table for b).
If we followed a strict normalized approach then we would create
additionally 11 tables each tag of type c) . And we are not
guaranteed that the same tags would have the same structure over the
whole fleet/manufacturers. So we are thinking of putting all
semi-structured data of tags of type c) into one table with a single
col of type jsonb .
From what I read timescaledb plays nice with jsonb (or at least not
bad).
Do you ppl see any gotcha with this approach ?
The only thing I can see at this time is: 'And we are not guaranteed
that the same tags would have the same structure over the whole
fleet/manufacturers.'
That would seem to me to point to a need for a table that maps a
structure template to a fleet or manufacturer and a corresponding
field in table c) that holds the fleet/manufacturer information.
Nice idea or just stuff everything (self-contained) inside json and let
the software do the relevant logic, a somewhat more liberal approach.
For starters we will not convert yet to timescaledb, but store them
and handle them like normal tables. At least until we grasp the ins
and outs of this.
Thank you!
