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
arenot guaranteed that the same tags would have the same structure over the whole fleet/manufacturers. So we are
thinkingof putting all semi-structured data of tags of type c) into one table with a single col of type jsonb . From
whatI 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
untilwe 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
easierinitially saving the time to put them into columns one by one, on the other end you have the hassle of dissecting
theJSON, 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
haveto convert the values to string to enter them. It is a pain in the butt, imho, retrieving them again. You can
alleviateby 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/
.
