Background
I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,
- Each sensor can support multiple features. Example: Temperature, Humidity, etc.
- The features supported by a sensor are identified by the model linked to the sensor.
- A model can be linked to multiple sensors.
- The features linked to a model are user configurable.
- A feature can be linked to more than one models.
Following is the ER diagram of my current database design,
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.
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.