Building structured data storage strategy for Things data

IOT data packets are generally dependent on the sensors, devices ,gateways, systems that generate them. Also this data can be structured or unstructured.

For the sake of this post lets talk about structured data and challenges to build a mechanism of storing this data into database.

As we all know Java Script Object Notation or JSON (as it is popularly referred to). is the preferred mechanism of data exchange. It is simple and easy to understand and can easily represent complex data structures with ease.
JSON can be nested and multilayered depending on the data. This is where the challenge begins.
Consider a scenario where a gateway is registered and activated on RubiThings. This gateway can be monitoring diverse IOT devices , sensors or even PLCs

The data generated by each device or group of devices or sensors will be diverse. Similarly a typical PLC has 250 tags and any combination of those can be reporting at different frequency.

From a storage perspective RubiThings also needs to be able to handle all these data packets and store the data into a relational database for easy retrieval , grouping , aggregation and reporting.

Thus any payload JSON received from the gateway has to be normalized and made ready to be stored into the data base in such a fashion as to facilitate easy retrieval when invoked from internal / external system for consumption.
What this ensures is fast and easy access

Using this technique will ensure fast and easy retrieval , ease of sorting and ordering.

The more complex the JSON the more columns in the table.
In case there are nested lists then lot of repeated rows created with null values