Message-ID: <496a97b1-d7f4-43aa-b5cc-01274dbf5873@cloud.gatewaynet.com>
Date: Fri, 14 Feb 2025 12:21:30 +0200
MIME-Version: 1.0
Subject: Re: Ideas about presenting data coming from sensors
To: Adrian Klaver <adrian.klaver@aklaver.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Cc: Achilleas Mantzios <itdev@gatewaynet.com>
References: <8d2dd92a-da16-435b-a38e-fe72191fc9d1@cloud.gatewaynet.com>
 <ea77fa23-6361-42f0-a511-8e0997dada26@aklaver.com>
 <d8892101-4cab-4f42-8c8b-9a84cb8ff767@cloud.gatewaynet.com>
 <6a326644-c149-4f6f-874a-0b027f863146@aklaver.com>
Content-Language: en-US
From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <6a326644-c149-4f6f-874a-0b027f863146@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/496a97b1-d7f4-43aa-b5cc-01274dbf5873%40cloud.gatewaynet.com>
Precedence: bulk


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!
>>>>
>>>>
>>>>
>>>>
>>>
>