1

Background

I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,

  1. Each sensor can support multiple features. Example: Temperature, Humidity, etc.
  2. The features supported by a sensor are identified by the model linked to the sensor.
  3. A model can be linked to multiple sensors.
  4. The features linked to a model are user configurable.
  5. A feature can be linked to more than one models.

Following is the ER diagram of my current database design, enter image description here

Problem:

In the current design, I store the configuration and readings as different columns in the Sensors table. Example: MinTemperature, MaxTemperature, Temperature, etc. This leads to a huge number of columns in the Sensors table. Not all config and reading fields will be required for each sensor. Example: Only the sensors which support the Temperature feature need the config and readings of Temperature.

The current count of columns I have in my table is 35 which I think is huge. Also supporting a new feature in the future means changing the database schema to add new columns for config and reading. Hence I want to improve this design.

Solution Tried:

To solve this I came up with an EAV pattern to store config and readings as Key-Value pairs in a table. But I found out that it is an anti-pattern and it is not recommended to use it. Following is the class diagram of the EAV approach.

enter image description here

Here, the Properties table contains keys of all the config and readings. I have mapped them against the features using the FeatureProperties table and used its Id to store the values of properties in the SensorFeatureProperties table. This is done to apply the constraint that the sensor must have values for required properties only.

There is a functionality that is possible in the existing design but not in the new design. There are certain readings that I want for every sensor but still want it to be user configurable so that the user can toggle its appearance in UI.

Example: MACAddress. The MACAddress is needed for every sensor for internal purposes but it still needs to be user configurable to toggle UI appearance. This is supported in the existing design as every reading was stored as a column. Now I have changed the design to store readings in an EAV table but I don't want to store MACAddress in an EAV table. This seems like some kind of violation of design since some features follow the design and some don't.

Please provide me with suggestions on how can I improve my existing design.

Improve this question
3
  • 1
    "The current count of columns I have in my table is 35 which I think is huge." - FWIW, many enterprise applications have tables with hundreds to thousands of columns in a single table. I consider that huge. 100 columns is typically what I consider to be the reasonable to unreasonable threshold.
    – J.D.
    Jun 14 at 12:37
  • The ER diagram that you propose is creating a circle and this will mostly lead to deadlocking
    – Francesco Mantovani
    Jun 15 at 6:49
  • @FrancescoMantovani Are you telling this for the EAV approach I mentioned in my question in which I used the SensorFeatureProperty table? Is there a way to solve it?
    – Hem Bhagat
    Jun 15 at 7:09

1 Answer 1

Reset to default
0

The current count of columns I have in my table is 35 which I think is huge.

No is not huge, I've seen worse. Even if you are creating smart bulb with a CPU you can now store 256Gb in a card smaller than your thumbnail. and no, SqlLite will not have headache if you have 35 columns or more.

But I agree it's mess.

Because you said that every IoT might have different sensor I suggest you to create 1 table for each sensor. This will limit future design change to each sensor.

Solution Tried:

The ER diagram shows a circle which might lead to deadlocking. Don't do circles, do stars. As in the previous ER diagram, the current you are using.

Now I have changed the design to store readings in an EAV table but I don't want to store MACAddress in an EAV table.

Once again, what is your problem with too many columns or too many tables. It sounds like you have preconcepts about databases and file systems. They are piece of silicium, the will not have headache.

Overall you forgot to ask a plain question. We don't understand what is your question here. Do you have a performance problem? Does your current design provokes blocking, locking, deadlocking, performance degradation? Do you need to reach a certain benchmark with your performance? etc...

So my question to you is: what is the problem with the ER diagram you are currently using?

Improve this answer
3
  • The problem with the current design is I have to change the schema every time I decide to add/remove a feature or want to support new properties(config or reading) for already existing features. These can be frequent changes. I don't want to do that. Another problem is the NULL problem. In the current design, I will have so many columns that have NULL values. Currently, the number of columns is less but it will start increasing once I start supporting new features.
    – Hem Bhagat
    Jun 15 at 9:11
  • OK, how much is the weight of a normal database. I mean the average database that you use in production. 1Gb or 100Gb?
    – Francesco Mantovani
    Jun 15 at 20:01
  • You don't need to change the schema every time you have a new feature. Give a table to every device or a table to every feature
    – Francesco Mantovani
    Jun 15 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.