Storing dynamic data in django

Question

So, in my application I currently am receiving and storing some data,the structure currently is based around this:

Each application has a number of devices
Each device has incoming data from its sensors
Each sensor has a type (e.g. temperature/humidity/etc)

Initially, I had wanted to find a solution to store the data dynamically into one table for all data, with a model that might look like this:

time = models.DateTimeField()
sensor = models.ForeignKey(Sensor, on_delete=models.CASCADE)
data = ???

Now, each sensor is connected in the models to all the details (including the application, the sensor hardware information and other API-related information for connecting to said sensors), however, for the data I looked up a number of options but I have yet to find one that feels sufficient, some of the options I explored are:

Storing the data in a dictionary and storing that in a model as per the answers in Here
Having separate tables and somehow calling the models later when storing data using the method here, in addition to some system with inheritance such as discussed here
Something similar to this using dynamic fields

I am currently using mySQL, and I saw some other solutions that might work using PostgreSQL for example, but I would like to avoid switching databases if there is another option.

I just feel there must be a better more clean solution for this, as it feels like a straightforward implementation. In a sense most of those implementation either feel too hack-y and inefficient especially considering that I will be storing a large amount of data (A larger number of sensors sending data continuously to the server), or do not allow queries on the sub-data.

Are there any other better implementations for my application? If not, which one of those implementations is best for me to focus on?

By PostgreSQL do you mean the `JSONField`? You can now use that in other databases also if you use Django version >= 3.1. See [`JSONField` (Django docs)](https://docs.djangoproject.com/en/3.1/ref/models/fields/#jsonfield). Anyway the other solutions are not exactly hacky, considering we are talking about RDBMS. Your use case is more suited to some NoSQL database. — Abdul Aziz Barkat, Mar 09 '21 at 13:42
@AbdulAzizBarkat I read that you can store objects with postgreSQL instead, but JSONField was one of the other options. — Zaid Al Shattle, Mar 09 '21 at 14:48
Storing objects with PostgreSQL **is** using `JSONField` (or `HStoreField`). The other option you talk about is most likely using a `CharField` whose data you yourself convert to and from JSON... — Abdul Aziz Barkat, Mar 09 '21 at 14:59
I see, I apologize for my confusion, still a bit new in Django. So wouldn't using JSONFields be inefficient if I have to query a lot of data? — Zaid Al Shattle, Mar 09 '21 at 15:00

score 0 · Accepted Answer · answered Mar 10 '21 at 10:38

The method I ended up using, if anyone faces a similar issue, is as follows:

The main model:

class Sensor(Device):
    sensorName = models.CharField(max_length=50)
    sensorSerial = models.CharField(max_length=50)
    sensorTable = models.CharField(max_length=50)

Each sensor will recieve data, each entry of said data will be saved as follows:

class Entry(models.Model):
    time = models.DateTimeField()
    sensor = models.ForeignKey(Sensor, on_delete=CASCADE)

And each entry will contain a number of fields which are unknown, depending on what kind of sensor it was, so a third class for the DATA is included and used a pseudo-dictionary:

class Data(models.Model):
    entry = models.ForeignKey(Entry, on_delete=CASCADE)
    key = models.CharField(max_length=50)
    value = models.CharField(max_length=50)

I am still investigating how to push the effieciency higher, but this is definitely much faster than looping through entries while decoding XML/JSON over 100s of thousands of entries.

I will update the answer with any breakthroughs or updates I find.

Storing dynamic data in django

1 Answers1