title: Storing sensor data for various sensor types
body: <p>I have a question about how best to store the sensor data produced when the types of sensors are different. </p>
  
  <p><strong>A little bit of background:</strong></p>
  
  <p>I have two different sensoring units.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>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".</p>
  
  <p><strong>My question</strong></p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>Database Structure</strong></p>
  
  <p>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. </p>
  
  <p><strong>Sensor Types explained</strong></p>
  
  <p>Sensor type A has the following information:</p>
  
  <ol>
  <li>Date (string)</li>
  <li>Time (string)</li>
  <li>SensorID (string)</li>
  <li>Temp (decimal)</li>
  <li>Humidity (decimal)</li>
  <li>PIR Count (int)</li>
  </ol>
  
  <p>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:</p>
  
  <p>Config 1 (pipe temps)</p>
  
  <ol>
  <li>DateTime (unix timestamp)</li>
  <li>SensorID</li>
  <li>Pipe Temp 1 (decimal)</li>
  <li>Pipe Temp 2 (decimal)</li>
  </ol>
  
  <p>Config 2 (Power monitoring)</p>
  
  <ol>
  <li>DateTime (unix timestamp)</li>
  <li>SensorID (string)</li>
  <li>Power 1 (decimal)</li>
  <li>Power 2 (decimal)</li>
  <li>Power 3 (decimal)</li>
  <li>Power 4 (decimal)</li>
  </ol>
  
  <p>All sensors have the same common data:</p>
  
  <ol>
  <li>DateTime </li>
  <li>SensorID</li>
  </ol>
  
  <p>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</p>
  
  <p>Thanks</p>