Storing sensor data for various sensor types

Question

I have a question about how best to store the sensor data produced when the types of sensors are different.

A little bit of background:

I have two different sensoring units.

One unit (lets say Unit Type A) has built in sensoring (temp, humidity etc) and produces a CSV string 12 in length in a fixed order, where all 12 values are of use to me.

The other unit (Unit type B) is a unit where you can plug in probes and energy moniting clamps etc, in all cases it produces a CSV string 13 in length, but if you only have two things connected, you only get useful information from two of the 13 CSV values. We have two different configurations of this unit we will use.

All of the sensors have an ID (given in the CSV string), I will be linking the sensor by ID to a room which is already in a database (SQL 2016) through a web UI (ASP.NET).

The types of queries I will be doing are "what is the current temp in room a", I will also need to query for trends such as "give me all the rooms with a high humidity average".

My question

Considering that I will have about 250 sensors at the moment, each posting through a web API around every 10 seconds or so, and I have currently two different types of CSV Strings (maybe more in the future), and in one case the CSV string will contain useful information in a different part of the CSV string than another, what would you recommend as a suitable table structure to support this? ideally in SQL server (maybe 2016 for JSON support?) as SQL Server is something I am more familar with, however if this is a badchoice of course I am open to your ideas.

I was trying to avoid a table 'per sensor type', as this seems messy, it would also make it harder to add different sensor types in the future without new tables etc.

I did consider having my API seperate to my web app, posting my sensors to it and have the API store the CSV 'as is' into a database, along with a 'sensor type' ID. I thought then I could post processing it in batches (service of some sort) using a stored procedure to inserting into my main database in batches as I thought this might reduce the API overhead some what.

Database Structure

A building has many rooms, a room has many sensors. A sensor has a type. I will keep tabs of the mapping between a rooms and a sensor IDs in a mapping table.

Sensor Types explained

Sensor type A has the following information:

1. Date (string)
2. Time (string)
3. SensorID (string)
4. Temp (decimal)
5. Humidity (decimal)
6. PIR Count (int)

Sensor B comes in two different configurations, in both cases the same CSV string is posted, the different will be in which values are used from it, but i want to store:

Config 1 (pipe temps)

1. DateTime (unix timestamp)
2. SensorID
3. Pipe Temp 1 (decimal)
4. Pipe Temp 2 (decimal)

Config 2 (Power monitoring)

1. DateTime (unix timestamp)
2. SensorID (string)
3. Power 1 (decimal)
4. Power 2 (decimal)
5. Power 3 (decimal)
6. Power 4 (decimal)

All sensors have the same common data:

1. DateTime
2. SensorID

I suppose one solution is to have a room have many 'sensor type A's' and many sensor type b's in config 1, and many sensor type b's in config 2 with each sensor type having its own table for its data, however I thought it would be great if I could just have the one table for all sensors in a sensor table, and give them a type from a type table, as this would be more flexible if/when adding more sensors. The downside to this approach is how when do i link to these different sensor data types/shapes

Thanks

Answer 1

Your basic options are discussed in this question, but it's interesting to work the example.

Okay, let's go at this bit-by-bit.

A building has many rooms

So right off, we know we have two tables:

Building

and

Room 
  --fk to Building

a room has many sensors.

Sensor
  --fk to Room

(or, possibly, if sensors might monitor events from multiple rooms)

Sensor

Room_Sensor
  --fk to Room
  --fk to Sensor

A sensor has a type.

Sensor
  --type id of some sort (manufacturer?)

Sensor type A has the following information:

... and this is where it gets interesting. Because, while it's true Type A generates this information, this is not the state of a Type A; it's the state of a room.

And that's the important part of this: databases are a repository of state (a series of states, here, given we have timestamps).

There's an additional problem, too - what happens if sensors are moved, or rooms are subdivided ("adding" two or more rooms that weren't previously there)? So lets backtrack a little:

Room
  --fk to building

Sensor
  --type id of some sort, manufacturer info?

Room_Sensor
  --pk
  --fk to room
  --fk to sensor
  --createdAt timestamp

Note that Room_Sensor may have multiple copies of the same (Room, Sensor) relationship, changing over time (maybe a sensor was moved across the hallway for a week or something).

Now, what about the measured data? You actually have several different "things" here:

Environment
  --fk to Room_Sensor
  --occurredAt timestamp
  --temperatureInCelsius
  --humidityInBar (or whatever other unit)
  --PIR (particulate?  please label your units)

(I'm assuming that the indexed items for Type B represent the same measurement of different things, which are supposed to be multiple rows, and which mean an additional table to use for foreign keys. If they're instead different measurements of the same thing - inflow and outflow temperature, say - it's simpler)

Pipe

Pipe_Temperature
  --fk to pipe
  --fk to Room_Sensor
  --occurredAt timestamp
  --temperatureInCelsius

And

Electrical_Drop

Electrical_Drop_Draw
  --fk to Electrical_Drop
  --fk to Room_Sensor
  --occurredAt timestamp
  --consumptionInWatts

...yeah, that's probably what I'd start with. With this sort of structure, the actual physical type of the sensor is irrelevant - we only care about the type of information it provides us. Perhaps in the future Type B has new capability added to measure room temperature; if so, the database may remain the same, and the rows may be added to the existing tables.

Note that doing this requires you to have an API or loading program of some sort, but you'd most likely require one of those regardless.