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.
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!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
