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@aklaver.com
