title: best way to store time series data with an identifier in sqlite3
body: <p>Let's say there are a number of different sensors, all of which save data in a database as they measure it and each sensors can have more entries. I'm looking for the best way to save this data so that select queries could be done fastest possible later. Something like <br/></p>
  <pre><code>&quot;CREATE TABLE IF NOT EXISTS DataTable (sensor_id TEXT, measured_value REAL, time_of_measuring REAL)&quot;
  </code></pre>
  <p>could basically work, but I imagine this wouldn't be very fast for selecting. I know about primary keys, but they prevent duplicates, so I can't just put sensor_id as a primary key. I'm basically looking for sqlite equivalent of saving data like this, but in a single table and as one measurement being one row:</p>
  <pre><code>data = {&quot;sensor1&quot;:[x1,x2,x3], &quot;sensor2&quot;:[z1,z2,z3]...}
  </code></pre>
  <p>I imagine something like ˇˇ would work for inserting more than a single value for each sensor, but would that help at all with selecting?</p>
  <pre><code>&quot;CREATE TABLE IF NOT EXISTS DataTable (sensor_id TEXT NOT NULL, measured_value REAL, time_of_measuring REAL NOT NULL, PRIMARY KEY(sensor_id, time_of_measuring ))&quot;
  </code></pre>