0

I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this?

-

Option 1 : should I integrate all sensors into a single database like this

Table Sensor
---
SensorID
SensorType
SensorDesc

Table SensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue

-

Option 2 : Or should I put each sensor type into separate table?

Table TemperatureSensor
---
SensorID
SensorDesc

Table TemperatureSensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue (double)

Table MotionSensor
---
SensorID
SensorDesc

Table MotionSensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue (integer)

Considering that various types of sensor tables will have similar fields but potentially different reading data types (some of them are integer and some of them are doubles, etc). Is it logical to separate the tables for each sensors? Or keep using a single table for every type of sensors?

  • Which one is the best practice to implement such database if there might be various types of sensors to be added in the future?
  • Which one can be better optimized for performance?

Thank you

1
  • How often are you going to want to have queries that return data from multiple types of sensors? How big a deal is adding a new type of sensor in the real world? If adding a new type of sensor means that you're going to have to make a bunch of application changes to support it, that's very different than if adding a new type of sensor requires no changes to the application. Commented Oct 22, 2019 at 14:43

1 Answer 1

0

Depending on how you plan on using the reading values, I would lean towards option 2 for this. Since you have different categories of sensors, with different types of values being used for each, then it would make sense to to have a master table that stores all the sensors and their type and status (online/offline) and location. There would then be a table for each of the types. This would make it easier to process the data over time, such as average temperature over time.

If you just want to store the data without much processing, then going the one-table route would be best, and store the values as VARCHARs. This would make adding a type later on easier, but would make manipulating the data in the future more difficult.

Your Answer

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

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