public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios - cloud <[email protected]>
To: [email protected]
Subject: Re: Ideas about presenting data coming from sensors
Date: Fri, 14 Feb 2025 12:42:23 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>


On 2/13/25 20:44, Thiemo Kellner wrote:
> 13.02.2025 10:54:05 Achilleas Mantzios - cloud <[email protected]>:
>
>> 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 ?
>>
>> 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.
> I have not come to see the real advantage of putting data into something like Jason or XML columns. Sue, you make life easier initially saving the time to put them into columns one by one, on the other end you have the hassle of dissecting the JSON, XML you name it when you retrieve/select the data, every query. While the query might stay stable the  computational
> effort dissection will have to be done with every query execution.
Yes, jsonb is for cases that we simply do not know or care to know or 
have the time to break down something that will change from ship to 
ship, and already the burden of fighting with the electricians to define 
correctly the tags of a) and b) is too much. So since, on top of that, 
we hear variable "opinions" on those of type c) (one of them being ... 
nah .. nobody needs those), so we are saving our mental health going 
with jason, and of course this is under review, it could change with no 
big trouble at this stage.
>
> For c) you could go to 6th normal form, or what number that is, by a table like TAG(id, tag_name, tag_value). You would have to convert the values to string to enter them. It is a pain in the butt, imho, retrieving them again. You can alleviate by having a specific column/table per data type, e.g. TAG(id, tag_name,tag_value_varray,tag_value_date,...).
>
> What is the downside of having each a table for the special tags? More effort in setting up.
> The upside is less effort in retrieval, and a much more understandable model.
The downside is for every vessel I might end up with a new table for 
every new setup which sucks, the idea by Adrian is fine, but we don't 
have users to support the variable dynamic definitions case by case, we 
would have to do that, so I better offload it all to the devs for lack 
of a better option. Plus this is the beginning we are starting lean, not 
over-engineering without a reason, and then augment as we go.
>
> If your tag structure is volatile, you might have generic column names on a table mapping them in a view to speaking names. Taking this further does anchor modelling https://www.anchormodeling.com/
> .
>
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Ideas about presenting data coming from sensors
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox