How to define the table structure for measured data in sqlite

database-designperformancesqlite

I have an embedded system with an sqlite database.
How should I define the table structure for the measured data from 4 sensors?

Each sensor give us every second an numeric measured value.
The measured value should be stored with an timestamp in the database.

Criteria are:

  • Table are "fill only". There are no delete and update operations.
  • INSERT (timestamp, value) should be fast.
  • SELECT (timestamp, value) for an specific time range should be fast.

First Approach: One table for all sensors

CREATE TABLE Measured_Values (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    sensor_id INTEGER  NOT NULL,
    timestamp DATETIME NOT NULL,
    value     REAL,
    FOREIGN KEY (sensor_id) REFERENCES Sensor_List(id))

with

CREATE TABLE Sensor_List(
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT)

Second Approach: One table per sensor

CREATE TABLE Measured_Values_Sensor_ONE (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME NOT NULL,
    value     REAL)

CREATE TABLE Measured_Values_Sensor_TWO (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME NOT NULL,
    value     REAL)

and so on.

OR is there another better design?

Which design is usually used?
Which design gives me small insert time but also fast query time?

Best Answer

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.

When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.

For fast queries on time ranges, you need an index on the timestamp column. This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the sensor_id and timestamp columns.)

Please note that you do not need AUTOINCREMENT when you never delete rows. (Autoincrement In SQLite)

Related Question