title: How do you handle database normalization design for optional columns?
body: <p>I am working on a system that stores sensor data. Most sensors measure a single value but some can measure many values for each sample period. I am trying to keep my database as normalized as possible without suffering performance problems for looking up lots of sample data. My question is how to design the sensor data table to account for optional measured data values. For example, sensor A only reads one value, but sensor B reads 5 values. How do I store both sets of data in the data table?</p>
  
  <p>Option 1 is to create a flat structure with a table that has a bunch of columns (value1, value2, value3...valueN, etc) and a field that records how many columns are used. Functional but bad design in my opinion:</p>
  
  <pre><code>Sensor Data
    Sensor ID (Pk)
    Timestamp (PK)
    Columns Used
    Value 1
    Value 2
    Value 3
    ...
    Value n
  </code></pre>
  
  <p>The other option is to highly normalize the structure and have a data table that uses a composite key to store individual data values. It would track the sensor id, timestamp, and data type to maintain unique values. This is highly normalized and allows for an unlimited number of optional data values per sample, but duplicates a lot of information (specifically, sensor id and timestamp):</p>
  
  <pre><code>Sensor Data
    Sensor ID (Pk)
    Timestamp (Pk)
    Data Type (Pk)
    Value
  </code></pre>
  
  <p>This wouldn't be that bad for a few thousand samples, but this system is designed to store millions of sensor samples and joining those values could suffer performance problems (i.e. WHERE Sensor ID and Timestamp are equal but the Data Type is different).</p>
  
  <p>Anyone have a better idea for designing a database to store optional values? Side note: the design has to work with SQL Server and Entity Framework (EF).</p>