Re: Ideas about presenting data coming from sensors

On 2/14/25 11:34, Allan Kamau wrote:
>
>
> On Thu, Feb 13, 2025 at 9:44 PM Thiemo Kellner 
> <thiemo@gelassene-pferde.biz> wrote:
>
>     13.02.2025 10:54:05 Achilleas Mantzios - cloud
>     <a.mantzios@cloud.gatewaynet.com>:
>
>     > 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.
>
>     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.
>
>     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/
>     .
>
>
>
> Though you haven't asked for comments on database design, I have a 
> suggestion, have you considered using table partitioning based on a 
> well defined date interval, maybe monthly?
> Here your DB data population application will determine the name of 
> the table based on the current date then check for the existence of 
> the table and then construct the inherited table if one does not exist 
> and proceed to populate the child table accordingly.

Absolutely, this will be a necessity sooner or later, as I can see the 
future coming it will be a new timescaledb functioning at the start as a 
logical replica of our maindb, and timescaledb will handle all the 
partitioning (i hope). At some point switch the app (both the data 
loading (from the vessels) and the queries ) to the timescaledb, and 
free the space in the maindb or smth along those line.

> This may prove useful in future.
>
> -Allan.
>